SQL Server calculate number of working dates in a month-CodePudding

I have the sample data below

create table #Calendar (Dates  date)
insert into #Calendar values
('2021-11-01'),('2021-11-02'),('2021-11-03'),('2021-11-04'),('2021-11-05'),('2021-11-06'),
('2021-11-07'),('2021-11-08'),('2021-11-09'),('2021-11-10'),('2021-11-11'),('2021-11-12'),
('2021-11-13'),('2021-11-14'),('2021-11-15'),('2021-11-16'),('2021-11-17'),('2021-11-18'),
('2021-11-19'),('2021-11-20'),('2021-11-21'),('2021-11-22'),('2021-11-23'),('2021-11-24'),
('2021-11-25'),('2021-11-26'),('2021-11-27'),('2021-11-28'),('2021-11-29'),('2021-11-30')

What I want is to count the number of working day in a month. For the sample data given for Nov 2021 there are 22 working days if a date falls on Saturday or Sunday the Friday working number will be assigned to those days for example Mon 1 ,Tue 2, Wed 3, Thu 4, Fri 5, Sat 5, Sun 5 Mon 7 etc until I get to the last day of the month that will reset every month

I wrote the script below

select
      Dates
      ,DENSE_RANK() OVER (ORDER BY CASE WHEN datepart(DW,Dates)-1 IN (0,6) THEN 5 ELSE datepart(DW,Dates)-1 END) AS WorkingDays
      ,DATENAME(dw,dates) as DaysName
from #Calendar
order by Dates

The problem with the query is that each week reset to start from 1

Current Output

Expected output

Thanks

CodePudding user response：

Rather than a dense_rank, you can use a rolling conditional sum:

select
      Dates
      ,sum(case when datepart(DW,Dates)-1 IN (0,6) then 0 else 1 end) over (order by Dates) as WorkingDays
      ,datename(dw,Dates) as DaysName
from #Calendar
order by Dates

CodePudding user response：

DROP TABLE  IF EXISTS #Calendar
CREATE table #Calendar (Dates  date)
insert into #Calendar values
('2021-11-01'),('2021-11-02'),('2021-11-03'),('2021-11-04'),('2021-11-05'),('2021-11-06'),
('2021-11-07'),('2021-11-08'),('2021-11-09'),('2021-11-10'),('2021-11-11'),('2021-11-12'),
('2021-11-13'),('2021-11-14'),('2021-11-15'),('2021-11-16'),('2021-11-17'),('2021-11-18'),
('2021-11-19'),('2021-11-20'),('2021-11-21'),('2021-11-22'),('2021-11-23'),('2021-11-24'),
('2021-11-25'),('2021-11-26'),('2021-11-27'),('2021-11-28'),('2021-11-29'),('2021-11-30')


SELECT c.Dates,
       (CASE WHEN DATENAME (dw, c.Dates) IN ( 'Saturday' )
             THEN LAG (s.WorkingDays, 1, 1) OVER (ORDER BY c.Dates)
             WHEN DATENAME (dw, c.Dates) IN ( 'Sunday' )
             THEN LAG (s.WorkingDays, 2, 1) OVER (ORDER BY c.Dates)
             ELSE s.WorkingDays
        END
       ) AS WorkingDays,
       DATENAME (dw, c.Dates) AS DaysName
FROM   #Calendar AS c
LEFT JOIN
       (
           SELECT cc.Dates,
                  ROW_NUMBER () OVER (ORDER BY cc.Dates) AS WorkingDays
           FROM   #Calendar AS cc
           WHERE  DATENAME (dw, cc.Dates) NOT IN ( 'Saturday', 'Sunday' )
       ) AS s
    ON s.Dates = c.Dates][1]][1]

Output [1]: https://i.stack.imgur.com/Ysw9z.png