CODEWITHSUNDEEP
sqlsql-server
user1464139
user1464139

Reputation:

How can I create a cumulative report of data with SQL?

I have this table DDL:

CREATE TABLE [dbo].[Audit] (
    [AuditId] INT              IDENTITY (1, 1) NOT NULL,
    [Entity]  INT              NOT NULL,
    [Action]  INT              NOT NULL,
    [Id]      UNIQUEIDENTIFIER NULL,
    CONSTRAINT [PK_Audit] PRIMARY KEY CLUSTERED ([AuditId] ASC)
);

Update - I added the missing SQL here. Sorry about that

What I did was create a SQL report using this SQL that would show me how much activity there was each day:

select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
       [Col2] = convert(varchar, count(*))
from Audit a
group by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101)
order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

The output looks like this:

col1        Col2
03/05/2017  1
03/04/2017  20
03/03/2017  10
03/02/2017  5
03/01/2017  10

What I now need to do is to create a SQL select that shows me a cumulative value and not the count. So I need SQL that clould create a report that would show this

col1        Col2
03/05/2017  46
03/04/2017  45
03/03/2017  25
03/02/2017  15
03/01/2017  10

Does anyone have any idea as to how I can modify my SQL to create this type of a report?

Note that I am really looking for a one command solution as it is executed from .net framework and if there are multiple commands in the solution I think I would need to work out how to put this into a stored procedure somehow.

Upvotes: 5

Views: 760

Answers (6)

mansi
mansi

Reputation: 877

Only change your query by below query (this query works only when you are using SQL SERVER 2012)

Try This:

    DECLARE Audit TABLE (date datetime,
        [col1]  int  NOT NULL
    )

    insert into Audit(date,Col1)
    VALUES
    ('03/05/2017',  1  )
    ,('03/04/2017',  20 )
    ,('03/03/2017',  10 )
    ,('03/02/2017',  5  )
    ,('03/01/2017',  10 )


    select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
           [Col2] = SUM(col1)  over (Order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101))
    from Audit a
    order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

Upvotes: 1

KumarHarsh
KumarHarsh

Reputation: 5094

if you give data sample of Audit Table and not data of your result set thn may be you get optmize and accurate query.

Using Sql 2012,

DECLARE @Audit TABLE (
    [col1]  datetime  NOT NULL
    ,[col2]  INT  NOT NULL
)

insert into @audit(col1,Col2)
VALUES
('03/05/2017',  1  )
,('03/04/2017',  20 )
,('03/03/2017',  10 )
,('03/02/2017',  5  )
,('03/01/2017',  10 )

;WITH CTE as
(
select col1 ,sum(col2) over(ORDER BY col1 desc  RANGE BETWEEN 
CURRENT ROW AND UNBOUNDED FOLLOWING) AS col2
from @Audit
)
SELECT * FROM cte   
order by col1 desc

Upvotes: 2

John Cappelletti
John Cappelletti

Reputation: 82010

You can use the Window Functions Sum() Over 

Select col1 = convert(varchar(10),cast(dateadd(HOUR, 8, a.date) as date),101)
      ,col2 = sum(count(*))  over (Order by cast(dateadd(HOUR, 8, a.date) as date))
 From  Audit a
 Group by cast(dateadd(HOUR, 8, a.date) as date)
 Order By cast(dateadd(HOUR, 8, a.date) as date) Desc

Returns

col1        Col2
03/05/2017  46
03/04/2017  45
03/03/2017  25
03/02/2017  15
03/01/2017  10

Upvotes: 5

Naim Halai
Naim Halai

Reputation: 375

You can try something like below.

IF OBJECT_ID('tempdb..#CumulativeSum') IS NOT NULL
    DROP TABLE #CumulativeSum

SELECT t1.col1, t1.col2 AS col2Actual, SUM(t2.col2) AS col2
INTO #CumulativeSum
FROM Audit t1
INNER JOIN Audit t2 on t1.col1 >= t2.col1
GROUP BY t1.col1, t1.col2
ORDER BY t1.col1

SELECT col1, col2 FROM #CumulativeSum ORDER BY col2 DESC

The answers was copied from below link as FiReTiTi suggested.

how to get cumulative sum

Edit - For having query into single command.

SELECT col1, col2 FROM(
    SELECT TOP 100 PERCENT t1.col1, t1.col2 AS col2Actual, SUM(t2.col2) AS col2
    FROM Audit t1
    INNER JOIN Audit t2 on t1.col1 >= t2.col1
    GROUP BY t1.col1, t1.col2
    ORDER BY t1.col1)x
ORDER BY col2 DESC

Hope this will help achieving your exact requirements.

Upvotes: 1

VHao
VHao

Reputation: 742

Use "with" statement to have the sum in one command. Like this:

WITH TBL ([col1], [col2])  
AS  
(  
SELECT [col1] = CONVERT(DATE, DATEADD(HOUR, 8, a.date)),
       [Col2] = COUNT(*)
FROM Audit a
GROUP BY CONVERT(DATE, DATEADD(HOUR, 8, a.date))
)  
SELECT  [col1] = CONVERT(VARCHAR(10), t1.[col1], 101), 
        [col2] = CONVERT(VARCHAR, SUM(t2.[Col2]))
FROM TBL t1 INNER JOIN TBL t2 on t1.col1 >= t2.col1
GROUP BY t1.col1
ORDER BY t1.col1 DESC

Good luck!

Upvotes: 4

nazark
nazark

Reputation: 1240

try the below query 

select [col1] = CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) ,
       [Col2] = (select count(*) from Audit b where b.date<= DATEADD(HOUR, 8, a.date))
from Audit a
group by DATEADD(HOUR, 8, a.date)
order by CONVERT(VARCHAR(10), DATEADD(HOUR, 8, a.date), 101) desc

Upvotes: 1

Related Questions