rolling 12months - 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

General SQL Server Forums

New to SQL Server Programming

rolling 12months

Author

Topic

FData
Starting Member

24 Posts

Posted - 2012-08-30 : 09:45:32

Hello guys

This is my first post, so please be gentle

I have created a query which provides me with rolling 12 months data
The table i used is
SELECT TOP 1000 [hst_merchnum]
,[hst_date_processed]
,[Net_Sales]
,[Net_Intg]
,[Scheme_Fees]
,[Funding_Amt]
FROM [FDMS].[dbo].[Fact_Financial_History]

and the coding i used to get the rolling 12 months is as follows

Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))
from FDMS.dbo.Fact_Financial_History)
SELECT ParentID
,sum([Net_Sales]) as Net_Sales_Rolling12,
SUM ([Net_Intg]) as Net_Intg_Rolling12,
SUM ([Scheme_Fees]) as Scheme_Fees_Rolling12,
SUM ([Funding_Amt]) as Funding_Amt_Rolling12

Into #R12
FROM [FDMS].[dbo].[Fact_Financial_History]f full outer join Dim_Outlet o
on f.hst_merchnum = o.FDMSAccountNo_First9
Where hst_date_processed > @date
group by o.ParentID

however i need the rolling 12months query above adapted so it can work out the following below

SELECT
FDMSAccountNo,
[Month_end_date] as hst_date_processed,
Case when Fee_Code IN ('42B','42C','42D','42E') Then 'PCI'
When Fee_Code = '00Y' Then 'Refund Transaction Charge'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'Y' then 'Other repeatable'
When salesMI_Group1 IN ('other', 'N/A') and d.repeat_msc = 'N' then 'Other Non repeatable'
Else salesMI_Group1 end as description,
d.repeat_msc,
Sum([Retail_amount]) As Amount
Into #MscCount
FROM FDMS.dbo.Fact_Fee_History f
INNER JOIN
FDMS.dbo.Dim_Fee_Codes D ON f.Fee_Sequence_Number = D.Fee_Code
where year (f.Month_end_date) > 2010
Group by FDMSAccountNo, Month_end_date, d.Fee_Code, d.SalesMI_Group1, d.Repeat_MSC

--drop table #MscCount

SELECT [FDMSAccountNo],
Case when year(hst_date_processed) = '2012' and description ='MSC' then SUM ([Amount]) else 0 end as [MSC_2012],
,
Case when year(hst_date_processed) = '2012' and description ='PCI' then SUM ([Amount]) else 0 end as [PCI_2012] ,
case when year(hst_date_processed) = '2012' and description ='Joining Fee' then SUM ([Amount]) else 0 end as [JoiningFee_2012],
case
case when year(hst_date_processed) = '2012' and description ='Refund Transaction Charge' then SUM ([Amount]) else 0 end as [RefundTransactionCharge_2012],
case when year(hst_date_processed) = '2012' and description ='Other repeatable' then SUM ([Amount]) else 0 end as [Other repeatable_2012],
case when year(hst_date_processed) = '2012' and description ='Other Non repeatable' then SUM ([Amount]) else 0 end as [Other Non repeatable_2012]

Into #Msc
FROM #MscCount
where year(hst_date_processed) > 2010
group by [FDMSAccountNo], hst_date_processed, description

select
SUM ([PCI_2012])as [PCI 2012],
SUM ([JoiningFee_2012])as [JoiningFee 2012],
SUM ([RefundTransactionCharge_2012])as [Refund Transaction 2012],
SUM ([Other repeatable_2012])as [Other repeatable 2012],
SUM ([Other Non repeatable_2012])as [Other Non repeatable2012],
into #msc1
from #Msc
group by [FDMSAccountNo]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2012-08-30 : 10:07:36

quote:
Originally posted by FData

Hello guys

This is my first post, so please be gentle

No, your first post was 08/23/2012

This is your third post

