CODEWITHSUNDEEP
sql-server
red
red

Reputation: 119

Rolling up values in SQL Server

This is the result of my first sql statement:

SELECT 
    count(*) countQuarter, Hour, Quarter,
    ROW_NUMBER() OVER(ORDER BY Hour, Quarter ASC) AS rownum
FROM
     (SELECT [ID] ,[simulationID] ,[time],
      replace(str(time/3600,len(ltrim(time/3600))+abs(sign(time/359999)-1)) + ':' +         str((time/60)%60,2) + ':' + str(time%60,2),' ','0') dtString, 
      (time/3600) Hour, (time/60)%60 Minute, case  when (time/60)%60<15 then 15 when 
      (time/60)%60<30 then 30 when (time/60)%60<45 then 45 when (time/60)%60<60 then 60 end 
      Quarter ,[person] ,[link] ,[vehicle] FROM [TEST].[dbo].[evtLinks]
      WHERE simulationID=@simulationID) B
 GROUP BY Hour, Quarter

which gives the following results:

Count     Hour  Quarter Rownum
497         0     15      1
842         0     30      2
1033        0     45      3
1120        0     60      4
1235        1     15      5
1267        1     30      6
1267        1     45      7
1267        1     60      8
1267        2     15      9
1267        2     30     10

I desire a result, where the column fullCount is the sum of the Count of the actual row and the next 3!

Count    Hour  Quarter  Rownum  Fullcount
497         0     15      1      3492
842         0     30      2      4230
1033        0     45      3      4655 
1120        0     60      4       ...
1235        1     15      5
1267        1     30      6
1267        1     45      7
1267        1     60      8
1267        2     15      9
1267        2     30     10

How can this be done with grouping or analytical functions in SQL Server?

Upvotes: 0

Views: 232

Answers (2)

Kahn
Kahn

Reputation: 1660

Tested the logical scenario and it works, but I don't have your data, so in your case it should look roughly like this:

;WITH CTE as (SELECT count(*) countQuarter,Hour,Quarter,
       ROW_NUMBER() OVER(ORDER BY Hour, Quarter ASC) AS rownum
FROM
     (SELECT [ID] ,[simulationID] ,[time],
      replace(str(time/3600,len(ltrim(time/3600))+abs(sign(time/359999)-1)) + ':' +         str((time/60)%60,2) + ':' + str(time%60,2),' ','0') dtString, 
      (time/3600) Hour, (time/60)%60 Minute, case  when (time/60)%60<15 then 15 when 
      (time/60)%60<30 then 30 when (time/60)%60<45 then 45 when (time/60)%60<60 then 60 end 
      Quarter ,[person] ,[link] ,[vehicle] FROM [TEST].[dbo].[evtLinks]
      WHERE simulationID=@simulationID) B
 GROUP BY Hour, Quarter)
SELECT *, CA.Fullcount 
FROM CTE
CROSS APPLY (SELECT SUM(countQuarter) Fullcount FROM CTE C WHERE C.ID BETWEEN CTE.ID AND CTE.ID+3) CA

Upvotes: 1

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239764

For SQL Server 2012, yes this can be done:

declare @t table ([Count] int,[Hour] int,[Quarter] int,Rownum int)
insert into @t([Count],[Hour],[Quarter],Rownum) values
(497    ,    0  ,  15  ,   1 ),
(842    ,    0  ,  30  ,   2 ),
(1033   ,    0  ,  45  ,   3 ),
(1120   ,    0  ,  60  ,   4 ),
(1235   ,    1  ,  15  ,   5 ),
(1267   ,    1  ,  30  ,   6 ),
(1267   ,    1  ,  45  ,   7 ),
(1267   ,    1  ,  60  ,   8 ),
(1267   ,    2  ,  15  ,   9 ),
(1267   ,    2  ,  30  ,  10 )

select *,SUM([Count]) OVER (
                         ORDER BY rownum
                         ROWS BETWEEN CURRENT ROW AND
                                      3 FOLLOWING)
from @t

Here I'm using @t as your current result set - you may be able to adapt this into your current query or may have to place your current query in a CTE.

Unfortunately, the ROWS BETWEEN syntax is only valid on 2012 and later.

Upvotes: 1

Related Questions