My boss has asked to see if we can get SQL written to determine cases newly created during a time period and ones edited.
When working on the SQL - it looks like in the Cases table - when a new case is added the user_id and updated_by are the same values. When it is updated then the updated_by gets updated.
So it seems we can use - for the new cases - where user_id and updated_by are the same and for those updated - the 2 fields would have different values.
Problem is - if I add in a new case and 3 days later edit it - technically I have updated it and it is not new. The 2 fields will remain the same… If I use the case_changes table - if the user_id and updated_by are the same and there are no records in the case_changes table - then the case is new. But as soon as I edit my case (even if 3 seconds later) - a record goes into the case_changes table and th etest case will be seen as an update.
I just want to make sure there is no other way to determine a new case as my boss will need to explain the reason why it looks we create very few cases.