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