Geek Answers Handbook

The sum of SQL of different status within 24 hours by hour

I am trying to summarize status for 24 hour groups by hour. I have an order, order status and status table.

Order table:

+---------+-------------------------+ | orderid | orderdate | +---------+-------------------------+ | 1 | 2015-09-16 00:04:19.100 | | 2 | 2015-09-16 00:01:19.490 | | 3 | 2015-09-16 00:02:33.733 | | 4 | 2015-09-16 00:03:58.800 | | 5 | 2015-09-16 00:01:16.020 | | 6 | 2015-09-16 00:01:16.677 | | 7 | 2015-09-16 00:02:06.920 | +---------+-------------------------+

Order Status Table:

 +---------+----------+ | orderid | statusid | +---------+----------+ | 1 | 11 | | 2 | 22 | | 3 | 22 | | 4 | 11 | | 5 | 22 | | 6 | 33 | | 7 | 11 | +---------+----------+

Status Table:

 +----------+----------+ | statusid | status | +----------+----------+ | 11 | PVC | | 22 | CCC | | 33 | WWW | | | | +----------+----------+

I am trying to write SQL that displays a status score for 24 hours for individual orderings grouped by hour, as shown below:

 +------+-----+-----+-----+ | Hour | PVC | CCC | WWW | +------+-----+-----+-----+ | 1 | 0 | 2 | 1 | | 2 | 1 | 1 | 0 | | 3 | 1 | 0 | 0 | | 4 | 1 | 0 | 0 | +------+-----+-----+-----+

This is my SQL so far. I'm stuck trying to get the sum of each order status:

 SELECT DATEPART(hour, o.orderdate) AS Hour, SUM( CASE ( SELECT stat.status FROM Status stat, orderstatus os WHERE stat.status IN ('PVC') AND os.orderid = o.id AND os.statusid = stat.id ) WHEN 'PVC' THEN 1 ELSE 0 END ) AS PVC, SUM( CASE ( SELECT stat.status FROM Status stat, orderstatus os WHERE stat.status IN ('WWW') AND os.orderid = o.id AND os.statusid = stat.id ) WHEN 'CCC' THEN 1 ELSE 0 END ) AS CCC, SUM( CASE ( SELECT stat.status FROM Status stat, orderstatus os WHERE stat.status IN ('CCC') AND os.orderid = o.id AND os.statusid = stat.id) WHEN 'WWW' THEN 1 ELSE 0 END ) AS WWW FROM orders o WHERE o.orderdate BETWEEN DATEADD(d,-1,CURRENT_TIMESTAMP) AND CURRENT_TIMESTAMP GROUP BY DATEPART(hour, o.orderdate) ORDER BY DATEPART(hour, o.orderdate);
+5
source share
2 answers

Here you go - I ignore errors in your data, as this will not succeed if there really were duplicate identifiers in the status table, as in your example data.

 SELECT hour, sum(PVC) as PVC, sum(CCC) as CCC, sum(WWW) as WWW from ( select datepart(hour,orderdate) as hour, case when s.status = 'PVC' then 1 else 0 end as PVC, case when s.status = 'CCC' then 1 else 0 end as CCC, case when s.status = 'WWW' then 1 else 0 end as WWW from order o join orderstatus os on o.orderid = os.orderid join status s on s.statusid = os.statusid ) sub group by hour
0
source

this should come closer to you, then you should turn:

 SELECT DATEPART(HOUR,o.orderdate) AS orderDate_hour, s.status, COUNT(DISTINCT o.orderid) AS count_orderID FROM orders o INNER JOIN orderstatus os ON o.orderid = os.orderid INNER JOIN status s ON os.statusid = s.statusid WHERE o.orderdate >= DATEADD(d,-1,CURRENT_TIMESTAMP) GROUP BY DATEPART(HOUR,o.orderdate) , s.status ORDER BY DATEPART(HOUR,o.orderdate)

try this for a fulcrum:

 SELECT * FROM (SELECT DATEPART(HOUR,o.orderdate) AS orderDate_hour, s.status, COUNT(DISTINCT o.orderid) AS count_orderID FROM orders o INNER JOIN orderstatus os ON o.orderid = os.orderid INNER JOIN status s ON os.statusid = s.statusid WHERE o.orderdate >= DATEADD(d,-1,CURRENT_TIMESTAMP) GROUP BY DATEPART(HOUR,o.orderdate) , s.status) s PIVOT ( MAX(count_orderID) FOR status IN ('pvc','ccc','www')) AS p ORDER BY orderDate_hour
0
source

More articles:


All Articles