SQL parts per hour, min, dynamic

Ignition
1

Hello, I need to write a a query to report cases per hour, or pallets per hour.

the way i have it, it is. i have Runlog table, in that table i have create an entry every time a pallet it is creater with the runid, palletid, time, caseqty.

so at the end of the run or in the middle of the run, i want to know how many cases or pallets per each hour i’m producing.

Ideally would be to compare the amount that i should be running at 100% to the amount that it is actually running. that way to get and efficiency. my idea was to be able to graph this. compare the ideal amount to the actual amount. i would be bringing this from an sql database and not from the PLC tag history. Im using sql server.

so the query would be the sum of pallets or cases per hour, some hours will have 0 cases so that it is also something i need to be able to do. the run usually go overnight so it might be shift 2,3 and 1 from the next day. would actually be great if i can make it dynamic and be able to select either per hour, or minutes.

help would be great on this i have been stuck on this for a while.

Thanks in advance,

Juan Garbin

2

A query like this might work

SELECT SUM(caseqty) 
FROM table_name 
WHERE time BETWEEN DATE_SUB(NOW(), INTERVAL 1 HOUR) AND NOW()

Did A quick test in MySQL and seems to work fine

3

Thank you, i’m actually trying to converted to sql server, but no luck so far with the query.

also did you try for the hours that have no records on it?

4

Try this then, failed to notice that you are on SQL server.

SELECT COALESCE(SUM(caseqty), 0) AS 'total_cases', 
COALESCE(COUNT(*), 0) AS 'total_pallets'
FROM your_table_name
WHERE time BETWEEN DATEADD(HOUR, -1, CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP

The query should return the quantity of cases from the last hour. To get cases from a specific time and one hour back, replace the CURRENT_TIME with a valid datetime value or variable.
Also, if you want to select from a given time and one hour forward the where clause could be written as

WHERE time BETWEEN CURRENT_TIMESTAMP AND DATEADD(HOUR, 1, CURRENT_TIMESTAMP)

Please note, I do not have a SQL server on hand to test this on. To get the interval in minutes, replace HOUR with MINUTE, and to change the range, replace 1 with the desired range.

EDIT:
Would just mention that a valid date time to replace the CURRENT_TIMSTAMP could be e.g. ‘2018-01-08 22:00:00.000’

5

Thank you very much. it works but it gives the sum of all the cases between the interval as one sum. is there a way to make so that it sums cases per hour, instead of a all the cases in one sum?

6

Well, now it can get a little tricky, not sure if I entirely understand the final goal, will try anyway :slight_smile:

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, time), 0) as 'time',
COALESCE(SUM(caseqty), 0) AS 'total_cases',
COALESCE(COUNT(*), 0) AS 'total_pallets'
FROM your_table_name
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, time), 0)
ORDER BY time

Hope this is what you are looking for :slight_smile:

And again, doesn’t have a SQL server to test this on, but hope it is correct.

EDIT:
Just to clarify, this only returns rows where records exist within the hour.

EDIT 2:
Noticed an error in the query. Is corrected.

And again to clarify. To change to minutes replace the HOUR (all 4) with MINUTE.

7

A different method, tweak to suit

Use this myself in Ignition reporting with the two ‘?’ as StartDate and EndDate

Then graph and you can easily view min/max or there are min/max extensions within reporting

Will work outside the report component too of course

SELECT datepart(hour, [TestTime]) as hour_offloaded, count(*) as qty FROM
where a
and b
and TestTime > ?
and TestTime < ?
group by datepart(hour, [TestTime])
order by datepart(hour, [TestTime])

8

Wouldn't this group let's say records from 11h each day if the timespan is greater than 24 hours?

9

And just had to edit mine again as the time range didn’t work. Should however work getting within each minute, hour, day or whatever specified.

10

Finally, came up with a solution that works with range aswell.

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, time), -DATEPART(HOUR, time) % 3) as 'time',
COALESCE(SUM(caseqty), 0) AS 'total_cases',
COALESCE(COUNT(*), 0) AS 'total_pallets'
WHERE runid = some_value_to_show
FROM your_table_name
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, time), -DATEPART(HOUR, time) % 3)
ORDER BY time

