I'm working with data that has a start datetime and end datetime column for each event which can be a short period of time (Few hours) up to a number of days in duration.
I'm trying to establish a way to check for where the following is true:
- the duration between the start and end datetimes is greater than 6 hours
- this duration takes place between the hours of 09:00 and 17:00 (on any of the dates within the period if it was a long duration e.g. 36hrs)
If the total duration of 6hrs is met but any of those 6hrs falls outwith these hours, then I don't want to count it.
01/05/23 09:00 to 01/05/23 16:30 - true
01/05/23 08:30 to 01/05/23 14:30 - false
01/05/23 10:00 to 01/05/23 17:30 - true
01/05/23 11:00 to 02/05/23 01:00 - true
01/05/23 12:00 to 02/05/23 02:30 - false
I have tried setting up a basic case when to check where datediff >= 6 and check that the end time > start time (incase of any data quality issues with dates recorded) but struggling to come up with a way to check for the datediff >= 6 being met within the 09:00 and 17:00 period and tie the two conditions together.
I'm looking to add this to a large data set so looking for something that hopefully won't affect performance too much as I remember doing something before where I listed rows by the hour within a duration but it had a knock on effect on performance due to the vast amount of rows created to do this.
I was wondering if anyone has any ideas on the best way I can achieve this? Thanks in advance.