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.

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’

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?

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

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

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.

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])

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.

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 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

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