sql - DateDiff By Day of Week Count Does Not Match Actual Count from Paper Calendar

Question

I have a query that groups by day of the week basis:

SELECT DATENAME(DW,rct.rpt_date) [Day of The Week]
      ,DATEDIFF(day,CAST(@date_from as DATE),CAST(@date_to AS DATE) [Number]
      ,COUNT(DISTINCT rct.rpt_date) as [Occurrence / Active]
FROM receipt_summary rct
WHERE rct.rpt_date between CAST(@date_from as DATE) and CAST(@date_to AS DATE)
AND rct.bus_id = 10
GROUP BY DATENAME(DW,rct.rpt_date)
ORDER BY CASE DATENAME(DW,rct.rpt_date)
WHEN 'SUNDAY' THEN 1
WHEN 'MONDAY' THEN 2
WHEN 'TUESDAY' THEN 3
WHEN 'WEDNESDAY' THEN 4
WHEN 'THURSDAY' THEN 5
WHEN 'FRIDAY' THEN 6
WHEN 'SATURDAY' THEN 7 END

When I used DATEDIFF(wk,@dateFrom,@dateTo) as one the columns, the count in Number column is same across the seven days. It doesn't match the number I aimed to get when count comparing with using a paper calendar.

For example, between 1 December 2019 and 12 March 2020, I would expect to see 15 times of Sundays to Thursdays, while Fridays and Saturdays should show 14 times. The Inactive column is naturally a subtraction of Number - Occurrences. Per the problem above, Sunday - Thursday shows 14.

This screenshot is what I am hoping to achieve:

Can anyone help on this? Where exactly did I miss out on?

Note: For grouping by Day of the Month, I am aware that it is similar in structure except for replacing DATENAME(DW,date) with DATENAME(D,rct.rpt_date) + CASE WHEN DAY IN (1,21,31) Then ...etc, but how different will it be the handling in the DATEDIFF portion?

UPDATE The updated screenshot shows the current and expected results:

This is a calendar from December 2019 to March 2020. Drawn in boxes are number of Sundays, Mondays and so forth from (date from) to 12 March 2020 (date to).

标签： sqlsql-serversql-server-2008