October 6, 2009 at 3:44 am
HI - Anyone know what is the best method to grab a rolling MAT (Moving Annual Total)-
I have a table that has the date format 200802, I need to automate each month when the script is run to grab the latest rolling MAT and carry forward previous rolling MAT'S - I can do the date capture for the the latest year but not sure how to move forward all previous as I am aware doing it this way will when the date moves to next month it will miss the previous.
Any comments or ideas would be greatly appreciated
October 6, 2009 at 3:47 am
first reply i thought of was, check out your local hardware store, then i read your question properly instead of skimming it.
I saw this solution on another site, link as below
http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/Q_21968741.html
credit to doddwell, whoever that is ...
CREATE Procedure uspMATPhasedBrl
@StartDate smalldatetime
As
Select AssetCode,
sum(case when YrPeriod between dateAdd(month,-11,@StartDate) and @Startdate then Act else 0 end) as [P1],
sum(case when YrPeriod between dateAdd(month,-10,@StartDate) and dateadd(month,1,@Startdate) then Act else 0 end) as [P2],
sum(case when YrPeriod between dateAdd(month,-9,@StartDate) and dateadd(month,2,@Startdate) then Act else 0 end) as [P3],
sum(case when YrPeriod between dateAdd(month,-8,@StartDate) and dateadd(month,3,@Startdate) then Act else 0 end) as [P4],
sum(case when YrPeriod between dateAdd(month,-7,@StartDate) and dateadd(month,4,@Startdate) then Act else 0 end) as [P5],
sum(case when YrPeriod between dateAdd(month,-6,@StartDate) and dateadd(month,5,@Startdate) then Act else 0 end) as [P6],
sum(case when YrPeriod between dateAdd(month,-5,@StartDate) and dateadd(month,6,@Startdate) then Act else 0 end) as [P7],
sum(case when YrPeriod between dateAdd(month,-4,@StartDate) and dateadd(month,7,@Startdate) then Act else 0 end) as [P8],
sum(case when YrPeriod between dateAdd(month,-3,@StartDate) and dateadd(month,8,@Startdate) then Act else 0 end) as [P9],
sum(case when YrPeriod between dateAdd(month,-2,@StartDate) and dateadd(month,9,@Startdate) then Act else 0 end) as [P10],
sum(case when YrPeriod between dateAdd(month,-1,@StartDate) and dateadd(month,10,@Startdate) then Act else 0 end) as [P11],
sum(case when YrPeriod between @StartDate and dateadd(month,11,@Startdate) then Act else 0 end) as [P12]
from vwBrlYrPeriodMAT
Group By AssetCode
The above is based upon the vwBrlYrPeriodMAT. The view takes the Year and Period Fields and concatenates them into a date:
CREATE View vwBrlYrPeriodMAT
As
Select Top 100 Percent AssetCode, Sum(Act) As Act, Yr, Period,
Cast((Cast(Yr As Char(4)) + CASE WHEN LEN(Period) = 1 THEN '0' + Cast(Period as Char(1))
ELSE Cast(Period As Char(2)) END + '01') As smalldatetime) As YrPeriod
FROM tblbrl
Group By AssetCode, Yr, Period
Order BY AssetCode, Yr, Period Asc
--------------------------------------------------------------------------------------
[highlight]Recommended Articles on How to help us help you and[/highlight]
[highlight]solve commonly asked questions[/highlight]
Forum Etiquette: How to post data/code on a forum to get the best help by Jeff Moden[/url]
Managing Transaction Logs by Gail Shaw[/url]
How to post Performance problems by Gail Shaw[/url]
Help, my database is corrupt. Now what? by Gail Shaw[/url]
October 6, 2009 at 4:56 am
Thanks for your reply - afraid does not quite do - which is probaly my attempt at explaining -
the MAT periods are as below - starting from 200701 to latest month - so...
start point would be 200701 to 200712, then 200702 to 200801, 200703 to 200802
right up to latest month - 200811 to 200910, all aggregated to each MAT period
hopefully this makes what I am trying to do a little clearer:-)
Kind Regards
October 6, 2009 at 7:33 am
Don't know if this is the BEST way, but it's one of the ways:
declare @basemonth datetime
set @basemonth = '20070612'--set this to start at least one month BEFORE your start
;with n1 as (
select 1 as num UNION ALL
select 2 UNION ALL
select 3 UNION ALL
select 4 UNION ALL
select 5),
DynTally as
( Select ROW_NUMBER() over (order by ni.num) as N
from n1 ni
cross join n1 nj
cross join n1 nk
cross join n1 nl)
MATPeriods as (
select dateadd(month,N,@basemonth) start,
dateadd(month,N+12,@basemonth) ending
from DynTally
)
select MATPeriods.start, SUM(MyAmount)
from Mytable
inner join MATPeriods on mytable.MyDate>=MATPeriods.start and
mytable.MyDate<MATPeriods.Ending
group by MATPeriods.start
Depending on how many months you need - you might be able to shorten up the dynamic tally table.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply