I am trying to calculate minutes between two timestamps but I would like to exclude not only weekends and holidays but also any period that are out of scheduled shift start and shift end times.
I have case create date/time field and case assignment date/time field. The shift start and end time is not in the data source and I need to somehow hard code different shift start and end times based on agent. So basically I am trying to figure out how long did it take an agent to assign a case from the time it was created. I do have business days calendar tables that I can utilize.
Please let me know if there are any questions.
Thanking you in advance.
Here and here are two great examples of SQL functions (UDFs) you can create to compute time differences in seconds; you could easily modify them to compute minutes instead. It sounds like you would need to join to your reference tables to substract out the times that are not scheduled shifts, but creating a SQL UDF to compute all these time differences would make the script simpler, easier to read and follow.