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))
-- 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))