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 IntDECLARE @LastFinancialYear as IntSET @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.custnumwhere InvoiceHead.fiscalyear = @CurrentFinancialYear order by fulldate