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

nfg76nw0 于 2021-07-27 发布在 Java

关注(0)|答案(1)|浏览(402)

我正试着想出一个工作日内有多少次送货被休了。注意：我不能定义函数。
我有一张table，上面有送货细节，比如：

+--------+---------------+---------------+
| 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        |
+--------+---------------+---------------+----------+

提前谢谢

sql Join sql-server count subquery

来源：https://stackoverflow.com/questions/63177749/count-of-work-days-between-two-date-columns

1条答案

按热度按时间

hgtggwj01#

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

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

赞(0）回复(0）举报 2021-07-27

我来回答

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

1条答案

相关问题

热门标签

最新问答