CODEWITHSUNDEEP
sqlsql-servert-sqlstored-procedures
Sanprof
Sanprof

Reputation: 409

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.

Upvotes: 0

Views: 63

Answers (3)

Lukasz Szozda
Lukasz Szozda

Reputation: 176074

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.

If you need (DateFrom, DateTo] just change ON t.PacketsDate > p.DateFrom AND t.PacketsDate <= p.DateTo

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82010

I use a UDF to create Dynamic Date Ranges (listed below). I've supplied the values given, but can use parameters i.e. @StartDate, @EndDate,@Increment

Select DateR1
      ,DateR2
      ,UDP_Packets  = SUM([UDP_PacketsAmount])
      ,UDP_Packets  = SUM([TCP_PacketsAmount])
      ,IPv6_Packets = SUM([IPv6_PacketsAmount])
      ,UPD_Flows    = SUM([UPD_Flows])
      ,TCP_Flows    = SUM([TCP_Flows])
      ,IPv6_Flows   = SUM([IPv6_Flows])
 From YourTableName A
 Join (Select DateR1=RetVal,DateR2=DateAdd(MINUTE,30,RetVal) from [dbo].[udf-Create-Range-Date]('2016-01-13 23:00:00','2016-01-14 02:00:00','MI',30)) B
   on  PacketsDate Between DateR1 and DateR2 and PacketsDate<DateR2
 Group By DateR1,DateR2
 Order By DateR1

The Sub-query returns 

DateR1                  DateR2
2016-01-13 23:00:00.000 2016-01-13 23:30:00.000
2016-01-13 23:30:00.000 2016-01-14 00:00:00.000
2016-01-14 00:00:00.000 2016-01-14 00:30:00.000
2016-01-14 00:30:00.000 2016-01-14 01:00:00.000
2016-01-14 01:00:00.000 2016-01-14 01:30:00.000
2016-01-14 01:30:00.000 2016-01-14 02:00:00.000
2016-01-14 02:00:00.000 2016-01-14 02:30:00.000

The UDF to create dynamic date ranges

CREATE FUNCTION [dbo].[udf-Create-Range-Date] (@DateFrom datetime,@DateTo datetime,@DatePart varchar(10),@Incr int)

Returns 
@ReturnVal Table (RetVal datetime)

As
Begin
    With DateTable As (
        Select DateFrom = @DateFrom
        Union All
        Select Case @DatePart
               When 'YY' then DateAdd(YY, @Incr, df.dateFrom)
               When 'QQ' then DateAdd(QQ, @Incr, df.dateFrom)
               When 'MM' then DateAdd(MM, @Incr, df.dateFrom)
               When 'WK' then DateAdd(WK, @Incr, df.dateFrom)
               When 'DD' then DateAdd(DD, @Incr, df.dateFrom)
               When 'HH' then DateAdd(HH, @Incr, df.dateFrom)
               When 'MI' then DateAdd(MI, @Incr, df.dateFrom)
               When 'SS' then DateAdd(SS, @Incr, df.dateFrom)
               End
        From DateTable DF
        Where DF.DateFrom < @DateTo
    )

    Insert into @ReturnVal(RetVal) Select DateFrom From DateTable option (maxrecursion 32767)

    Return
End

Upvotes: 1

Jaydip Jadhav
Jaydip Jadhav

Reputation: 12309

Use simple CASE WHEN...END statements to make Summation

SELECT DateTo,
   SUM(CASE WHEN ProtocolName='UPD' THEN PacketsAmount END) AS UDP_PacketsAmount,
   SUM(CASE WHEN ProtocolName='TCP' THEN PacketsAmount END) AS TCP_PacketsAmount,
   SUM(CASE WHEN ProtocolName='IPv6' THEN PacketsAmount END) AS IPv6_PacketsAmount,
   SUM(CASE WHEN ProtocolName='UPD' THEN Flows END) AS UPD_Flows,
   SUM(CASE WHEN ProtocolName='TCP' THEN Flows END) AS TCP_PacketsAmount,
   SUM(CASE WHEN ProtocolName='IPv6' THEN Flows END) AS IPv6_PacketsAmount
FROM TableName
Group BY  DateTo

Upvotes: 2

Related Questions