0

Thank you in advance for your assistance. I've created a table to outline the percent of Reports in each county, for each day of the week within the date range, see below. I'd like to provide the average number of Reports for each day of the week as well, but am getting stuck on how to count the number of 'Monday's', for example between two dates, i.e. Between 2023-01-01 AND 2023-07-19 there are 29 Mondays. This should aid each county with understanding their work demands and setting appropriate staffing levels.

Thank again for your assistance!

SELECT
    c.CountyID,
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=1,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Sunday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=2,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Monday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=3,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Tuesday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=4,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Wednesday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=5,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Thursday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=6,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Friday],
    (SUM(IIF(DATEPART(DW, c.TimestampCreated)=7,1,0))) * 100.0 / COUNT(c.CountyID) AS [% Saturday]
FROM vCourt c 
        WHERE c.TimestampCreated BETWEEN '2023-01-01 00:00:00.000' AND '2023-07-19 23:59:59.999'
GROUP BY c.CountyID
Haus
  • 3
  • 2
  • please tag your database system and add data and wanted result – nbk Jul 24 '23 at 16:16
  • 1
    This has been asked many times before, one example here: [How to calculate the number of "Tuesdays" between two dates in TSQL?](https://stackoverflow.com/questions/7563069/how-to-calculate-the-number-of-tuesdays-between-two-dates-in-tsql) – Jonas Metzler Jul 24 '23 at 16:18

0 Answers0