Last 30 days aggregate query

Question

Post reply

Last 30 days aggregate query

kiril.lazarov.77

Hall of Fame

Points: 3742
More actions
December 1, 2014 at 4:19 pm

#292980

Hi, I'm trying to write a query that returns last 30 days data and sums the amount by day. However I need to do it for every year not just the current one(I need to go back as far as 2000). Thanks.
declare @t table (id int identity(1,1), dt datetime, amt MONEY)
insert into @t (dt, amt)
select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL
select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL
select '2014-11-20 23:19:55.003' as dt, 66 as amt UNION ALL
select '2014-11-18 23:15:19.930' as dt, 36 as amt UNION ALL
select '2014-11-18 23:11:34.413' as dt, 66 as amt UNION ALL
select '2014-10-14 23:07:17.837' as dt, 66 as amt UNION ALL
select '2014-10-10 22:59:50.917' as dt, 56 as amt UNION ALL
select '2014-10-16 22:59:20.887' as dt, 66 as amt UNION ALL
select '2014-10-10 22:57:26.200' as dt, 66 as amt UNION ALL
select '2013-11-30 23:39:35.717' as dt, 5 as amt UNION ALL
select '2013-11-25 14:29:16.747' as dt, 66 as amt UNION ALL
select '2013-11-25 23:25:33.480' as dt, 66 as amt UNION ALL
select '2013-11-25 23:19:55.023' as dt, 53 as amt UNION ALL
select '2013-11-14 21:13:19.930' as dt, 76 as amt UNION ALL
select '2013-11-14 13:11:34.413' as dt, 4 as amt UNION ALL
select '2013-11-14 23:07:17.837' as dt, 66 as amt UNION ALL
select '2013-10-10 12:53:50.917' as dt, 2 as amt UNION ALL
select '2013-10-6 22:59:20.887' as dt, 66 as amt UNION ALL
select '2013-10-1 21:57:46.200' as dt, 66 as amt UNION ALL
select '2012-11-30 23:39:35.717' as dt, 66 as amt UNION ALL
select '2012-11-30 14:29:16.747' as dt, 4 as amt UNION ALL
select '2012-11-22 23:25:33.480' as dt, 66 as amt UNION ALL
select '2012-11-21 23:19:55.023' as dt, 66 as amt UNION ALL
select '2012-11-21 21:13:19.930' as dt, 4 as amt UNION ALL
select '2012-11-15 13:11:34.413' as dt, 66 as amt UNION ALL
select '2012-11-14 23:07:17.837' as dt, 3 as amt UNION ALL
select '2012-10-10 12:53:50.917' as dt, 66 as amt UNION ALL
select '2012-10-6 22:59:20.887' as dt, 1
select * from @t
--expected output
select '2014-11-30' AS dt, 71 AS Amt UNION ALL
select '2014-11-22' AS dt, 62 AS Amt UNION ALL
select '2014-11-20' AS dt, 66 AS Amt UNION ALL
select '2014-11-18' AS dt, 102 AS Amt UNION ALL
select '2013-11-30' AS dt, 5 AS Amt UNION ALL
select '2013-11-25' AS dt, 62 AS Amt UNION ALL
select '2013-11-20' AS dt, 185 AS Amt UNION ALL
select '2013-11-14' AS dt, 146 AS Amt UNION ALL
select '2012-11-30' AS dt, 70 AS Amt UNION ALL
select '2012-11-22' AS dt, 66 AS Amt UNION ALL
select '2012-11-21' AS dt, 70 AS Amt UNION ALL
select '2012-11-15' AS dt, 55 AS Amt UNION ALL
select '2012-11-14' AS dt, 3 AS Amt

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

ScottPletcher SSC Guru Points: 101044 More actions · Answer 1

SELECT CONVERT(varchar(10), DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0), 120) AS dt, SUM(amt) AS Amt

FROM @t

GROUP BY DATEADD(DAY, DATEDIFF(DAY, 0, dt), 0)

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

kiril.lazarov.77 Hall of Fame Points: 3742 More actions · Answer 2

Thank you. Your query also returns data for October, I Only want to return data for the last 30 days so the oldest data I want to see should be November 1st. November 1st to December 1st, this range should be used for each year not just the current year. Is it possible?

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 3

Crud. I can't post the answer from where I'm at. I have a solution for you and will post it as soon as I get home.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004177 More actions · Answer 4

