I have a transactional table of moves that occurs during the day and night. Our operation consists of two shifts, from 7AM to 6PM, and from 7PM to 6AM. The problematic part has always been the night shift, since it goes through the date change at midnight.
The time field is [Move Completed On], so I created a calculated field like this:
if datepart('hour', [Move Completed On]) >=7 and datepart('hour', [Move Completed On]) <=23 then
[Move Completed On]
else dateadd('day', -1, [Move Completed On])
That formula fixes the day change issue, bringing me a whole 24 hour day, starting at 7AM, and finishing just before 7AM of the next day.
The issue is, depending on the time of the day, I want to select either the day shift (0700-1800 hrs) or the night shift (1900-0600 hrs), to display the transactions that are occurring during the current shift.
With the calculation provided, you can get the 24 hour look at things but can't you just create another calc for day and night? Is there something preventing two separate calculations from being utilized in this example?
if datepart('hour',[Move Completed On])>=7 and datepart('hour',[Move Completed On])<=18 then 'day' else 'night' end