SQL Server 2008从DateDiff中排除公司假期 [英] Sql Server 2008 Exclude Company Holiday from DateDiff

问题描述

我有三个表，其中一个是主日历，其中包含公司假期的指标。我想从DateDiff计算中排除Holiday。以下是我所拥有的...

I have three tables, One of which is a master calendar that houses an indicator for company Holidays. I would like to exlude Holiday from my DateDiff calculation. Below is what I have...

提醒日期12/23/16
完成日期12/28/16
公司假期12/26 / 16需要从DateDIff计算中排除

alert date 12/23/16 complete date 12/28/16 company Holiday 12/26/16 Need to exlude from DateDIff calculation

-我的计算显示3个工作日，应排除12/26/16 = 2个工作日

--My calculation shows 3 business days it should exclude 12/26/16 = 2 business days

 DATEDIFF(DD,A.ALERTS_CREATE_DT,S.CreatedDate) 
           -(DATEDIFF(WK,A.ALERTS_CREATE_DT,S.CreatedDate) * 2)  --HOW MANY WEEKEND DAYS PASSED BY
           -(CASE WHEN DATENAME(DW,A.ALERTS_CREATE_DT) = 'Sunday' THEN 1 ELSE 0 END) 
           -(CASE WHEN DATENAME(DW,S.CreatedDate) = 'Saturday' THEN 1 ELSE 0 END) 
           -(CASE WHEN CAL.GRH_HOLIDAY_IND = 'Y' THEN 1 ELSE 0 END) 
           -(  SELECT COUNT(CAL.GRH_HOLIDAY_IND)
               FROM  A
               FULL OUTER JOIN S ON A.ID= S.ID
               INNER JOIN  CAL ON  A.ALERTCREATEDT_MMDDYYYY = CAL.CALENDAR_DATE
               WHERE 1=1
               AND Cal.GRH_HOLIDAY_IND = 'Y' 
               AND CAL.CALENDAR_DATE  between CAST(A.CREATE_DT AS DATE) AND CAST(S.CreatedDate AS DATE) ) AS BusinessDays

试图摆脱假期是我苦苦挣扎的地方，请参阅下文。

Trying to remove the Holidays is where I'm struggling, see below.

-(  SELECT COUNT(CAL.GRH_HOLIDAY_IND)
               FROM  A
               FULL OUTER JOIN S ON A.ID= S.ID
               INNER JOIN  CAL ON  A.ALERTCREATEDT_MMDDYYYY = CAL.CALENDAR_DATE
               WHERE 1=1
               AND Cal.GRH_HOLIDAY_IND = 'Y' 
               AND CAL.CALENDAR_DATE  between CAST(A.CREATE_DT AS DATE) AND CAST(S.CreatedDate AS DATE

推荐答案

在2个case语句之后，您可以添加以下语句以从假日日历表中减去天数。

After 2 case statements you can add below statement to subtract no of days from holiday Calendar table.

- (select ISNULL(count(1),0) from Cal where Cal.Holiday between ALERTS_CREATE_DT AND CreatedDate )

这篇关于SQL Server 2008从DateDiff中排除公司假期的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！