Rolling 12 mo AND Prior Rolling 12 mo Query

Question

Rolling 12 mo AND Prior Rolling 12 mo Query

xtimesu

SSC Enthusiast

Points: 186
More actions
July 22, 2015 at 3:16 pm

#306009

Please help!
Need query to provide rolling 12 month and prior year rolling 12 month. Example: If this is July I would want Jun 14-July 15 and Jun 13-Jul 14
I want something so I don't have to make any modificatins each month.. I just have to run it.
I was trying something like this but know that I'm just not thinking about this right..
USE PRICE37
GO
SELECT BA.CustomerNbr, FiscalYear, FiscalPd
, CASE WHEN FiscalPd IN (1,2,3,4,5,6,7,8,9,10,11,12) THEN FiscalYear - 1 ELSE FiscalYear END AS [Calendar Year]
, [MonthName], ProductCd, SUM(RevenueUSD) AS RevenueSpend
FROM BusinessAssets AS BA
JOIN [dbo].[tbl_PeriodToMonthName] AS PMN
ON BA.FiscalPd = PMN.FiscalPeriod
WHERE CASE WHEN FiscalPd IN (1,2,3,4,5,6,7,8,9,10,11,12) THEN FiscalYear - 1 ELSE FiscalYear END > 2015
GROUP BY BA.CustomerNbr

Viewing 7 posts - 1 through 7 (of 7 total)

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

WayneS SSC Guru Points: 95461 More actions · Answer 1

xtimesu (7/22/2015)
Please help!
Need query to provide rolling 12 month and prior year rolling 12 month. Example: If this is July I would want Jun 14-July 15 and Jun 13-Jul 14
I want something so I don't have to make any modificatins each month.. I just have to run it.
I was trying something like this but know that I'm just not thinking about this right..
USE PRICE37
GO
SELECT BA.CustomerNbr, FiscalYear, FiscalPd
, CASE WHEN FiscalPd IN (1,2,3,4,5,6,7,8,9,10,11,12) THEN FiscalYear - 1 ELSE FiscalYear END AS [Calendar Year]
, [MonthName], ProductCd, SUM(RevenueUSD) AS RevenueSpend
FROM BusinessAssets AS BA
JOIN [dbo].[tbl_PeriodToMonthName] AS PMN
ON BA.FiscalPd = PMN.FiscalPeriod
WHERE CASE WHEN FiscalPd IN (1,2,3,4,5,6,7,8,9,10,11,12) THEN FiscalYear - 1 ELSE FiscalYear END > 2015
GROUP BY BA.CustomerNbr

Can you please post the CREATE TABLE statements for the tables, and then some INSERT statements to put some test data in these tables so that we can see what to work with? The test data needs to have data to show the situation. Then, based upon this test data, can you show us what the expected output would be?

Doing this will help us help you easier, faster, and more accurate.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

TheSQLGuru SSC Guru Points: 134017 More actions · Answer 2

This should be able to be done with WINDOWING functions since you are on SQL 2012. I bet I could also do it very quickly and efficiently using DATEADD and CASE to pivot the data too.

Like Wayne said - help us help you by providing sample data we can work with.

Best,
Kevin G. Boles
SQL Server Consultant
SQL MVP 2007-2012
TheSQLGuru on googles mail service

xtimesu SSC Enthusiast Points: 186 More actions · Answer 3

I'm new to this so please bare with me..

This is what the report looks like when I run it.. and then how I want it to look is below that

I have to run it right now for each year by itself.. which I don't want to have to do..

CustomerNbrFiscalYearFiscalPdCalendar YearProductCdRevenueSpend

NULL201412015ABC100

1000001201422015ABC200

1000002201432015ABC300

10000032014112015ELSE200

10000022014122015BO300

10000032014102015BO100

1000001201442015ELSE100

1000002201452015ABC200

1000001201462015ABC300

1000002201472015ABC100

1000001201482015ELSE200

1000002201492015ABC300

10000032014102015BO100

10000022014112015ELSE200

10000032014122015BO300

1000002201512015ABC100

1000001201522015ABC200

1000002201532015ABC300

1000003201542015ELSE100

1000002201552015ABC200

1000003201552015ABC200

1000002201562015ABC300

1000003201572015ABC100

1000002201582015ELSE200

1000003201592015ABC300

10000022015102015BO100

10000032015112015ELSE200

10000022015122015BO300

10000032015102015OI100

10000032015112015ELSE200

10000032015122015BO300

1000002201612015ABC100

1000003201612015ABC100

1000002201612015ABC100

How I want it to look

Fiscal YearFiscalPdRevenueSpend

20141100

20142200

20143300

20144100

20145200

20146300

20147100

20148200

20149300

201410200

201411400

201412600

