I need to pull rather simple time stamped data for first shift 7a - 7p and second shift 7p - 7a per day. I am using start hour. The second shift is a challenge due to 12a - 6a falling on a different day of the 7p - 7a shift. I need to somehow recognize one date plus the next date. I know we have a previous function but I cannot find anything on a next function.
Has anyone encountered this need and were you able to resolve it?
Thanks in advance,
Terri
Your question is not clear. When you say ‘need to pull’, that sounds like a query issue. But, then you mention the previous function (there is no next function), which is a report level function. Can you provide more clarity and maybe a sample of what you are looking to do.
There is an indicator that can be documented on a customer at any time. Qualifying on the indicator, I am pulling and displaying the customer id, date it was documented, and start hour it was documented. Filtering on start hour and breaking on the date, I can tally the number of customer id’s for 7a - 7p per day without issue. What I am struggling to tally is the total number of customer id’s for 7p - 7a since that shift spans two days.
Create a date variable. First you need to decide which shifts belong to a date…
do 10/12/2021 7am - 10/13/2021 7am count as 10/12/2021
or do 10/11/2021 7pm - 10/12/2021 7pm count as 10/12/2021
Based on the logic you need, create a variable to determine the date, something along the lines of: (using the first option above)
= If FormatDate( [DateTimeField] ; “HH:mm” ) between “07:00” and “18:59” then ToDate( FormatDate( [DateTimeField] ; “MM/dd/yyyy” ) ; “MM/dd/yyyy” )
else ToDate( FormatDate( RelativeDate( [DateTimeField] ; -1 ) ; “MM/dd/yyyy” ) ; “MM/dd/yyyy” )
If you also need to count per shift, create a similar variable for your shift:
= If FormatDate( [DateTimeField] ; “HH:mm” ) between “07:00” and “18:59” then “7a - 7p” else “7p - 7a”
This worked brilliantly, thank you!
Much appreciated,
Terri