# SQL parts per hour, min, dynamic

**URL:** <https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517>\
**Category:** Ignition\
**Created:** [January 8, 2018, 5:43pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517 "2018-01-08T17:43:07Z")\
**Posts on this page:** 15\
**Page:** 1

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [January 8, 2018, 5:43pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/1 "2018-01-08T17:43:07Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 8, 2018, 6:41pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/2 "2018-01-08T18:41:35Z")

</div>

A query like this might work

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

---

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [January 8, 2018, 7:03pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/3 "2018-01-08T19:03:11Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 8, 2018, 8:57pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/4 "2018-01-08T20:57:03Z")

</div>

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

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

```auto
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’

---

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [January 8, 2018, 9:35pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/5 "2018-01-08T21:35:09Z")

</div>

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?

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 8, 2018, 9:56pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/6 "2018-01-08T21:56:46Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 8, 2018, 10:31pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/7 "2018-01-08T22:31:51Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 8, 2018, 10:36pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/8 "2018-01-08T22:36:09Z")

</div>

> [@Matrix\_Engineering](#):
>
> SELECT datepart(hour, [TestTime]) as hour\_offloaded, count(\*) as qty FROMwhere aand band TestTime \> ?and TestTime \< ?group by datepart(hour, [TestTime])order by datepart(hour, [TestTime])

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

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 8, 2018, 10:38pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/9 "2018-01-08T22:38:28Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 8, 2018, 11:18pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/10 "2018-01-08T23:18:47Z")

</div>

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

```auto
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.

---

<div class="post-metadata">

**Author:** ![Matrix\_Engineering](https://sea1.discourse-cdn.com/inductiveautomation/user_avatar/forum.inductiveautomation.com/matrix_engineering/32/19781_2.png) [@Matrix\_Engineering](https://forum.inductiveautomation.com/u/Matrix_Engineering)\
**Post date:** [January 9, 2018, 9:57pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/11 "2018-01-09T21:57:47Z")

</div>

> [@juang](#):
>
> the run usually go overnight so it might be shift 2,3 and 1 from the next day.

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. ")

---

<div class="post-metadata">

**Author:** ![juang](https://avatars.discourse-cdn.com/v4/letter/j/9dc877/32.png) [@juang](https://forum.inductiveautomation.com/u/juang)\
**Post date:** [January 11, 2018, 8:24pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/12 "2018-01-11T20:24:17Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 11, 2018, 8:53pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/13 "2018-01-11T20:53:16Z")

</div>

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

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

```

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 11, 2018, 9:15pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/14 "2018-01-11T21:15:34Z")

</div>

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

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

---

<div class="post-metadata">

**Author:** ![Claus\_Nielsen](https://avatars.discourse-cdn.com/v4/letter/c/77aa72/32.png) [@Claus\_Nielsen](https://forum.inductiveautomation.com/u/Claus_Nielsen)\
**Post date:** [January 12, 2018, 1:41pm UTC](https://forum.inductiveautomation.com/t/sql-parts-per-hour-min-dynamic/16517/15 "2018-01-12T13:41:44Z")

</div>

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.

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