Ross Turner
asked on
Fiscal/Financial Year Comparison SQL
Hi EE,
I’m trying to create a stored procedure that will get the total value for the Financial Year to Date by customer, but then I want to show the previous Financial Year to Date as well for comparison.
So take today (July 9th 2012) I want to get all the values from (May 1st 2012 to July 9th 2012) for Current Financial Year .
So last year would run from (May 1st 2011 to July 9th 2011) to make an accurate comparison.
Our Financial Years always run from (May 1st to the following April 30th)
Below is a mock up picture of what i'm trying to achieve.
i seem to be going round in circles....
Thank you for your Help....
Ross
I’m trying to create a stored procedure that will get the total value for the Financial Year to Date by customer, but then I want to show the previous Financial Year to Date as well for comparison.
So take today (July 9th 2012) I want to get all the values from (May 1st 2012 to July 9th 2012) for Current Financial Year .
So last year would run from (May 1st 2011 to July 9th 2011) to make an accurate comparison.
Our Financial Years always run from (May 1st to the following April 30th)
Below is a mock up picture of what i'm trying to achieve.
i seem to be going round in circles....
ECLARE @CurrentFinancialYear as Int
DECLARE @LastFinancialYear as Int
SET @CurrentFinancialYear ='2012'
SET @LastFinancialYear ='2011'
Select invoicedate As [FullDate],
customer.name As [CustomerName],
InvoiceHead.fiscalyear AS [FinancialYear],
InvoiceHead.fiscalperiod AS [FinancialYear],
DATENAME(MONTH,InvoiceHead.invoicedate) AS [FinancialMonthName],
(dbo.invcdtl.sellingshipqty * dbo.invcdtl.unitprice) AS [Value]
from dbo.invchead as InvoiceHead
INNER JOIN
dbo.invcdtl ON InvoiceHead.invoicenum = dbo.invcdtl.invoicenum
INNER JOIN
dbo.customer ON InvoiceHead.custnum = dbo.customer.custnum
where InvoiceHead.fiscalyear = @CurrentFinancialYear
order by fulldate
Thank you for your Help....
Ross
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ross - 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.
ASKER