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:
cost.vStd_SalesInvoiceCostLine sicl LEFT OUTER JOIN
dim.FiscalPeriod fp ON
sicl.FiscalPeriodCode = fp.FiscalPeriodCode
ItemNumber COLLATE DATABASE_DEFAULT IN ('11041', '12081')