Fiscal YearFiscalPdRevenueSpend

20151100

20152200

20153300

20154100

20155400

20156300

20157100

20158200

20159300

201510200

201511400

201512600

Fiscal YearFiscalPdRevenueSpend

20161300

sgmunson SSC Guru Points: 110651 More actions · Answer 4

xtimesu (7/22/2015)
Please help!
Need query to provide rolling 12 month and prior year rolling 12 month. Example: If this is July I would want Jun 14-July 15 and Jun 13-Jul 14
I want something so I don't have to make any modificatins each month.. I just have to run it.
I was trying something like this but know that I'm just not thinking about this right..
USE PRICE37
GO
SELECT BA.CustomerNbr, FiscalYear, FiscalPd
, CASE WHEN FiscalPd IN (1,2,3,4,5,6,7,8,9,10,11,12) THEN FiscalYear - 1 ELSE FiscalYear END AS [Calendar Year]
, [MonthName], ProductCd, SUM(RevenueUSD) AS RevenueSpend
FROM BusinessAssets AS BA
JOIN [dbo].[tbl_PeriodToMonthName] AS PMN
ON BA.FiscalPd = PMN.FiscalPeriod
WHERE CASE WHEN FiscalPd IN (1,2,3,4,5,6,7,8,9,10,11,12) THEN FiscalYear - 1 ELSE FiscalYear END > 2015
GROUP BY BA.CustomerNbr

A couple of things stand out here:

1.) Your examples are requesting 13 months of data, yet you're saying you want a rolling 12 months and the "previous" 12 months... If we assume that you actually want 13 monrhs, then we'll need to account for that, and

2.) You are using a field called FiscalPd, which I assume represents a Fiscal Period withn a Fiscal Year. A rolling year-long timeframe is only going to agree with your fiscal year for a 1 month timeframe in any given year, so you can NOT use that field as a basis for date selection, and

3.) You have a CASE statement listing what appears to be ALL POSSIBLE VALUES for the FiscalPD field, which will result in the condition ALWAYS being true.

You might be able to use the above to re-think your query...

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)

WayneS SSC Guru Points: 95461 More actions · Answer 5

xtimesu (7/23/2015)
I'm new to this so please bare with me..
This is what the report looks like when I run it.. and then how I want it to look is below that
I have to run it right now for each year by itself.. which I don't want to have to do..
CustomerNbrFiscalYearFiscalPdCalendar YearProductCdRevenueSpend
NULL201412015ABC100
1000001201422015ABC200
1000002201432015ABC300
10000032014112015ELSE200
10000022014122015BO300
10000032014102015BO100
1000001201442015ELSE100
1000002201452015ABC200
1000001201462015ABC300
1000002201472015ABC100
1000001201482015ELSE200
1000002201492015ABC300
10000032014102015BO100
10000022014112015ELSE200
10000032014122015BO300
1000002201512015ABC100
1000001201522015ABC200
1000002201532015ABC300
1000003201542015ELSE100
1000002201552015ABC200
1000003201552015ABC200
1000002201562015ABC300
1000003201572015ABC100
1000002201582015ELSE200
1000003201592015ABC300
10000022015102015BO100
10000032015112015ELSE200
10000022015122015BO300
10000032015102015OI100
10000032015112015ELSE200
10000032015122015BO300
1000002201612015ABC100
1000003201612015ABC100
1000002201612015ABC100
How I want it to look
Fiscal YearFiscalPdRevenueSpend
20141100
20142200
20143300
20144100
20145200
20146300
20147100
20148200
20149300
201410200
201411400
201412600
Fiscal YearFiscalPdRevenueSpend
20151100
20152200
20153300
20154100
20155400
20156300
20157100
20158200
20159300
201510200
201511400
201512600
Fiscal YearFiscalPdRevenueSpend
20161300

Well, you're off to a start. How about what I asked before:

CREATE TABLE #Temp (columns and data types);

INSERT INTO #Temp (columns) VALUES () from the above data for each row.

Do you really expect someone giving you free help to just do everything for you? We'd rather help someone willing to do this to help us out.

Please read the first link in my signature for more help in getting this data for us.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2

WayneS SSC Guru Points: 95461 More actions · Answer 6

TheSQLGuru (7/22/2015)
This should be able to be done with WINDOWING functions since you are on SQL 2012. I bet I could also do it very quickly and efficiently using DATEADD and CASE to pivot the data too.
Like Wayne said - help us help you by providing sample data we can work with.

Exactly what I'm thinking Kevin.

Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes

If you can't explain to another person how the code that you're copying from the internet works, then DON'T USE IT on a production system! After all, you will be the one supporting it!

Links:
For better assistance in answering your questions
Performance Problems
Common date/time routines
Understanding and Using APPLY Part 1 & Part 2