Count of days for rest of month returning incorrect value for EOM date

user_5803584 0
Viewed 30

The below code when run for the last day of the month it is giving me a week day count of 1 when it should be 0 - how can I fix it?

Declare @EndDate DateTime = '03-31-2021'

;WITH mycte AS (
    SELECT  @EndDate + 1 DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM mycte
    WHERE DateValue < EOMONTH(@EndDate)
)

select
   count(case when datepart(dw, DateValue) = 1 then 1 end) SunCount
 , count(case when datepart(dw, DateValue) = 7 then 1 end) SatCount
 , count(case when datepart(dw, DateValue) between 2 and 6  then 1 end) WeekCount
from mycte
sql
1 Answers

Your initial CTE is actually creating a date of 2021-04-01 which is a week day, so that's where your count of 1 is coming from. If you want to restrict the counts to just the month in question, you could add a WHERE clause to your end query like this. This way, you get zeros for all counts.

Declare @EndDate DateTime = '03-31-2021'

;WITH mycte AS (
    SELECT  @EndDate + 1 DateValue
    UNION ALL
    SELECT DateValue +1
    FROM mycte
    WHERE DateValue < EOMONTH(@EndDate)
)

select 
   count(case when datepart(dw, DateValue) = 1 then 1 end) SunCount
 , count(case when datepart(dw, DateValue) = 7 then 1 end) SatCount
 , count(case when datepart(dw, DateValue) between 2 and 6  then 1 end) WeekDayCount
from mycte
where DATEPART(MM,DateValue)=DATEPART(MM,@EndDate)
user_108640930
Related