Count Business Days Between Start Date and Current Date

Question

I am trying to use a predefined INT value giving 1 or 0 as being a business day or not to count the rolling business days sequence. I have tried a lot of different code and checked out different posts here but none are specific enough to work with mine.

The predefined INT value is "business_day_flag_int". This comes from converting the calculations giving me "day_business_day_flag" which is a bit value. Currently Saturday/Sunday and Banking holidays trigger a 0 for "day_business_day_flag" and Mon-Fri being non holiday give it a 1 value.

How can I get this to work in an Update table that I can add to the rest of my table generation file? If more information is needed let me know.

I've tried a bunch of different alterations and variations of what I have here. If I remove date values it gives me an int value for all rows in the column of roughly 12600 business days. If I use with the date range it gives me

DECLARE @StartDate DATETIME = '01/01/2000' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2050' --End Value of Date Range
DECLARE
    @DayOfWeekInMonth INT,
    @DayOfWeekInYear INT,
    @DayOfQuarter INT,
    @WeekOfMonth INT,
    @CurrentYear INT,
    @CurrentMonth INT,
    @CurrentQuarter INT
DECLARE @CurrentDate AS DATETIME = @startDate
    SET @CurrentMonth = DATEPART(MM, @CurrentDate)
    SET @CurrentYear = DATEPART(YY, @CurrentDate)
    SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)


UPDATE [EDW_MDM].[dbo].[CALENDAR_DIM] SET 
    business_day_flag_int = Convert(INT, day_business_day_flag)
UPDATE [EDW_MDM].[dbo].[CALENDAR_DIM] SET 
    rolling_business_day_sequence = (SELECT count(business_day_flag_int) FROM [EDW_MDM].[dbo].[CALENDAR_DIM]
    WHERE business_day_flag_int = 1 and
    day_date between @StartDate and @CurrentDate)

I want the column "rolling_business_day_sequence" to count sequentially business days past. For example row 1 = 1, row 2 = 2, etc. Until the end of my calendar.

Update 1: Edited the line of code to

UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] SET 
    rolling_business_day_sequence = datediff(day,@StartDate,day_date) WHERE day_business_day_flag = 1

This gave me counting days and set rolling_business_day_sequence row values to null where not having day_busienss_day_flag = 1 and still counting them instead of not counting them. How can I make it not add the day?

Remember that holidays [which may be country-specific] must be considered. Of course, for your code, you have a flag that presumably is correct for them, but in general, that can be a difficult problem. — JosephDoggie, Apr 03 '19 at 15:48
Do you need the Time component, or do you just care about days? If you can, use `DATE` datatype, or `DATETIME2` if you need to track time, also. — Shawn, Apr 03 '19 at 16:17

Parfait · Accepted Answer · 2019-04-03T20:19:04.817

4

Consider a window function sum on your WHERE conditions to cumulatively count all instances of business_day_flag_int = 1 within specified date range. However, to use window functions in UPDATE, a CTE or subquery is required.

WITH CTE AS
( 
    SELECT ID, SUM(CASE WHEN day_date BETWEEN @StartDate AND @CurrentDate 
                             AND business_day_flag_int = 1
                        THEN 1
                         ELSE 0
                   END) OVER (ORDER BY day_date) AS running_sequence
    FROM [EDW_MDM].[dbo].[CALENDAR_DIM]
) 

UPDATE t
FROM [EDW_MDM].[dbo].[CALENDAR_DIM] t
JOIN CTE ON t.ID = CTE.ID
SET t.rolling_business_day_sequence = CTE.running_sequence

edited Apr 03 '19 at 20:19

answered Apr 03 '19 at 15:44

Parfait

104,375
17
94
125

