Different Rolling 12 Months - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Different Rolling 12 Months

Author

Topic

andrewz00
Starting Member

15 Posts

Posted - 2009-01-22 : 14:12:30

OK i have read many posts about rolling totals etc... cant quite find and answer to this one.

i have about 9 million records... i need to get 12 month rolling totals using sql in sql server 2005

Meaning, each month as a 12 month time frame... so 12 months ending jan 08, 12 months ending feb 08, mar, apr.. etc...

right now i am doing it the long way, running 12 unioned queries... PLEASE tell me there is a better way.

the big and ugly looks like this:

declare @dToday datetime,

@dMonth DATETIME

set @dToday = CAST(FLOOR(CAST(GetDate() AS FLOAT))AS DATETIME)

set @dMonth = dateadd(dd,1-datepart(dd,@dToday),@dToday)

SELECT

[Phleb] = 'SDP',

[BSI] = Count(Distinct Case When R.RegionAbbreviation Like 'BSI%' Then DR.donorcode Else Null End),

[HV] = Count(Distinct Case When R.RegionAbbreviation Like 'HV%' Then DR.donorcode Else Null End),

[NJ] = Count(Distinct Case When R.RegionAbbreviation Like 'NJ%' Then DR.donorcode Else Null End),

[NY] = Count(Distinct Case When R.RegionAbbreviation Like 'NY%' Then DR.donorcode Else Null End),

[LI] = Count(Distinct Case When R.RegionAbbreviation Like 'LI%' Then DR.donorcode Else Null End),

[NYBC] = Count(Distinct DR.donorcode),

[Start] = DateAdd(mm,-12,@dMonth),

[End]= DateAdd(d,-1,DateAdd(mm,-0,@dMonth))

FROM ctdonationrollup DR with (noLock)

Inner Join ctDonorLastShowUp DLast with (noLock)

On DR.DonorCode = DLast.DonorCode

Inner Join xtRegion R with (noLock)

On DLast.LastRegion = R.RegionID

WHERE phlebotomycode in ('B','F','G')

and datedonation Between DateAdd(mm,-12,@dMonth) AND DateAdd(d,-1,DateAdd(mm,-0,@dMonth))

UNION

SELECT

[Phleb] = 'SDP',

[BSI] = Count(Distinct Case When R.RegionAbbreviation Like 'BSI%' Then DR.donorcode Else Null End),

[HV] = Count(Distinct Case When R.RegionAbbreviation Like 'HV%' Then DR.donorcode Else Null End),

[NJ] = Count(Distinct Case When R.RegionAbbreviation Like 'NJ%' Then DR.donorcode Else Null End),

[NY] = Count(Distinct Case When R.RegionAbbreviation Like 'NY%' Then DR.donorcode Else Null End),

[LI] = Count(Distinct Case When R.RegionAbbreviation Like 'LI%' Then DR.donorcode Else Null End),

[NYBC] = Count(Distinct DR.donorcode),

[Start] = DateAdd(mm,-13,@dMonth),

[End]= DateAdd(d,-1,DateAdd(mm,-1,@dMonth))

FROM ctdonationrollup DR with (noLock)

Inner Join ctDonorLastShowUp DLast with (noLock)

On DR.DonorCode = DLast.DonorCode

Inner Join xtRegion R with (noLock)

On DLast.LastRegion = R.RegionID

WHERE phlebotomycode in ('B','F','G')

and datedonation Between DateAdd(mm,-13,@dMonth) AND DateAdd(d,-1,DateAdd(mm,-1,@dMonth))

If any one could point me to a known post or if you have an answer please let me know..

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-22 : 15:27:02

See if this is any better. You'll need to fill in the rest of the regions but the all 12 periods should be covered by this statement:


declare @dMonth DATETIME
set @dMonth = dateadd(month, datediff(month, 0, getdate()), 0)

select ca.[BSI]
       ,ca.[HV]
       ,[start] = dateadd(month, n.number-12, @dMonth)
       ,[end] = dateadd(month, n.number, @dMonth)
from   (
       select number * -1 [number]
       from   master..spt_values
       where  type = 'p'
       and    number < 12
       ) n
cross apply (
       select [BSI] = count(distinct Case When R.RegionAbbreviation Like 'BSI%' Then DR.donorcode End)
              ,[HV] = count(distinct Case When R.RegionAbbreviation Like 'HV%' Then DR.donorcode End)
       from   ctdonationrollup DR with (noLock)
       Inner Join ctDonorLastShowUp DLast with (noLock)
                     On DR.DonorCode = DLast.DonorCode
       Inner Join xtRegion R with (noLock)
                     On DLast.LastRegion = R.RegionID
       where  phlebotomycode in ('B','F','G')
       and    datedonation >= dateadd(month, n.number-12, @dMonth)
       and    datedonation <= dateadd(month, n.number, @dMonth)
       ) ca

Be One with the Optimizer
TG

andrewz00
Starting Member

15 Posts

Posted - 2009-01-22 : 16:00:07

Absolutly BEAUTIFUL! Always been wondering about spt_values!

Thank you very much!

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2009-01-22 : 16:30:55

You're welcome.

I'm just using spt_values as a table of numbers. Since it is a system table it's not necessarily guaranteed to exist in future versions. If you don't have other needs for a permanent numbers table you could either create a derived/temp/tableVar just for this SP or even use a function:
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=47685

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources