May 12, 2015 at 10:05 am
Ok, I'm looking to get counts on historical data where the number of records exists on or after May 1 in any given year. I've got the total number of records for each year worked out, but now looking for the number of records exist after a specific date. Here's what I have so far.
Any input would be most appreciated
SELECT p.FY10,p.FY11,p.FY12,p.FY13,p.FY14,p.FY15
FROM
(
SELECT COUNT(recordID) AS S,
CASE DateFY
When 2010 then 'FY10'
When 2011 then 'FY11'
When 2012 then 'FY12'
When 2013 then 'FY13'
When 2014 then 'FY14'
When 2015 then 'FY15'
END as DateFY
FROM Sometable
WHERE DateFY between 2010 and 2015
and OccuranceDate >= ???? <- (May 1 of the given FY)
GROUP BY DateFY
) as z
PIVOT
(SUM(S)
FOR DateFY in (FY10,FY11,FY12,FY13,FY14,FY15)
)
as p
May 12, 2015 at 10:39 am
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
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
May 12, 2015 at 11:26 am
Thanks, that did the trick...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply