SQL Server如何获取不包括周末和节假日的日期差？
技术问答
250 人阅读
|
0 人回复
|
2023-09-12
|
|
我有这张表:$ n2 d+ ]6 F1 h! S2 H
tbl_Sales
& H( M: C4 x. L; a----------------------------------------|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| ----------------------------------------如何在最近两个工作日获得最后一个非零售价值？这意味着我需要排除周末和假期。我有这张桌子，里面有假期。
/ L& ^' w) F! X$ C" U* o4 W0 mtbl_Holiday
. K+ o" a. W' J* {-------------------------|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日，输出如下：
$ E" ~4 S3 i- ]- B% u- l---------------------|Item_Code|Last_Sold||--------- ---------||BBPen100 |30 ||XSHIRT80 |9 ||YBSHADE7 | |---------------------我们的目标是在最近两个工作日内获得最终的销售价值，因此从2017年计数-04-17开始。输出分析:
8 E+ M3 @' ~, d& v9 aBBPen100-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目前，我有此查询：
7 w5 U: j }0 z7 p- x# ^SELECT Item_Code,Sold AS 'Last_Sold'FROM tbl_SalesWHERE 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()))但当然，这不能满足要求。
; N5 z6 H; {& O% |. g请帮我解决这个问题。, O$ C2 d) p! Q$ U7 k
重要说明:请考虑周末假期，如果我在周末或假期操作这个程序怎么办。
" b/ ~& s" }$ u I8 ~4 J( c先感谢您。' l R7 z3 r$ G! V$ d! m
7 v( d* G7 L7 m7 N
解决方案: |
|
|
|
|
|
|
|
|
|
