Sql: count bits as rolling sequentially

Question

0.00/5 (No votes)

See more:

I am trying to use my bit column "day_business_day_flag" as a way to count rolling business days as another value "rolling_business_day_sequence". day_business_day_flag is set to find if the day is a holiday, weekday/weekend and returns a 1 if it's non holiday and non weekend and 0 is if the day is a holiday or weekend. I am trying to set rolling_business_day_sequence to either add the previous row value for it as a sort of lag function or another way to calculate how many business days have past to the specific date.

[rolling_day_sequence] = datediff(day,@StartDate,day_date)+1,

I use this to count the normal rolling days sequence. This starts at one and everyday adds another number. I want to do the same with my day_business_day_flag column

day_business_day_flag is a bit value that returns a 1 if day_date is a non-weekend/non-holiday
rolling_business_day_sequence is the column I'm trying to populate.
@startdate is 01/01/2000
@enddate is 12/31/2050
day_date is the date of the row which can be anywhere between 2000-01-01 and 2050-12-31.
Let me know if more info is needed to get this working.
I get "Column name or number of supplied values does not match table definition." Most of the time.

What I have tried:

I tried doing

SELECT rolling_business_day_sequence = COUNT(day_business_day_flag) between @startdate and day_date --WHERE day_date >= @StartDate and day_date <= @EndDate AS rolling_business_day_sequence

I also tried

SELECT day_date, SUM(CASE WHEN day_business_day_flag = 1 then 1 else 0 END) AS rolling_business_day_sequence FROM CALENDAR_DIM group by day_date;

Another Try

SELECT day_date, day_business_day_flag, count(day_business_day_flag) FROM CALENDAR_DIM as rolling_business_day_sequence where day_business_day_flag = 1 group by day_date, day_business_day_flag

INSERT INTO CALENDAR_DIM
--SELECT SUM(CASE(day_business_day_flag) WHEN 1 THEN 1 ELSE 0 END) AS rolling_business_day_sequence FROM CALENDAR_DIM

INSERT INTO CALENDAR_DIM
SELECT COUNT(NULLIF(day_business_day_flag,0)) AS rolling_business_day_sequence FROM CALENDAR_DIM

New Try as of April 3rd, 2019

UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] SET 
	business_day_flag_int = (SELECT CAST(day_business_day_flag AS INT) FROM FCFCU_CALENDAR_DIM), 
	lag_rolling_business_day_sequence = (SELECT LAG(day_business_day_flag, 1) OVER (ORDER BY fcfcu_calendar_dim_id) FROM FCFCU_CALENDAR_DIM)
UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] SET 
	rolling_business_day_sequence = business_day_flag_int +lag_rolling_business_day_sequence

I'm getting the error "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression." When ran as it's own I see the day_date for 01/01/2000 repeated roughly 200 times before it goes to 01/02/2000. Any suggestions will be greatly appreciated because I feel I am close to what I am looking for.

Posted 1-Apr-19 7:56am

Trogers96

Updated 4-Apr-19 5:27am

v5

Add a Solution

2 solutions

Solution 1

Look at this article: Build a Calendar Without Pre-Existing Tables[^]

If you don't feel like using that, simply do this:

SQL

UPPER(DATENAME(WEEKDAY, yourDateField )) NOT IN ('SUNDAY','MONDAY')

to evaluate if it's a weekend day (you could also use day number if that's your bent).

Posted 1-Apr-19 9:37am

#realJSOP

Updated 1-Apr-19 9:38am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2019-04-04T05:27:00

Solution 2

If I've understood what you're trying to do, something like this should work:

SQL

UPDATE 
    T
SET
    rolling_business_day_sequence = 1 +
    (
        SELECT Count(1) 
        FROM [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] As L
        WHERE L.day_business_day_flag = 1
        And L.day_date < T.day_date
    )
FROM
    [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM] As T
WHERE
    T.day_business_day_flag = 1
;

Posted 4-Apr-19 5:27am

Richard Deeming

Updated 5-Apr-19 3:32am

v2

Comments

Maciej Los 4-Apr-19 12:51pm

5ed!

Trogers96 5-Apr-19 8:15am

Worked perfectly by putting the FROM below the SET statement. Marking at solution.

Richard Deeming 5-Apr-19 9:32am

Ah, good spot! I've corrected my answer.