July 10, 2015 at 8:40 am
Ok, I'm looking to get counts on historical data where the number of records exists between two dates with different years. The trick is the that the dates fall in different years. Ex: Give me the number of records that are dated between 0ct 1, 2013 and July 1, 2014.
A previous post of mine was similar where I needed to get records after a specific date. The solution provided for that one was the following. This let me get any records that occured after May 1 per given Fiscal year.
SELECT
MAX(CASE WHEN DateFY = 2010 THEN Yr_Count ELSE 0 END) AS [FY10],
MAX(CASE WHEN DateFY = 2010 THEN May_Count ELSE 0 END) AS [May+10],
MAX(CASE WHEN DateFY = 2011 THEN Yr_Count ELSE 0 END) AS [FY11],
MAX(CASE WHEN DateFY = 2011 THEN May_Count ELSE 0 END) AS [May+11],
MAX(CASE WHEN DateFY = 2012 THEN Yr_Count ELSE 0 END) AS [FY12],
MAX(CASE WHEN DateFY = 2012 THEN May_Count ELSE 0 END) AS [May+12],
MAX(CASE WHEN DateFY = 2013 THEN Yr_Count ELSE 0 END) AS [FY13],
MAX(CASE WHEN DateFY = 2013 THEN May_Count ELSE 0 END) AS [May+13],
MAX(CASE WHEN DateFY = 2014 THEN Yr_Count ELSE 0 END) AS [FY14],
MAX(CASE WHEN DateFY = 2014 THEN May_Count ELSE 0 END) AS [May+14],
MAX(CASE WHEN DateFY = 2015 THEN Yr_Count ELSE 0 END) AS [FY15],
MAX(CASE WHEN DateFY = 2015 THEN May_Count ELSE 0 END) AS [May+15]
FROM
(
SELECT
DateFY,
COUNT(recordID) AS Yr_Count,
SUM(CASE WHEN MONTH(OccuranceDate) >= 5 THEN 1 ELSE 0 END) AS May_Count
FROM Sometable
WHERE
DateFY BETWEEN 2010 AND 2015
GROUP BY DateFY
) AS derived
I basically need to have CASE WHEN MONTH(OccuranceDate) between Oct 1 (beginning year) and July 1 (ending year)
Any help would be appreciated.
July 10, 2015 at 9:38 am
Given that you have the DATE_FY field to determine your fiscal year, the question is, does your fiscal year ALWAYS match your calendar year? (I'm also assuming that both OccurrenceDate and DateFY are in the same table) If fiscal and calendar years are the same, try this kind of CASE statement construction:
CASE
WHEN CAST(OccurrenceDate AS date) BETWEEN
CAST(DateFY AS char(4)) + '1001' AND
CAST(DateFY + 1 AS char(4)) + '0630'
THEN somevalue
ELSE 0
END
If not, then we need to know what the fiscal year is first before coding a solution.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 9:54 am
The FY does not match up with Calendar year.
What I'm ultimately looking for are the number of records in a given FY where the RecordDate was from Oct 1 - July 1
The FY is a calculated field in our table that is generated by ([dbo].[UDF_FY]([RecordDate]))
CREATE FUNCTION [dbo].[UDF_FY] (@Date datetime ) RETURNS int
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Year int
DECLARE @Month int
SET @Month = MONTH(@Date);
SET @Year = YEAR(@Date);
IF @Month >= 10
SET @Year = @Year + 1
RETURN @Year
END
July 10, 2015 at 10:06 am
jon.wilson (7/10/2015)
The FY does not match up with Calendar year.What I'm ultimately looking for are the number of records in a given FY where the RecordDate was from Oct 1 - July 1
The FY is a calculated field in our table that is generated by ([dbo].[UDF_FY]([RecordDate]))
CREATE FUNCTION [dbo].[UDF_FY] (@Date datetime ) RETURNS int
WITH SCHEMABINDING
AS
BEGIN
DECLARE @Year int
DECLARE @Month int
SET @Month = MONTH(@Date);
SET @Year = YEAR(@Date);
IF @Month >= 10
SET @Year = @Year + 1
RETURN @Year
END
Okay, then this is even easier, because you're really looking to see the first 9 months of the fiscal year:
CASE WHEN MONTH(OccurrenceDate) BETWEEN 7 AND 9 THEN 0 ELSE somevalue END
If your fiscal year ever changes, this code will need to be updated.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
July 10, 2015 at 11:10 am
Thanks, that works for me.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply