Add a count of Business Days column when run. Counting Date in table to today

54 Views Asked by At
SELECT
    FullDate,
    DATEDIFF(dd, Fulldate, @Today) 
FROM
    (SELECT  
         [DateKey],
         [FullDate],
         [DayOfWeekName],
         [DayOfWeekAbbreviation],
         [IsWeekday],
         [IsUSCivilHoliday],
         CASE 
             WHEN IsWeekday = 'y' AND IsUSCivilHoliday = 'n' 
                 THEN 1
                 ELSE 0 
         END AS BusinessDay
     FROM 
         [ARCUSYM000].[dbo].[dimDate]
     WHERE
         FullDate > CONVERT(varchar(20), GETDATE() - 200, 110) 
         AND FullDate <= CONVERT(varchar(20), GETDATE(), 110)) x
GROUP BY
    FullDate

My problem is that I can't figure out a way to get only the business days.

I have a column BusinessDay that marks a business day or not. For each row in the data, I want to add how many business days till the current date. DateAdd like I have counts every day. Can anyone recommend a good way to do this?

Full Date | Business days till current date

Those are the two columns I need. Hope this makes sense

Example of what I need :


FullDate    BusinessDays
------------------------
2024-02-01      4
2024-02-02      3
2024-02-03      3
2024-02-04      3
2024-02-05      2
2024-02-06      1
2024-02-07      0
FullDate    DayOfWeekName   DayOfWeekAbbreviation   IsWeekday   IsUSCivilHoliday    BusinessDay
2024-01-29           MONDAY        MON                  Y         N                     1
2024-01-30           TUESDAY       TUE                  Y         N                     1
2024-01-31          WEDNESDAY       WED                 Y         N                     1
2024-02-01          THURSDAY        THU                 Y         N                     1
2024-02-02           FRIDAY         FRI                 Y         N                     1
2024-02-03          SATURDAY        SAT                 N         N                    0
2024-02-04           SUNDAY         SUN                 N         N                     0
2024-02-05           MONDAY         MON                 Y         N                     1
2024-02-06          TUESDAY         TUE                 Y         N                     1
2024-02-07         WEDNESDAY        WED                 Y         N                     1
0

There are 0 best solutions below