SQL Server 주말과 공휴일을 제외한 날짜 차이를 얻는 방법은 무엇입니까?

당신은 그것을 시도 할 수 있습니다

샘플 데이터

DECLARE @SampleData as TABLE (Item_Code varchar(10), Sold int, Date datetime)
Insert into @SampleData VALUES
('BBPen100', 30,'2017-04-17 00:00:00.000'),
('BBPen100', 21,'2017-04-13 00:00:00.000'),
('BBPen100', 13,'2017-04-12 00:00:00.000'),
('XSHIRT80', 0 ,'2017-04-17 00:00:00.000'),
('XSHIRT80', 24,'2017-04-14 00:00:00.000'),
('XSHIRT80', 9 ,'2017-04-13 00:00:00.000'),
('XSHIRT80', 5 ,'2017-04-12 00:00:00.000'),
('YBSHADE7', 0 ,'2017-04-17 00:00:00.000'),
('YBSHADE7', 6 ,'2017-04-15 00:00:00.000'),
('YBSHADE7', 0 ,'2017-04-13 00:00:00.000'),
('YBSHADE7', 11,'2017-04-12 00:00:00.000')

DECLARE @TblHoliday AS TABLE
(
   Holiday_Date date
)

INSERT INTO @TblHoliday
VALUES
('2017-04-14 00:00:00.000'),
('2017-05-01 00:00:00.000'),
('2017-10-18 00:00:00.000'),
('2017-12-25 00:00:00.000')

DECLARE @CurrentDate datetime = '2017-04-18 00:00:00'

@CurrentDate 전에 @ 2PreviousWorkingDays를 계산할 수 있습니다.

-- 2 Previous Working Day with out Holiday
DECLARE @2PreviousWorkingDay date = CASE
                         WHEN datepart(dw,@CurrentDate) IN (2,3) THEN dateadd(day,-4, @CurrentDate)  -- 2 previous working day before monday
                         WHEN datepart(dw,@CurrentDate) IN (1) THEN dateadd(day,-3, @CurrentDate)   -- 2 previous working day before sunday
                         ELSE dateadd(day,-2, @CurrentDate) -- other day week
                       END
-- with holiday
SELECT @2PreviousWorkingDay = dateadd(day,0 - (SELECT count(1) FROM @TblHoliday th 
                      WHERE  th.Holiday_Date BETWEEN @2PreviousWorkingDay AND @CurrentDate
                      ANd datepart(dw,th.Holiday_Date) NOT IN (7,1) -- calculate only holiday that isn't weekend
                            )                            
                      , @2PreviousWorkingDay    

그리고 원하는 결과 :

;with temps AS
(
SELECT *, row_number() over(PARTITION BY sd.Item_Code ORDER BY sd.[Date] DESC) AS Rn
FROM @SampleData sd
WHERE sd.[Date] >= @2PreviousWorkingDay -- 2 working days
   AND NOT EXISTS (SELECT 1 FROM @TblHoliday th WHERE th.Holiday_Date = Cast(sd.[Date] AS date)) -- not holiday
   AND datepart(dw,sd.[Date]) NOT IN (7,1) -- not weekend
   AND sd.Sold <> 0 -- not zero sold
)
SELECT sd.Item_Code, ISNULL(t.Sold,0) AS Sold FROM
(
   SELECT DISTINCT sd.Item_Code FROM @SampleData sd
) sd
LEFT JOIN temps t ON t.Item_Code = sd.Item_Code AND t.Rn = 1

데모 링크 : Rextester