Sql server first business day of week case help

Question

0.00/5 (No votes)

See more:

Hello, I am working on finding the first business day of the week/month/quarter/year/fiscalmonth, etc. I am doing it with just week right now as I am having a bit of trouble.

SET first_business_day_week = CASE
	WHEN [day_full_name] = 'Monday' AND [day_business_day_flag] = '1' THEN 1
	WHEN [day_full_name] = 'Tuesday' AND [day_business_day_flag] = '1' THEN 1 ELSE 0
END

Works but gives me some Mondays AND Tuesdays as business days. If the first WHEN line is true then how can I have it ignore the second WHEN line?

I feel the answer may be simple and I am overcomplicating it.

*UPDATE*

day_full_name is varchar(9)
day_business_day_flag is bit that calculates if a day is non US Federal Holiday and is a weekday. Returns a 1 if non holiday non weekend.
first_business_day_week is varchar(10) should give me the date of the first business day of the week. Was just trying 1/0 for now because I can figure out the date part once I have the rest.

*UPDATE 2*

I used a previous line for first_day_of_month and this may work similar for first business day of week.

ORMAT (CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD, @CurrentDate) - 1), @CurrentDate))),'MM/dd/yyyy') AS First_Day_Of_Month,

I'd just need to figure that formula out and then how to also add the business day tracker to it to verify.

What I have tried:

adding Else 0 to first WHEN line -> Didn't work

Posted 11-Mar-19 5:00am

Trogers96

Updated 11-Mar-19 7:26am

v3

Add a Solution

Comments

Maciej Los 11-Mar-19 16:27pm

Can you share example data? Based on your description i can't figure it out how day_full_name is related with day_business_day_flag... and why you need to break case when instruction after finding a first business/working day.

Trogers96 12-Mar-19 7:57am

I realized I made this post before I drank coffee so bare with me LOL but In my table I have
[day_full_name] varchar(9), -- Contains name of the day, Sunday, Monday
[day_of_week] char(1),-- First Day Sunday=1 and Saturday=7
[day_business_day_flag] bit Null -- 1=BusinessDay, 0=NonBusinessDay
[day_bank_holiday_flag] bit, -- 1=Holiday, 0=Not Holiday
[day_weekday_flag] bit, -- 1=Weekday, 0=Weekend
The function I have to calculate if the day is a business day or not it must be 0 for day_bank_holiday_flag and 1 for day_weekday_flag.

/*Business Day Indicator*/
UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM]
SET day_business_day_flag = '1'
WHERE day_bank_holiday_flag = 0 AND day_weekday_flag = 1
UPDATE [EDW_MDM].[dbo].[FCFCU_CALENDAR_DIM]
SET day_business_day_flag = CASE WHEN day_business_day_flag is not null THEN 1 WHEN day_business_day_flag IS NULL THEN 0 END

1 solution

Add a Solution

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

#realJSOP · Accepted Answer · 2019-03-11T06:04:00

Solution 1

Try this code:

SQL

declare @firstBusinessDay int = 2; -- 2 = monday
declare @isFirstBusDay varchar(10) = case when DATEPART(dw, GetDate()) = @firstBusinessDay then 'yes' else 'no' end;
select @isFirstBusDay;

Posted 11-Mar-19 6:04am

#realJSOP

Updated 11-Mar-19 6:05am

v3

Sql server first business day of week case help

1 solution

Solution 1

Add your solution here

Preview 0

Sql server first business day of week case help

1 solution

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us