I want to count all the entities that created between August 1 until May 31 for each year. (academic year)
I am successful counting it monthly, but it’s not good enough:
SELECT CAST(MONTH(en.CreatedDate) AS VARCHAR(2)) + '-' + CAST(YEAR(en.CreatedDate) AS VARCHAR(4)), COUNT(*) NumberOfEvent FROM Entity en INNER JOIN Event e ON e.EntityId = en.EntityId GROUP BY CAST(MONTH(en.CreatedDate) AS VARCHAR(2)) + '-' + CAST(YEAR(en.CreatedDate) AS VARCHAR(4))
Can someone help me with this? Thanks.
Advertisement
Answer
you can try this :
select Case when MONTH(en.CreatedDate) < 6 Then CAST(YEAR(en.CreatedDate) - 1 AS VARCHAR(4)) + '-' + CAST(YEAR(en.CreatedDate) AS VARCHAR(4)) Else CAST(YEAR(en.CreatedDate) AS VARCHAR(4)) + '-' + CAST(YEAR(en.CreatedDate) + 1 AS VARCHAR(4)), count(*) NumberOfEvent From Entity en Inner Join Event e on e.EntityId = en.EntityId Group By Case when MONTH(en.CreatedDate) < 6 Then CAST(YEAR(en.CreatedDate) - 1 AS VARCHAR(4)) + '-' + CAST(YEAR(en.CreatedDate) AS VARCHAR(4)) Else CAST(YEAR(en.CreatedDate) AS VARCHAR(4)) + '-' + CAST(YEAR(en.CreatedDate) + 1 AS VARCHAR(4))
Explanation : when the month is small than 6 then it’s part of (year – 1) and year, else it’s part of year and (year + 1)
and to make sure that data of month 6 and 7 is not calculated in case there is wrong data in the database you can add this condition:
Where Month(en.CreatedDate) < 6 and Month(en.CreatedDate) > 7