时间戳

首页 > 解决方案 > 两个日期列之间的工作日数

sql - 两个日期列之间的工作日数

问题描述

我正在尝试提出一个交货期的工作日数。注意：我无法定义函数。

我有一张包含交货详细信息的表格，例如：

+--------+---------------+---------------+
| Rec_Id | Date_Received | Date_Promised |
+--------+---------------+---------------+
| 1      | 2020-07-01    | 2020-07-07    |
+--------+---------------+---------------+
| 2      | 2020-07-15    | 2020-07-08    |
+--------+---------------+---------------+

我有一个如下的工作日表（T 表示它是工作日）：

+---------------+----------+
| CALENDAR_DATE | WORK_DAY |
+---------------+----------+
| 2020-07-01    | T        |
+---------------+----------+
| 2020-07-02    | F        |
+---------------+----------+
| 2020-07-03    | F        |
+---------------+----------+
| 2020-07-04    | F        |
+---------------+----------+
| 2020-07-05    | F        |
+---------------+----------+
| 2020-07-06    | F        |
+---------------+----------+
| 2020-07-07    | T        |
+---------------+----------+
| 2020-07-08    | T        |
+---------------+----------+
| 2020-07-09    | T        |
+---------------+----------+
| 2020-07-10    | T        |
+---------------+----------+
| 2020-07-11    | F        |
+---------------+----------+
| 2020-07-12    | F        |
+---------------+----------+
| 2020-07-13    | T        |
+---------------+----------+
| 2020-07-14    | T        |
+---------------+----------+
| 2020-07-15    | T        |
+---------------+----------+

结果将如下所示：

+--------+---------------+---------------+----------+
| Rec_Id | Date_Received | Date_Promised | Days_Off |
+--------+---------------+---------------+----------+
| 1      | 2020-07-01    | 2020-07-07    | -1       |
+--------+---------------+---------------+----------+
| 2      | 2020-07-15    | 2020-07-08    | 5        |
+--------+---------------+---------------+----------+

提前致谢

标签： sqlsql-serverjoincountsubquery

解决方案

您可以使用横向连接、子查询和条件逻辑：

select 
    d.*,
    case when d.date_received > d.date_promised
        then (
            select count(*) 
            from work_days w 
            where 
                w.work_day = 'T' 
                and w.calendar_date >= d.date_promised 
                and w.calendar_date < d.date_received
        )
        else (
            select - count(*) 
            from work_days w 
            where 
                w.work_day = 'T' 
                and w.calendar_date >= d.date_received 
                and w.calendar_date < d.date_promised
        )
    end as days_off
from delivery_details d

您可以在子查询中移动条件逻辑以稍微缩短代码 - 尽管我怀疑它可能效率较低：

select 
    d.*,
    (
        select case when date_received > date_promised then 1 else -1 end * count(*) 
        from work_days w 
        where 
            w.work_day = 'T' 
            and (
                (w.calendar_date >= d.date_promised and w.calendar_date < d.date_received)
                or (w.calendar_date >= d.date_received and w.calendar_date < d.date_promised)
            )
    ) as days_off
from delivery_details d

推荐阅读