Return the working day number in each calendar month-CodePudding

enter image description here

I am trying to return the working day numbers in each calendar month. For example, in the month of November, there are 22 working days (1,2,3 -----22). Saturday and Sunday will be seen as the preceding Friday (for e.g November 1,2,3,4,5,5,5,6,7,8,9,10,10,10,11-----22). The logic I wrote below didn't work. Please can anyone solve this for me? Thank you

  SELECT
              Date 
            , datepart(DW,DATE) AS DayName
            , DATENAME(DW, DATE) ax
            , datepart(DW,DATE)-1 AS DayName1
            , CASE WHEN datepart(DW,DATE)-1 IN (0,6) THEN 5 ELSE datepart(DW,DATE)-1 END bx
        FROM [STAGING_4_6_DIM_CALENDAR_V2] 
        ORDER BY 1 ASC

CodePudding user response：

I suppose you can use a running sum:

WITH cte AS (
    SELECT Date
         , CASE WHEN DATENAME(WEEKDAY, Date) IN ('SATURDAY', 'SUNDAY') THEN 0 ELSE 1 END AS wd
    FROM t
)
SELECT Date, SUM(wd) OVER (PARTITION BY YEAR(Date), MONTH(Date) ORDER BY Date)
FROM cte

For November 2021 it will return:

1,2,3,4,5,5,5,6,7,8,9,10,10,10,11,12,13,14,15,15,15,16,17,18,19,20,20,20,21,22

CodePudding user response：

Post Withdrawn as only worked for months starting on Mondays.