I am working on a report that tabulates data from the MS-SQL server. The problem I am having is when it crosses over the midnight hour. From 12:01am till 11:59pm my query runs nice and shows what I need, but it bombs out when time goes over the midnight.
Here is what I have so far:
select machine, jobtask, sum(runtime), count(fault1) from prodtrack where shift = {root container\shift} and datediff(d,t,stamp,current_timestamp) = 0 group by machine,jobtask
i know the code looks rather vague I am writing this from home so I can not see the exact code but it gives you a good idea on the query. The shift number remains consistent and I had to add the datediff command in there otherwise it would take more than the current days totals. Maybe using datediff is the wrong command, I dont know but would like others opinions on how they would or have accomplished this kind of goal.
Thanks and have a great day.
What do you mean when you say the query “bombs out”?
Maybe bombs out was not the correct term to use. What it actually does is it looses all the counts and information from the previous day. So it does not keep accumulating and showing all the details that occured prior to midnight. It for more lack of a better word resets.
The biggest issue we have is our shift schedules. Under normal conditions our shifts are 6:30am till 3pm, 4:30pm till 1am, and 10pm till 6:30am. But when we get enough work the schedules change. And when they do that is when things get really hinky. Some of the things we do is split dayshift so we would have some that would come in at 4:30am and work till 4:00pm while the others would work from 6:30am till 6:00pm and second would go something like 2pm till 1:30am while others would work 4:30pm till 4:00am. about 80% of the time we run under the normal numbers but the other 20% we run these extended hours. And i can tell you that during the last month the shifts have been extended for more than 20%.
What I am trying to find is a query that will total for each shift but will not pull anything from the previous days run only the run that is currently being ran. Hope that is clear. So if the second shift runs normally I could run the query with the time/date greater than 4pm and less than 2 am with the shift = 2. But again that would not cover the extended shift.
Hope this helps clear things up. Have a great day.
Sounds like you’re going to need to keep track of your shifts in a way that includes their absolute start and stop. What I mean is that you need a full datetime for start and stop, not just the time of day. You could have a table that keeps track of all of your actual shifts, one record for each shift. Not just “shift 2” but shift 2 / start datetime / stop datetime, and then you can schedule your shifts arbitrarily and not worry about varying schedules.