Stored Procedure to automate reports for fiscal year 1

[metevil80]

I have been staring at this code for so long I don't even know what to do anymore. I need to automate several reports for the fiscal year which is 9/30-10/1 (example : 9/30/09-10/01/2010). This is what I have so far, any suggestions/guidance would be appreciated.

-- Insert statements for procedure here
DECLARE @tmpStart_fiscal_mo DATETIME,
@tmpStart_fiscal_yr DATETIME,
@tmpEnd_fiscal_mo DATETIME,
@tmpEnd_fiscal_yr DATETIME,
@NoOfDays DATETIME

SET
@tmpStart_fiscal_yr=DATEPART(YY, DATEADD(YY,-1,getdate())),
@tmpEnd_fiscal_yr='09/30/2009'
@Start_fiscal_mo =
CAST(CAST(@tmpStart_fiscal_mo AS VARCHAR(2))+'09' AS DATETIME)+'/'+CAST(YY(GETDATE()) AS VARCHAR(4))
@tmpEnd_fiscal_mo = CAST (CAST(@tmpEnd_fiscal_mo AS VARCHAR(2))+'/'+CAST(@NoOfDays AS VARCHAR(2))+'/'+ CAST(YY(GETDATE()) AS VARCHAR(4)) AS DATETIME)

SELECT DISTINCT
Bill_H.[CName], Bill_H.[Location], Bill_H.[Customer N], Bill_H.[Billing Date]
DATEDIFF(MM, @tmpStart_fiscal_mo, @tmpEnd_fiscal_mo) -12), (DATEDIFF(YY, @tmpStart_fiscal_yr, @tmpEnd_fiscal_yr) -1)
FROM Bill_H
WHERE (DATEPART(YY, dbo. Bill_H.[Billing Date]) = DATEPART(YY, @tmpStart_fiscal_yr)) AND (DATEPART(MM, dbo. Bill_H.[Billing Date]) = DATEPART(MM, @tmpEnd_fiscal_mo))



SELECT DISTINCT TOP 1000
Bill_H.[CName], Bill_H.[Location ID], Bill_H.[CNumber], Bill_H.[Billing Date],
DATEPART(MM, '09') AS Start_month,
DATEPART(MM, '10') AS End_month,
DATEADD(MM, -12, DATEADD(YY,-1, GETDATE()) AS Start_fiscal,
DATEADD(MM,0, DATEADD(YY,0, GETDATE()) AS End_fiscal


FROM Bill_H
WHERE dbo. Bill_H.[Billing Date] BETWEEN (Start_fiscal and End_fiscal) AND BETWEEN ((DATEPART(MM, dbo. Bill_H.[Billing Date]) = DATEPART(MM, '09') AS Start_month) AND(DATEPART(MM, dbo. Bill_H.[Billing Date]) = DATEPART(MM, '10') AS End_month))

 

[gmmastros]

You're making this a lot harder than it has too. This is often the case when people are dealing with dates, so don't feel too bad.

The simple truth is.... SQL Server is quite sophisticated when it comes to dealing with dates.

Declare @FiscalYear Int
Set @FiscalYear = 2009

Declare @FiscalYearEnd DateTime
Declare @FiscalYearStart DateTime

Select @FiscalYearStart = DateAdd(Year, @FiscalYear-1900,'19001001'),
       @FiscalYearEnd = DateAdd(Year, @FiscalYear-1900,'19011001')

SELECT DISTINCT                 
       Bill_H.[CName], 
       Bill_H.[Location], 
       Bill_H.[Customer N], 
       Bill_H.[Billing Date] 
FROM   Bill_H 
WHERE  Bill_H.[Billing Date] >= @FiscalYearStart
       AND Bill_H.[Billing Date] < @FiscalYearEnd
       
SELECT DISTINCT TOP 1000
       Bill_H.[CName], 
       Bill_H.[Location ID], 
       Bill_H.[CNumber], 
       Bill_H.[Billing Date]          
FROM   Bill_H
WHERE  Bill_H.[Billing Date] >= @FiscalYearStart
       AND Bill_H.[Billing Date] < @FiscalYearEnd

This code assumes that Fiscal Year 2009 starts on October 1, 2009 and end (at the end of the day) on Sept 30, 2010.

In the code above, the start date is set to October 1, and the end date is set to October 1 of the following year. This may sound strange until you notice the where clause conditions. We are filtering on billing date >= Start Date and Billing Date < End Date.

So, if you want to include data for September 30, 2010, but not include data for October 1, 2010, the code I show above will work perfectly for because it uses the LESS THAN operator for the end date comparison.

Make sense?

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[metevil80]

It seems to be my theme in life to make things more diffcult than need be. Thank you for your help, I do understand what you are saying, and once I get a chance I will try it out