在 SQL Server 中获取工作日

2021-09-10 00:00:00 sql tsql sql-server

我之前已经发布过这个问题，但我想我没有像我应该的那样清楚.我想计算两个日期之间的差异，比如 2018/01/01 和 2018/01/31.

I have posted this problem ago but I guess I wasn't able to be as clear as I should have. I want to calculate difference between 2 dates let's say 2018/01/01 and 2018/01/31.

我有一个表 Calendar ，它存储哪一天是假期，什么不是.我将周日和周六标记为假期，并想计算剩余的工作日应该是 23.

I have a table Calendar which is storing what day is a holiday and what is not. I am marking Sunday and Saturday as holidays, and want to calculate the working days remaining which should be 23.

但实际上问题是我也可以将星期日标记为假期而不是星期六.

But what actually the problem is that I can also mark Sunday as holiday and not Saturday.

这就是我也想使用这张表的原因.我看到很多解决方案给了我 23 个结果，因为他们将周日和周六标记为假期，这也很好，但我也希望它使用这张表.

That's why I want to use this table as well. I see so many solutions that are giving me 23 result because they are marking Sunday and Saturday as holiday which is fine too but I want it to be using this table as well.

我尝试了很多，但我确定我哪里出错了.也许现在有人可以提供帮助.

I tried much but I'm sure where I am going wrong. Maybe somebody can help now.

桌子是这样的

CREATE TABLE Calendar
(
    Day Varchar(25), --name of the day i.e Sunday, Saturday
    IsOffDay Binary  --1 for Yes and 0 for False
)

因此，如果我将周六和周日标记为假期，结果应该是 23 天，但如果我只将周日标记为假期并使周六不活动，那么结果应该是 27 天

So if I mark Saturday and Sunday as holiday the result should be 23 days but if I only mark Sunday as holiday and make Saturday inactive then it should be 27 days

一位伙伴说可能重复的提议解决方案是将周六和周日作为假期，但我不希望它被硬编码或您说什么.

The proposed solution that one mate says is possible duplicate is getting Saturday and Sunday as holiday but I don't want that to be hard coded or whatever you say.

推荐答案

试试这个查询.

你的桌子:

 CREATE TABLE #Calendar
    ([Day] Varchar(25), --name of the day i.e Sunday, Saturday
     IsOffDay BIT  --1 for Yes and 0 for False
     )

    INSERT INTO #Calendar ([Day],IsOffDay)
    SELECT 'Sunday',1  union
    SELECT 'Saturday',0

查询

DECLARE @STARTDATE  DATE='2018-01-01',@ENDDATE  DATE='2018-01-31'

;WITH CTE AS (
SELECT @STARTDATE  AS STARTDATE
UNION ALL
select DATEADD(D,1,STARTDATE) 
FROM CTE
WHERE STARTDATE <@ENDDATE
)
,WORKINGDAYS AS (
SELECT STARTDATE,DATENAME(DW,STARTDATE)WEEKDAYS,C1.Day AS isweekend
FROM CTE c
left JOIN #Calendar C1 ON DATENAME(DW,STARTDATE)=C1.Day AND C1.IsOffDay=1
)

SELECT COUNT(WEEKDAYS)as WORKINGDAYS FROM WORKINGDAYS WHERE isweekend is null

注意:上述查询为您提供 Working Days =27 .如果您希望星期六作为假期更新日历表 IsOffDay=1 where [Day]='星期六'然后给你工作日=23

Note: The above query gives you Working Days =27 .If you want saturday as holiday Update Calendar table IsOffDay=1 where [Day]='Saturday' then gives you Working Days =23

相关文章