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

nfg76nw0  于 2021-07-27  发布在  Java
关注(0)|答案(1)|浏览(430)

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

  1. +--------+---------------+---------------+
  2. | Rec_Id | Date_Received | Date_Promised |
  3. +--------+---------------+---------------+
  4. | 1 | 2020-07-01 | 2020-07-07 |
  5. +--------+---------------+---------------+
  6. | 2 | 2020-07-15 | 2020-07-08 |
  7. +--------+---------------+---------------+

我有一个工作日表如下(t表示这是一个工作日):

  1. +---------------+----------+
  2. | CALENDAR_DATE | WORK_DAY |
  3. +---------------+----------+
  4. | 2020-07-01 | T |
  5. +---------------+----------+
  6. | 2020-07-02 | F |
  7. +---------------+----------+
  8. | 2020-07-03 | F |
  9. +---------------+----------+
  10. | 2020-07-04 | F |
  11. +---------------+----------+
  12. | 2020-07-05 | F |
  13. +---------------+----------+
  14. | 2020-07-06 | F |
  15. +---------------+----------+
  16. | 2020-07-07 | T |
  17. +---------------+----------+
  18. | 2020-07-08 | T |
  19. +---------------+----------+
  20. | 2020-07-09 | T |
  21. +---------------+----------+
  22. | 2020-07-10 | T |
  23. +---------------+----------+
  24. | 2020-07-11 | F |
  25. +---------------+----------+
  26. | 2020-07-12 | F |
  27. +---------------+----------+
  28. | 2020-07-13 | T |
  29. +---------------+----------+
  30. | 2020-07-14 | T |
  31. +---------------+----------+
  32. | 2020-07-15 | T |
  33. +---------------+----------+

结果如下:

  1. +--------+---------------+---------------+----------+
  2. | Rec_Id | Date_Received | Date_Promised | Days_Off |
  3. +--------+---------------+---------------+----------+
  4. | 1 | 2020-07-01 | 2020-07-07 | -1 |
  5. +--------+---------------+---------------+----------+
  6. | 2 | 2020-07-15 | 2020-07-08 | 5 |
  7. +--------+---------------+---------------+----------+

提前谢谢

hgtggwj0

hgtggwj01#

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

  1. select
  2. d.*,
  3. case when d.date_received > d.date_promised
  4. then (
  5. select count(*)
  6. from work_days w
  7. where
  8. w.work_day = 'T'
  9. and w.calendar_date >= d.date_promised
  10. and w.calendar_date < d.date_received
  11. )
  12. else (
  13. select - count(*)
  14. from work_days w
  15. where
  16. w.work_day = 'T'
  17. and w.calendar_date >= d.date_received
  18. and w.calendar_date < d.date_promised
  19. )
  20. end as days_off
  21. from delivery_details d

您可以在子查询中移动条件逻辑,以稍微缩短代码—尽管我怀疑这可能会降低效率:

  1. select
  2. d.*,
  3. (
  4. select case when date_received > date_promised then 1 else -1 end * count(*)
  5. from work_days w
  6. where
  7. w.work_day = 'T'
  8. and (
  9. (w.calendar_date >= d.date_promised and w.calendar_date < d.date_received)
  10. or (w.calendar_date >= d.date_received and w.calendar_date < d.date_promised)
  11. )
  12. ) as days_off
  13. from delivery_details d
展开查看全部

相关问题