Sql server首周工作日的案例帮助 [英] Sql server first business day of week case help

问题描述

您好，我正在努力寻找一周/月/季度/年/财政月等的第一个工作日，等等。我现在就这样做，因为我遇到了一些麻烦。

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

工作但给了我一些星期一和星期二作为工作日。如果第一个WHEN行是真的那么我怎么能忽略第二个WHEN行呢？



我觉得答案可能很简单而且我过于复杂了。 />


*更新*



day_full_name是varchar（9）

day_business_day_flag是位计算一天是非美国联邦假日并且是工作日。如果非假期非周末，则返回1。

first_business_day_week是varchar（10）应该给我一周的第一个工作日的日期。现在只是尝试1/0，因为我可以在休息后找出日期部分。



*更新2 *



我使用了前一行first_day_of_month，这可能与一周的第一个工作日类似。

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,

我只需要知道该公式，然后如何添加工作日跟踪器进行验证。



我尝试过：



将Else 0添加到第一个WHEN行 - >没工作

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

推荐答案

试试这段代码：

Try this code:

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

这篇关于Sql server首周工作日的案例帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！