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
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]='星期六'然后给你
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
这篇关于在 SQL Server 中获取工作日的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持跟版网！