Hello
i am looking too combine totals from 2 different date/times into one record but its tricky because of the following
we have a day shift that works 6:30 am to 6:30 pm so this is easy to just CAST the date data to split the totals by day but the night shift works 6:30 pm to 6:30 am so 1 shift falls on 2 days and the 2nd day they can be back in at 6:30 pm that night so you can essentially have 2 different working days fall on the same day so they also would need to be split out. how would you write a filter or statement for this? this is beyond my skill level
I think you want to get the count of the number of shifts someone has worked but you launch right into mucking about with casting without actually defining the problem you want to solve. Also I think you have two columns, one of which has the shift start time and the shift end time but that is a guess. Knowing what you have in the larder helps determine what you can make for dinner and it's somewhat similar here.
Once that is straightened away, policy questions and exception handling for matters such as people only working part of a shift (they get called away to deal with personal matters, they go to the doctor or dentist, they fall down due to exhaustion due to overwork, they get bits of their person caught in a machine because 12 hours is a long time etc.) would then need to be clarified before a reasonably suitable response could be formulated. For example, it's possible that using an interval would be helpful but without a clearly defined problem there's no way to know that with any degree of certainty.