你可以试试看
样本数据
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 @TblHolidayVALUES('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 HolidayDECLARE @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 holidaySELECt @2PreviousWorkingDay = dateadd(day,0 - (SELECT count(1) FROM @TblHoliday th WHERe th.Holiday_Date BETWEEN @2PreviousWorkingDay AND @CurrentDateANd 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 RnFROM @SampleData sdWHERe 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) sdLEFT JOIN temps t ON t.Item_Code = sd.Item_Code AND t.Rn = 1
演示链接：Rextester
