How can I create an aggregate data report with SQL?

Question

I have this DDL table:

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, that

What I did was create a SQL report using this SQL, which will show me how much activity there was every 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 result is as follows:

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

Now I need to make a SQL selection that shows me the cumulative value, not the score. So I need SQL that could create a report that shows 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 know how I can modify my SQL to create this type of report?

Please note that I'm really looking for one command solution, because it is executed from the .net structure, and if there are several commands in the solution, I think I will need to figure out how to do this in the stored procedure.

Answer 1

You can use Window Sum() Over functions

 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 

Answer 2

Use the 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

Answer 3

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 were copied from the following link as suggested by FiReTiTi.

how to get the cumulative amount

Edit - for a request in one 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 helps you achieve your exact requirements.

Answer 4

if you give a sample of the audit table data, and not the data of your result set, you may receive an optimization and an exact request.

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 

Answer 5

try the following 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 

Answer 6

Change your query only by query (this query only works when using SQL SERVER 2012)

Try the following:

  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