Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

get last 12 months quantites only for months that have their fiscal period completed

Posted on 2014-04-11
5
Medium Priority
?
414 Views
Last Modified: 2015-09-22
I am writing a stored procedure that consists of Item Quantities for records that have completed their Fiscal Period in the last 12 months, and the last 24 months

The output should be like:

Item Number | Last 12 Months | Prior 12 Months


First I need to be able to figure out if the date that the report is run falls in a Fiscal Period that is Closed or Open, and only return the records for those that fall in the Closed Fiscal Period.

Let's say that i run the report today, and pass getdate() to the store procedure.

That is: 4/11/2014
That date is within the fiscal period 076, that uses Fiscal Start Date: '2014-03-30' , and Fiscal End Date: '2014-04-26'

The results should not include any records of April, because April's fiscal period has not yet closed.

The view that contains the quantities has the Fiscal Period Code in it.
There is a separate table that contains the Fiscal Start Date and Fiscal End Date (and Fiscal Period Code as well)

I think I need to do a left join to the Fiscal Period table and get the Start and End Dates and then check if today's date falls within those fiscal dates to include it or not.
Then I need to look in the last 12 months (last 12 months starting from the most recent close fiscal period) and the prior 12 months to those too.

I know it sounds like a puzzle, I am trying to explain what I learned so far and be clear, please understand that, and if I need to be more clear just tell me in which points.

I am including some sample data of the Item Quantities view for two items only. And data from the the Fiscal Period table.

I hope some one can offer me some help on this task.

Thank you very much.

my query so far:

	SELECT 
		sicl.ItemNumber
	,	SUM(sicl.InvoiceQuantity)
	,	sicl.FiscalYear
	,	sicl.FiscalPeriodCode
	,	fp.StartDate
	,	fp.EndDate
	FROM
		cost.vStd_SalesInvoiceCostLine sicl LEFT OUTER JOIN
		dim.FiscalPeriod fp ON
		sicl.FiscalPeriodCode = fp.FiscalPeriodCode
	WHERE
		ItemNumber COLLATE DATABASE_DEFAULT IN ('11041', '12081')
	GROUP BY 
		sicl.ItemNumber
	,	sicl.FiscalYear
	,	sicl.FiscalPeriodCode
	,	fp.StartDate
	,	fp.EndDate
	ORDER BY
		sicl.FiscalPeriodCode ASC
	,	sicl.ItemNumber

Open in new window

fiscalperiod-table.txt
itemquantities-view.txt
0
Comment
Question by:metropia
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 70

Accepted Solution

by:
Scott Pletcher earned 2000 total points
ID: 39994849
Maybe something like below.  I don't have data to test it, so haven't tested it yet (sorry, I really don't have time to convert text data to CREATE TABLEs and SQL INSERTs -- that would take me multiple times more time than the SQL query itself below!).



DECLARE @reporting_date datetime
SET @reporting_date = GETDATE()
----------------------------------------------------------------------------------------------------

DECLARE @reporting_fiscal_period_startdate datetime
DECLARE @reporting_fiscal_period_enddate datetime
SELECT
    @reporting_fiscal_period_startdate = fp.StartDate
,   @reporting_fiscal_period_enddate = fp.EndDate
FROM
      dim.FiscalPeriod fp
WHERE
    @reporting_date >= fp.StartDate AND
    @reporting_date < DATEADD(DAY, 1, fp.EndDate)


SELECT
      sicl.ItemNumber
,      SUM(CASE WHEN fp.StartDate >= DATEADD(YEAR, -1, DATEADD(DAY, -1, @reporting_fiscal_period_startdate))
             THEN sicl.InvoiceQuantity ELSE 0 END) AS [Last 12 Months]
,      SUM(CASE WHEN fp.StartDate >= DATEADD(YEAR, -2, DATEADD(DAY, -1, @reporting_fiscal_period_startdate))
              AND fp.StartDate < DATEADD(YEAR, -1, DATEADD(DAY, -1, @reporting_fiscal_period_startdate))
             THEN sicl.InvoiceQuantity ELSE 0 END) AS [Prior 12 Months]
,      sicl.FiscalYear
,      sicl.FiscalPeriodCode
,      fp.StartDate
,      fp.EndDate
FROM
      cost.vStd_SalesInvoiceCostLine sicl INNER JOIN
      dim.FiscalPeriod fp ON
      sicl.FiscalPeriodCode = fp.FiscalPeriodCode
WHERE
      ItemNumber COLLATE DATABASE_DEFAULT IN ('11041', '12081') AND
      fp.EndDate < @reporting_fiscal_period_startdate      AND
      --get only last two closed fiscal periods
      fp.StartDate >= DATEADD(YEAR, -2, DATEADD(DAY, -1, @reporting_fiscal_period_startdate))
GROUP BY
      sicl.ItemNumber
,      sicl.FiscalYear
,      sicl.FiscalPeriodCode
,      fp.StartDate
,      fp.EndDate
ORDER BY
      sicl.FiscalPeriodCode ASC
,      sicl.ItemNumber
0
 

Author Comment

by:metropia
ID: 39995331
would you be willing to explain to me what is happening in the case statements and also at the end on the where clause?

thank you much
0
 
LVL 70

Assisted Solution

by:Scott Pletcher
Scott Pletcher earned 2000 total points
ID: 39995343
The WHERE clause restricts the rows selected to only those for the past two years.

The CASE statements restrict each total to only the specific 12 month's data they should include.  A simple SUM() won't work, since each total should only be a specific 12 months out of the 24 months total data being read.
0
 

Author Closing Comment

by:metropia
ID: 39999591
excellent solution! it fitted my need very well.

thank you very much Scott :)
0
 
LVL 66

Expert Comment

by:Jim Horn
ID: 40989021
metropia - I just kicked out an article that deals specifically with Fiscal calendar planning --> SQL Server Calendar Table:  Fiscal Years.   Let me know if this helps you, and if yes please click on the 'Good Article' button and provide some feedback.  Thanks.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question