The values 3 in the DATEPART functions (2 locations) is the range in the specified units (minutes, hours, days …). This query will group in 3 hour intervals. Note, there is now 6 locations to change from HOUR to MINUTE if changing to showing for the last xxx minutes.
A WHERE clause was added to filter on runid. This can be removed/altered to fit your needs.

11

Yes, if > 2 days data was entered in the testtime <> fields. Using 24h ("military") time, it will not confuse 11pm on the 2nd with 11am on the 3rd

OP needs to clarify shift length ("the run usually go overnight so it might be shift 2,3 and 1 from the next day. ")

12

hi, thanks for all the help. i did the query as you show. it works almost as i need. the only thing missing. is that in the hours that no production was done for that run, lets say a breakdown. those hours don't appear on the query. the goal here was to be able to get an average cases per hour produce per run. including hours where 0 cases where produce, that way i can then graph the records in that interval.

this is the query like you put

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, ActionDate), -DATEPART(HOUR, ActionDate) % 1) as 'time',
COALESCE(SUM(casecount), 0) AS 'total_cases',
COALESCE(COUNT(*), 0) AS 'total_pallets'

FROM runlog
WHERE runid = 1244
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, ActionDate), -DATEPART(HOUR, ActionDate) % 1)
ORDER BY time

I get this result.
time/case/pallet

2017-08-04 14:00:00.000 180.00 3
2017-08-04 15:00:00.000 60.00 1
2017-08-05 00:00:00.000 300.00 5
2017-08-05 01:00:00.000 60.00 1
2017-08-09 20:00:00.000 120.00 2
2017-08-14 14:00:00.000 60.00 1

so the hours missing are not there. this is a simulated run, a run would never last more than 2 days.

13

I guess this could be done using an outer join with another query that selects all hours within the interval. Haven’t used SQL server much and can’t find any information on a function/query that would select that right now.

So something like this

SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, ActionDate), -DATEPART(HOUR, ActionDate) % 1) as 'time',
COALESCE(SUM(casecount), 0) AS 'total_cases',
COALESCE(COUNT(*), 0) AS 'total_pallets'

FROM runlog RIGHT OUTER JOIN (some_select_statement_here_that_selects_hours_within_the_range) ON ActionDate=generated_ActionDate
WHERE runid = 1244
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, ActionDate), -DATEPART(HOUR, ActionDate) % 1)
ORDER BY time
14

Found this, maybe there’s some inspiration. Doesn’t have much time tonight, getting late here :slight_smile:

https://dba.stackexchange.com/questions/86486/results-for-every-hour-of-the-day-even-data-if-not-present

15

Try this. Again, I don’t have acces to a SQL server so not entirely sure it works. Not familiar with the ‘with’ statement, found it by coincidence searching something else this morning, but CTE tables should be able to do the job here. The with statement is tested in an online SQL server and atleast I know this works.

WITH dates_CTE
     AS (SELECT  @minTime AS time
         UNION ALL
         SELECT DATEADD(HOUR, 1, time)
         FROM   Dates_CTE
         WHERE  time <  @maxTime)
SELECT DATEADD(HOUR, DATEDIFF(HOUR, 0, ActionDate), -DATEPART(HOUR, ActionDate) % 1) as 'time',
COALESCE(SUM(casecount), 0) AS 'total_cases',
COALESCE(COUNT(*), 0) AS 'total_pallets'
FROM runlog
WHERE runid = 1244
GROUP BY DATEADD(HOUR, DATEDIFF(HOUR, 0, ActionDate), -DATEPART(HOUR, ActionDate) % 1)
UNION
SELECT time, 0 AS 'total_cases', 0 AS 'total_pallets'
FROM dates_CTE
OPTION(MAXRECURSION 0)
ORDER BY time

The query has one limit though, the @minTime and @maxTime parameters must extend the runs min and max ActionDate’s

EDIT:
Changed from a JOIN to an UNION