我有这张表：
tbl_Sales
---------------------------------------- |Item_Code|Sold|Date | |---------+----+-----------------------| |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| ----------------------------------------
如何获得最近2个工作日中的最后一个非零的售出价值？这意味着我需要排除周末和假期。我有这张桌子，里面有假期。
tbl_Holiday
------------------------- |Holiday_Date | |-----------------------| |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| -------------------------
举例来说，今天是2017年4月18日，输出应如下所示：
--------------------- |Item_Code|Last_Sold| |---------+---------| |BBPen100 |30 | |XSHIRT80 |9 | |YBSHADE7 |0 | ---------------------
我们的目标是在最近2个工作日内获得最后的售出价值，因此计数从2017-04-17开始。输出分析：
BBPen100-since it has value from last 1 working day (2017-04-17), that value will be retrieved. XSHIRT80-Zero value from last 1 working day (2017-04-17) -2017-04-16 & 2017-04-15 are weekends -2017-04-14 is holiday -So value from 2017-04-13 will be retrieved. YBSHADE7-Zero value from last 1 working day (2017-04-17) -2017-04-16 & 2017-04-15 are weekends -2017-04-14 is holiday -2017-04-13 has Zero value -2017-04-12 is beyond Last 2 working days -So value retrived should be Zero
当前，我有此查询：
SELECT Item_Code, Sold AS 'Last_Sold' FROM tbl_Sales WHERE CONVERT(date, [DATE]) = CASE WHEN CONVERT(date, [DATE]) = CONVERT(date, DATEADD(day, -1, GETDATE())) THEN CONVERT(date, DATEADD(day, -1, GETDATE())) WHEN CONVERT(date, [DATE]) <> CONVERT(date, DATEADD(day, -1, GETDATE())) THEN CONVERT(date, DATEADD(day, -2, GETDATE()))
但是，当然，这不能满足要求。
请帮我解决这个问题。
重要说明：请考虑周末的假期，以及如果我在周末或假期运行该程序该怎么办。
先感谢您。
你可以试试看
样本数据
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