Thank you, I ran into a problem using this with invalid syntax near ) after the "AND business_day_flag_int = 1". Couldn't figure it out so still trying more equations – Acurze Apr 03 '19 at 18:13
That sounds like you do not have [window functions](https://learn.microsoft.com/en-us/sql/t-sql/queries/select-over-clause-transact-sql?view=sql-server-2017) which is strange. First carefully check you are running exactly the expression: `SUM(...) OVER(...)`. What version of SQL Server are you running? – Parfait Apr 03 '19 at 18:19
A 2008 version according to my boss but connected to our DB it says SQL Server 12 – Acurze Apr 03 '19 at 18:25
The exact error message I get is "Msg 102, Level 15, State 1, Line 462 Incorrect syntax near ')'." when using ``` UPDATE [EDW_MDM].[dbo].[CALENDAR_DIM] SET rolling_business_day_sequence = SUM(day_date BETWEEN @StartDate AND @CurrentDate AND business_day_flag = 1) OVER (ORDER BY day_date) ``` – Acurze Apr 03 '19 at 18:31
I am reading that `ORDER BY` alone in `OVER()` for aggregate functions were added in [SQL Server 2012](https://stackoverflow.com/a/10040935/1422451). – Parfait Apr 03 '19 at 18:37
Yeah that could definitely be part of the problem. So with that being said, if I remove it and leave it as ``` rolling_business_day_sequence = SUM(day_date BETWEEN @StartDate AND @CurrentDate AND business_day_flag = 1 ) ``` I'm still left with a syntax error with the last ")". – Acurze Apr 03 '19 at 18:41
See update using a `CASE` statement, summing the true conditions which would be counts. Maybe re-try the `OVER`? – Parfait Apr 03 '19 at 18:47
I can get OVER to work in this scenario but isn't the solution, just another test to try to lead off of. ``` SET rolling_business_day_sequence = (SELECT(CASE WHEN day_business_day_flag = 1 THEN (LAG(rolling_business_day_sequence,1,0) OVER (order by day_date)) + 1 WHEN day_business_day_flag = 0 THEN (LAG(rolling_business_day_sequence,1,0) OVER (order by day_date)) - 1 END)) ``` – Acurze Apr 03 '19 at 19:32
But [`LAG`](https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-2017) is only available starting in 2012. You still see same syntax error with `OVER (ORDER BY ...))`? See edit with clause `rows unbounded preceding`. – Parfait Apr 03 '19 at 19:50
I tried your edited function and got Windowed functions can only appear in the SELECT or ORDER BY clauses. If I put in in a (SELECT( )) it doesn't work either. – Acurze Apr 03 '19 at 20:07
Tried as a INSERT INTO to see if it would work and still no luck ``` INSERT INTO [EDW_MDM].[dbo].[CALENDAR_DIM] SELECT ( SUM(CASE WHEN day_date BETWEEN @StartDate AND @CurrentDate AND business_day_flag_int = 1 THEN 1 ELSE 0 END) OVER (ORDER BY day_date ROWS UNBOUNDED PRECEDING)) AS rolling_business_day_sequence FROM [EDW_MDM].[dbo].[CALENDAR_DIM] ``` – Acurze Apr 03 '19 at 20:15
Instead use CTE. I just found out you cannot use window functions directly in `UPDATE` statements. See edit. I assume a primary key named ID is available in table. Adjust as needed. – Parfait Apr 03 '19 at 20:20
I run into to small errors when trying to run this. First this seems to ignore me previously declared @tempvariables "@StartDate" and "@CurrentDate" Then for UPDATE t the FROM is incorrect syntax saying it should expect a set. Not sure if my SQL is broken but this looks like it should work after researching this function in multiple different places. – Acurze Apr 05 '19 at 12:03
Very close to what you gave, ``` UPDATE T SET rolling_business_day_sequence = 1 + ( SELECT Count(1) FROM [EDW_MDM].[dbo].[CALENDAR_DIM] As L WHERE L.day_business_day_flag = 1 And L.day_date < T.day_date ) FROM [EDW_MDM].[dbo].[CALENDAR_DIM] As T WHERE T.day_business_day_flag = 1 ; ``` did the trick. Thank you! – Acurze Apr 05 '19 at 12:17
Very close? You do not use a window function but in fact use a correlated subquery. But glad you found a solution. Happy coding! – Parfait Apr 05 '19 at 12:27

Count Business Days Between Start Date and Current Date

1 Answers1