CODEWITHSUNDEEP
sql-serversql-server-2008group-bysum
rashfmnb
rashfmnb

Reputation: 10538

Sum Column as an Accumulation Based on a Time Period

I have a table in which records are inserted at different periods (each record contains a column called 'Amount').

I want to show the total amount acummulation, after each 5 seconds. I have tried with the following query without success:

SELECT Sum(totalamount) AS RealTimeTotalAmount,
       Datepart(second, createstamp) / 5 AS dp
FROM   [order]
WHERE 
       createstamp BETWEEN Dateadd(s, -5, Getdate()) AND Getdate()
GROUP  BY Datepart(second, createstamp) / 5

The problem I am facing is, that it shows me the 'accumulative sum as per each second' and I want to see it like '(accumulative sum as per each second + total accumulative amount till that second)'

Here is how the source data looks like: 

-----------------------------------------------------------
|OrderID  | CreateStamp             | TotalAmount         |
-----------------------------------------------------------
|1        |2015-03-22 15:26:05.620  | 10                  |
-----------------------------------------------------------
|2        |2015-03-22 15:26:05.653  | 20                  |
-----------------------------------------------------------
|3        |2015-03-22 15:26:05.660  | 10                  |
-----------------------------------------------------------
|4        |2015-03-22 15:26:06.663  | 10                  |
-----------------------------------------------------------
|5        |2015-03-22 15:26:06.670  | 30                  |
-----------------------------------------------------------

Essentially, I want the resulting query to return as follows:

----------------------------------------
|Period          | Accumulative Amount | 
----------------------------------------
|0 to 5 seconds  | 30                  |
----------------------------------------
|0 to 10 seconds | 80                  |
----------------------------------------

This is basically an accumulation from 0 time to multiples of 5.for last 5 seconds basically i am calculating the amount for the whole day up to the time when i execute this query and for example the amount for whole day before this time was 50 so result table should look like 

----------------------------------------
|0 to 5 seconds  | 30 + 50 = 80        |
----------------------------------------
|0 to 10 seconds | 80 + 80 = 160       |
----------------------------------------

Upvotes: 1

Views: 1174

Answers (2)

ughai
ughai

Reputation: 9890

you can try something like this.

Input Data

DECLARE @Orders TABLE
(
    OrderId INT,
    CreateStamp DATETIME,
    TotalAmount NUMERIC(9,2)
)
INSERT INTO @Orders
SELECT 1,'2015-03-22 15:26:05.620',400
UNION ALL SELECT 2,'2015-03-22 15:26:04.653',500
UNION ALL SELECT 3,'2015-03-22 15:26:05.660',600
UNION ALL SELECT 4,'2015-03-22 15:26:06.663',700
UNION ALL SELECT 5,'2015-03-22 15:26:06.670',900
UNION ALL SELECT 6,'2015-03-22 15:26:05.660',600
UNION ALL SELECT 7,'2015-03-22 15:26:09.663',700
UNION ALL SELECT 8,'2015-03-22 15:26:12.670',900

Query

;WITH CTE as 
(
    SELECT DATEDIFF(minute,0,CreateStamp)totalminutes,Datepart(second, CreateStamp ) / 5 sec,SUM(TotalAmount) TotalAmount
    FROM @Orders
    GROUP BY DATEDIFF(minute,0,CreateStamp),Datepart(second, CreateStamp) / 5
)
SELECT  DATEADD(minute,totalminutes,0) dt,sec,(SELECT SUM(TotalAmount) FROM cte WHERE totalminutes <=c2.totalminutes and sec <=c2.sec)
FROM CTE c2
ORDER BY sec;

I have added a GROUP BY DATEDIFF(minute,0,CreateStamp) to separate seconds for different dates and minutes.

Upvotes: 1

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

If I understand you correctly:

DECLARE @t TABLE
    (
      ID INT ,
      D DATETIME ,
      A MONEY
    )
DECLARE @mind DATETIME ,
    @maxd DATETIME


INSERT  INTO @t
VALUES  ( 1, '2015-04-07 13:49:15.000', 5 ),
        ( 2, '2015-04-07 13:49:17.000', 15 ),
        ( 3, '2015-04-07 13:49:35.000', 2 ),
        ( 4, '2015-04-07 13:49:45.000', 4 ),
        ( 5, '2015-04-07 13:49:49.000', 20 ),
        ( 6, '2015-04-07 13:50:05.000', 20 ),
        ( 7, '2015-04-07 13:50:09.000', 3 ),
        ( 8, '2015-04-07 13:50:09.000', 3 ),
        ( 9, '2015-04-07 13:50:10.000', 1 ),
        ( 10, '2015-04-07 13:50:15.000', 1 )


SELECT  @mind = MIN(d) ,
        @maxd = MAX(d)
FROM    @t;

WITH    cte
          AS ( SELECT   @mind AS d
               UNION ALL
               SELECT   DATEADD(ss, 5, d)
               FROM     cte
               WHERE    cte.d <= @maxd
             )

SELECT cte.d, SUM(A) AS A FROM cte
JOIN @t t ON t.D < cte.d
GROUP BY cte.d

Output:

d                       A
2015-04-07 13:49:20.000 20.00
2015-04-07 13:49:25.000 20.00
2015-04-07 13:49:30.000 20.00
2015-04-07 13:49:35.000 20.00
2015-04-07 13:49:40.000 22.00
2015-04-07 13:49:45.000 22.00
2015-04-07 13:49:50.000 46.00
2015-04-07 13:49:55.000 46.00
2015-04-07 13:50:00.000 46.00
2015-04-07 13:50:05.000 46.00
2015-04-07 13:50:10.000 72.00
2015-04-07 13:50:15.000 73.00
2015-04-07 13:50:20.000 74.00

Upvotes: 0

Related Questions