SQL Count based on shifts

[craigb]

Im struggling with this one but I know out there, there is people far more clever then I am.

At the moment I have a SQL query based on taking info from a dateTime picker.

SELECT COUNT(DISTINCT Joint_Number)
FROM  impp_004_joint_counter
WHERE t_stamp BETWEEN '{FromValue}' AND '{ToValue}'

But i want to make it dymamic for this shift and last shift. Our shifts run 0600-1800 then 1800-0600 and I want to automatically calculate the counts. The trouble Im facing is that if you look at the count, same day after 1800 the sql is DATENOW then the time. But if you look at the count the next day before 0600 its then datenow-1. How do you make that dymanic when the date is always changing ?

[Transistor]

Try using SQL’s DATEADD (MS SQL) or DATE_ADD (MySQL) functions to work the end date from the first. It would be something like

WHERE t_stamp >= '{FromValue}' 
  AND t_stamp < DATEADD(hour, 12, '{FromValue}') AS DateAdd

Check whether the parameter needs to be quoted or not.

[rvilchis]

I’ve faced the same challenge. My solution was to create a function Tag to determine the current shift, then another pair of tags to be written to, depending on the current shift. At the end, you’ll have three different Tags: Main, 1st_Shift, 2nd_Shift. Meaning…

1. you can count dynamically in the first stream
   

   
   
   for an hour by hour report
   
   

2. you can easily count on each bucket (1st_Shift or 2nd_Shift)
   

   
   
   for a day-by-day and shift-by-shift report
   
   

…the really challenging part is developing an easy logic/interface to manage distinct shifts/schedules per machine (where I’m stuck at).

Additionally, this approach will retain the Shifts configuration thru time (reflected in the DB’s data), which might be useful for future enquires of Industrial/Production Engineering.

[dkhayes117]

Welcome to the forums @rvilchis
The way I handle the shift times dynamically, is to create a schedule like machine1_1stShift, then I assign a dummy user to that schedule. I then have a gateway timer script that checks if the user is scheduled every 5 seconds. When the system.user.isUserScheduled() goes from false to true, I write the current time to a memory tag called something like machine1_Start. Then you can use that tag for any queries etc and can easily be managed from a schedule managing component.