Too old to Rock'n'Roll too young to die.

Edit: I know - my answer isn't helpful but I could not resist

FData
Starting Member

24 Posts

Posted - 2012-08-30 : 10:11:17

Ha

Thank you for pointing that out :P
Okay this my third post, but still stuck and need help ;)
Am willing to pay someone to help me !

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:12:25

sorry your requirement is not fully clear. As per your definition you just need same @date based condition added to other queries as well. But I'm sure your actual scenario is something else. Could you explain with some sample data to illustrate your issue?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FData
Starting Member

24 Posts

Posted - 2012-08-30 : 10:21:21

Hi visakh16

basically i need to work out the last 12months PCI, MSC, joining fee, Refund Transaction Charge , Other repeatable
and Other Non repeatable

SO it be july 2012 data - august 2011 data which will then be summed. and grouped by the FDMSAccountNo. Each row will be unique as we dont have the same FDMSaccountno.

For example
the layout should be

FdmsaccountNo, PCI, MSC, joining fee, Refund Transaction Charge , Other repeatable, Other Non repeatable

001 £1, £3, £150, £400 , , 75 , £ 10
002 £3, £1, £10, £4800 , , 95 , £ 110

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:23:59

for that you just need to replace

where year(hst_date_processed) > 2010

with

where hst_date_processed >= dateadd(mm,datediff(mm,0,getdate())-11,0)
and hst_date_processed < dateadd(mm,datediff(mm,0,getdate())+1,0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FData
Starting Member

24 Posts

Posted - 2012-08-30 : 10:28:42

Hi visakh16

The problem i think i will have with that code ,
is that our data is always 1month behind

So the data which i currently have for 2012 is july.

so i need to get july 2012 - august 2011 , As thats my rolling 12,

Will your query above do that ?

I know for a fact that 'Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))

actually works, so is it possible if you could incorporate that ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:34:37

then change it like

where hst_date_processed >= dateadd(mm,datediff(mm,0,getdate())-12,0)
and hst_date_processed < dateadd(mm,datediff(mm,0,getdate()),0)

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:41:56

quote:
Originally posted by FData

Hi visakh16

The problem i think i will have with that code ,
is that our data is always 1month behind

So the data which i currently have for 2012 is july.

so i need to get july 2012 - august 2011 , As thats my rolling 12,

Will your query above do that ?

I know for a fact that 'Declare @date varchar(10)
set @Date = (select dateadd(MM,-12,max(hst_date_processed))

actually works, so is it possible if you could incorporate that ?

that will work so long as you've only data till 1 month back

if at later stage you get this months data or even future data like planning,forecast etc then result will get affected

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

FData
Starting Member

24 Posts

Posted - 2012-08-30 : 10:43:12

HI visakh16

Thank you for providing a solution,

but i still think max has to be in there somwehre,. I only say that, as august data wont be in my database untill the 2nd week of september. And if someone runs a report first week of septemeber , it will assume septemember data is in there and work 12months back from that .

Thats why i have put the max in there, as it looks at the maxiumn month we have and have 12months back from that.

Is it possible if you could try and change your solution to the one provided above ?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:48:11

quote:
Originally posted by FData

HI visakh16

Thank you for providing a solution,

but i still think max has to be in there somwehre,. I only say that, as august data wont be in my database untill the 2nd week of september. And if someone runs a report first week of septemeber , it will assume septemember data is in there and work 12months back from that .

Thats why i have put the max in there, as it looks at the maxiumn month we have and have 12months back from that.

Is it possible if you could try and change your solution to the one provided above ?

so is it always latest month data in table -12 months?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-08-30 : 10:51:10

then make it

Declare @date datetime
select @Date = dateadd(mm,datediff(mm,0,max(hst_date_processed))+1,0)
from yourtable

and put where condition like

where hst_date_processed >= dateadd(mm,-12,@date)
and hst_date_processed < @date

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources