Sanprof Sanprof - 5 months ago 12
SQL Question

Group By in periods

I have a table with data

[ProtocolName], [PacketsDate], [PacketsAmount], [Flows]
'UPD' | '01/13/2016 23:55:00' | 50 | 10
'UPD' | '01/14/2016 00:02:00' | 50 | 10
'UPD' | '01/14/2016 00:03:00' | 50 | 10
'UPD' | '01/14/2016 01:10:00' | 50 | 10
'TCP' | '01/14/2016 00:00:00' | 50 | 10
'TCP' | '01/14/2016 00:03:00' | 50 | 10
'TCP' | '01/14/2016 00:10:00' | 50 | 10
'IPv6'| '01/13/2016 23:30:00' | 50 | 10
'IPv6'| '01/14/2016 00:03:00' | 50 | 10
'IPv6'| '01/14/2016 00:45:00' | 50 | 10
'IPv6'| '01/14/2016 00:50:00' | 50 | 10
'IPv6'| '01/14/2016 01:35:00' | 50 | 10
'IPv6'| '01/14/2016 01:33:00' | 50 | 10


and I have the custom generated table in stored procedure with periods

[DateFrom], [DateTo]
'01/13/2016 23:00:00' | '01/13/2016 23:30:00'
'01/13/2016 23:30:00' | '01/14/2016 00:00:00'
'01/14/2016 00:00:00' | '01/14/2016 00:30:00'
'01/14/2016 00:30:00' | '01/14/2016 01:00:00'
'01/14/2016 01:00:00' | '01/14/2016 01:30:00'
'01/14/2016 01:30:00' | '01/14/2016 02:00:00'


So, in result I need see the next columns

[DateTo], SUM([UDP_PacketsAmount]), SUM([TCP_PacketsAmount]), SUM([IPv6_PacketsAmount]), SUM([UPD_Flows]), SUM([TCP_Flows]), SUM([IPv6_Flows])
'01/13/2016 23:30:00' | 0 | 0 | 50 | 0 | 0 | 10 |
'01/14/2016 00:00:00' | 50 | 50 | 0 | 10 | 10 | 0 |
'01/14/2016 00:30:00' | 100 | 50 | 50 | 20 | 10 | 10 |
'01/14/2016 01:00:00' | 0 | 0 | 100 | 0 | 0 | 20 |
'01/14/2016 01:30:00' | 50 | 0 | 0 | 10 | 0 | 0 |
'01/14/2016 02:00:00' | 0 | 0 | 100 | 0 | 0 | 20 |


This is very difficult for me, I don't know where to start.

Answer

You could use conditional aggregation and JOIN to periods table:

SELECT p.DateTo,
 UDP_PacketsAmount = SUM(CASE WHEN ProtocolName = 'UDP' THEN PacketsAmount END),
 TCP_PacketsAmount = SUM(CASE WHEN ProtocolName = 'TCP' THEN PacketsAmount END),
 IPv6_PacketsAmount= SUM(CASE WHEN ProtocolName = 'IPv6' THEN PacketsAmount END),
 UPD_Flows         = SUM(CASE WHEN ProtocolName = 'UDP' THEN Flows END),
 TCP_Flows         = SUM(CASE WHEN ProtocolName = 'TCP' THEN Flows END), 
 IPv6_Flows        = SUM(CASE WHEN ProtocolName = 'IPv6' THEN Flows END)  
FROM tab t
RIGHT JOIN periods p
  ON t.PacketsDate >= p.DateFrom AND t.PacketsDate < p.DateTo
GROUP BY p.DateTo
ORDER BY p.DateTo;

LiveDemo

Note:

DateFrom and DateTo overlaps so I used [DateFrom, DateTo) range.