Count for each day return wrong value

The below code gives a count of 1 for each day, and accumulates from today (13/04/2021) til the end of the month and sums them for Saturdays, Sundays and Week days.

;WITH mycte AS (
    SELECT  GETDATE() DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM mycte
    WHERE DateValue < EOMONTH(dateadd(day,-1, getdate()))
)

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 1 and 7 then 1 end) WeekCount
from mycte

For today (13/04/2021) I would expect the count to be Saturday = 2, Sunday = 2, and Weekdays to be 14 but instead I get 18 til the end of April – why is that?

Answer

I think it is because ‘between’ includes 1 and 7 again, below query should give you remaining 14 week days

;WITH mycte AS (
    SELECT  GETDATE() DateValue
    UNION ALL
    SELECT DateValue + 1
    FROM mycte
    WHERE DateValue < EOMONTH(dateadd(day,-1, getdate()))
)

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