OK, here we go. This will go back for 30 days (not including "today" because it's a partial day) and also report on the same time span for all years. I let leap year take care of itself so that you would still get 30 days. It also reports "0" for the span StartDate for any year that may be missing.

If you need to include "today" (which I believe would be a mistake but it is ultimately up to you), let me know. It's a minor tweak to make that change.

Here's your original data and the code. You probably won't need the 1,000 year capability but, then again, you won't ever have to worry about the number of years. 😛

--===== Original test data

declare @t table (id int identity(1,1), dt datetime, amt MONEY)

insert into @t (dt, amt)

select '2014-11-30 23:39:35.717' as dt, 66 as amt UNION ALL

select '2014-11-30 23:29:16.747' as dt, 5 as amt UNION ALL

select '2014-11-22 23:25:33.780' as dt, 62 as amt UNION ALL

select '2014-11-20 23:19:55.003' as dt, 66 as amt UNION ALL

select '2014-11-18 23:15:19.930' as dt, 36 as amt UNION ALL

select '2014-11-18 23:11:34.413' as dt, 66 as amt UNION ALL

select '2014-10-14 23:07:17.837' as dt, 66 as amt UNION ALL

select '2014-10-10 22:59:50.917' as dt, 56 as amt UNION ALL

select '2014-10-16 22:59:20.887' as dt, 66 as amt UNION ALL

select '2014-10-10 22:57:26.200' as dt, 66 as amt UNION ALL

select '2013-11-30 23:39:35.717' as dt, 5 as amt UNION ALL

select '2013-11-25 14:29:16.747' as dt, 66 as amt UNION ALL

select '2013-11-25 23:25:33.480' as dt, 66 as amt UNION ALL

select '2013-11-25 23:19:55.023' as dt, 53 as amt UNION ALL

select '2013-11-14 21:13:19.930' as dt, 76 as amt UNION ALL

select '2013-11-14 13:11:34.413' as dt, 4 as amt UNION ALL

select '2013-11-14 23:07:17.837' as dt, 66 as amt UNION ALL

select '2013-10-10 12:53:50.917' as dt, 2 as amt UNION ALL

select '2013-10-6 22:59:20.887' as dt, 66 as amt UNION ALL

select '2013-10-1 21:57:46.200' as dt, 66 as amt UNION ALL

select '2012-11-30 23:39:35.717' as dt, 66 as amt UNION ALL

select '2012-11-30 14:29:16.747' as dt, 4 as amt UNION ALL

select '2012-11-22 23:25:33.480' as dt, 66 as amt UNION ALL

select '2012-11-21 23:19:55.023' as dt, 66 as amt UNION ALL

select '2012-11-21 21:13:19.930' as dt, 4 as amt UNION ALL

select '2012-11-15 13:11:34.413' as dt, 66 as amt UNION ALL

select '2012-11-14 23:07:17.837' as dt, 3 as amt UNION ALL

select '2012-10-10 12:53:50.917' as dt, 66 as amt UNION ALL

select '2012-10-6 22:59:20.887' as dt, 1

;

--========= Do daily sums for each of the last 30 whole days (not including today because it's a partial day)

-- and that same date span from the minimum year in the table to the maximum year in the table even

-- if a whole year is missing.

WITH

E1(N) AS (SELECT 1 FROM (VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1))v(N)) --10^1 or 10 rows

,E3(N) AS (SELECT 1 FROM E1 a, E1 b, E1 c) --10^3 or 1,000 rows

,Tally(N) AS (SELECT TOP (SELECT MAX(YEAR(dt))-MIN(YEAR(dt))+1 FROM @t) -(ROW_NUMBER() OVER (ORDER BY N)-1) FROM E3)

,DateSpan AS (SELECT StartSpan = DATEADD(yy,t.N,DATEADD(dd,DATEDIFF(dd,30,GETDATE()),0))

,EndSpan = DATEADD(yy,t.N,DATEADD(dd,DATEDIFF(dd, 0,GETDATE()),0))

FROM Tally t)

SELECT DT = CAST(DATEADD(dd,DATEDIFF(dd,0,ISNULL(td.dt,ds.StartSpan)),0) AS DATE)

,Amt = SUM(ISNULL(amt,0))

FROM @t td

RIGHT JOIN DateSpan ds

ON td.dt > = ds.StartSpan AND td.dt < ds.EndSpan

GROUP BY DATEDIFF(dd,0,ISNULL(td.dt,ds.StartSpan))

ORDER BY DT

;

Here are the results (again, does NOT include "today" because it's a partial day).

DT Amt

---------- ---------------------

2012-11-14 3.00

2012-11-15 66.00

2012-11-21 70.00

2012-11-22 66.00

2012-11-30 70.00

2013-11-14 146.00

2013-11-25 185.00

2013-11-30 5.00

2014-11-18 102.00

2014-11-20 66.00

2014-11-22 62.00

2014-11-30 71.00

Also note that this code forms a full Cartesian product because there are no indexes. I strongly recommend that you put an index on the real table on the combination of the DT and Amt columns, which will turn the code to a rather nice INDEX SEEK/SCAN that will use only the correct rows instead of using all of the rows once for each year in the data.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

serg-52 SSCrazy Eights Points: 9913 More actions · Answer 5

A version when no rows needed for the year where no data for the given interval exists.