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