問題描述

我之前已經發布過這個問題，但我想我沒有像我應該的那樣清楚.我想計算兩個日期之間的差異，比如 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

這篇關于在 SQL Server 中獲取工作日的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！