CODEWITHSUNDEEP
sqlsql-server
Elior Sastiel
Elior Sastiel

Reputation: 1124

Count all the entity between periods - SQL Server

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.

Upvotes: 0

Views: 66

Answers (1)

Mustapha Larhrouch
Mustapha Larhrouch

Reputation: 3403

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

Upvotes: 1

Related Questions