Rolling 12Months in a Pvt Help

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 in a Pvt Help

Author

Topic

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-29 : 09:45:30

Hey guys

I am hoping you will be able to help me

i am not sure if this viable, i tried to find some solutions online, but not getting much luck.
What i am trying to achieve is create a pivot which brings in the financials for last 12months

For eg, if a run the query today (October) it would give me September 2012- October 2011 financials
If run the query in november2012-, it would provide me with October 2012 – November 2011

My query is

Declare @r12 datetime
set @r12 = dateadd(MM,-12,(select MAX(hst_date_processed)
from dbo.Fact_Financial_History))

SELECT
ParentID,
Parent_Name,
RM_Code
,[January]
,[February]
,[March]
,[April]
,[May]
,[June]
,[July]
,[August]
,[September]
,[October]
,[November]
,[December]
FROM (SELECT DISTINCT
o.ParentID,
o.Parent_Name,
o.RM_Code,

DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) AS Months,
--DATENAME(Month, hst_date_processed) Months
Sum (Net_Sales) AS 'Total'
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet o on hst_merchnum = FDMSAccountNo_First9
WHERE hst_date_processed >@r12
and RM_Account = 'y'
GROUP BY
o.ParentID,
o.Parent_Name,
o.RM_Code,
hst_date_processed
) AS p
PIVOT
(sum(Total) FOR Months IN ([January],[February],[March],[April],[May],[June],[July],[August],[September],[October],[November],[December]))AS pvt

Hoping that you can help

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 09:55:26

[code]
SELECT
ParentID,
Parent_Name,
RM_Code
,[1]
,[2]
,[3]
,[4]
,[5]
,[6]
,[7]
,

,[9]
,[10]
,[11]
,[12]
FROM (SELECT ROW_NUMBER() OVER (PARTITION BY o.ParentID,
o.Parent_Name,
o.RM_Code
ORDER BY DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0) DESC) AS Seq,
o.ParentID,
o.Parent_Name,
o.RM_Code,--DATENAME(Month, hst_date_processed) Months
Sum (Net_Sales) AS 'Total'
FROM [FDMS].[dbo].[Fact_Financial_History] inner join Dim_Outlet o on hst_merchnum = FDMSAccountNo_First9
WHERE hst_date_processed >@r12
and RM_Account = 'y'
GROUP BY
o.ParentID,
o.Parent_Name,
o.RM_Code,
DATEADD(DAY,DATEDIFF(DAY,0,hst_date_processed),0)
) AS p
PIVOT
(sum(Total) FOR Seq IN ([1],[2],[3],[4],[5],[6],[7],

,[9],[10],[11],[12]))AS pvt
[/code]

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

masond
Constraint Violating Yak Guru

447 Posts

Posted - 2012-10-29 : 10:03:44

Hi visakh16

Thank you for your response

is there anyway that i could reference the months instead of numbers for eg

1 = October 2011
2=November 2011
12 = September 2012

And these months would change based on my declaration (as seen below )

Declare @r12 datetime
set @r12 = dateadd(MM,-12,(select MAX(hst_date_processed)
from dbo.Fact_Financial_History))

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-10-29 : 10:13:18

quote:
Originally posted by masond

Hi visakh16

Thank you for your response

is there anyway that i could reference the months instead of numbers for eg

1 = October 2011
2=November 2011
12 = September 2012

And these months would change based on my declaration (as seen below )

Declare @r12 datetime
set @r12 = dateadd(MM,-12,(select MAX(hst_date_processed)
from dbo.Fact_Financial_History))

thats why you could put only numbers

you cant change the column names on the fly unless you use dynamic sql

like this

http://beyondrelational.com/modules/2/blogs/70/posts/10840/dynamic-pivot-in-sql-server-2005.aspx

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources