sql - 带有美国假期的日期列表加上从上周六开始的每一天
问题描述
我想创建一个从今天前三年的 1 月 1 日到未来 2 年的 12 月 31 日的日期列表。在第二列中,我想标记圣诞节 (XMAS)、新年前夜 (NYE)、元旦 (NYD)、阵亡将士纪念日 (MEM)、7 月 4 日 (JUL4)、劳动节 (LABOR)。对于后三个假期,我不仅需要标注假期日期,还要标注上周六的每一天,以便标注整个长周末。否则用工作日名称标记日期。
SELECT STAYDATE,
CASE
WHEN TO_CHAR(STAYDATE,'MM')=12 AND TO_CHAR(STAYDATE,'DD')=25 THEN 'XMAS'
WHEN TO_CHAR(STAYDATE,'MM')=12 AND TO_CHAR(STAYDATE,'DD')=31 THEN 'NYE'
WHEN TO_CHAR(STAYDATE,'MM')=01 AND TO_CHAR(STAYDATE,'DD')=01 THEN 'NYD'
WHEN TO_CHAR(STAYDATE,'MM')=7 AND TO_CHAR(STAYDATE,'DD')=04 THEN 'JUL4'
ELSE
TO_CHAR(STAYDATE,'dy') END DAYLABEL
FROM (
SELECT TRUNC (add_months(sysdate,24) - ROWNUM) STAYDATE
FROM DUAL CONNECT BY ROWNUM < 1000)
解决方案
查询 1:
SELECT DT,
CASE
WHEN EXTRACT( MONTH FROM DT ) = 12
AND EXTRACT( DAY FROM DT ) = 25
THEN 'XMAS'
WHEN EXTRACT( MONTH FROM DT ) = 12
AND EXTRACT( DAY FROM DT ) = 31
THEN 'NYE'
WHEN EXTRACT( MONTH FROM DT ) = 1
AND EXTRACT( DAY FROM DT ) = 1
THEN 'NYD'
WHEN EXTRACT( MONTH FROM DT ) = 7
AND DT BETWEEN NEXT_DAY( TRUNC( DT, 'MM' ) - 4, 'SATURDAY' )
AND TRUNC( DT, 'MM' ) + 3
THEN 'JUL4'
WHEN EXTRACT( MONTH FROM DT ) = 9
AND DT BETWEEN NEXT_DAY( TRUNC( DT, 'MM' ) - 1, 'MONDAY' ) - 2
AND NEXT_DAY( TRUNC( DT, 'MM' ) - 1, 'MONDAY' )
THEN 'LABOR'
WHEN EXTRACT( MONTH FROM DT ) = 5
AND DT BETWEEN NEXT_DAY( TRUNC( LAST_DAY( DT ) ) - 7, 'MONDAY' ) - 2
AND NEXT_DAY( TRUNC( LAST_DAY( DT ) ) - 7, 'MONDAY' )
THEN 'MEM'
ELSE TO_CHAR( DT, 'DY' )
END AS day
FROM (
SELECT ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), -36 ) + LEVEL - 1 AS DT
FROM DUAL
CONNECT BY
ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), -36 ) + LEVEL - 1
< ADD_MONTHS( TRUNC( SYSDATE, 'YYYY' ), +24 )
)
结果:
| DT | DAY |
|----------------------|-------|
| 2015-01-01T00:00:00Z | NYD |
| 2015-01-02T00:00:00Z | FRI |
| 2015-01-03T00:00:00Z | SAT |
| 2015-01-04T00:00:00Z | SUN |
| 2015-01-05T00:00:00Z | MON |
| 2015-01-06T00:00:00Z | TUE |
| 2015-01-07T00:00:00Z | WED |
| 2015-01-08T00:00:00Z | THU |
| 2015-01-09T00:00:00Z | FRI |
| 2015-01-10T00:00:00Z | SAT |
| 2015-01-11T00:00:00Z | SUN |
| 2015-01-12T00:00:00Z | MON |
...
| 2015-05-21T00:00:00Z | THU |
| 2015-05-22T00:00:00Z | FRI |
| 2015-05-23T00:00:00Z | MEM |
| 2015-05-24T00:00:00Z | MEM |
| 2015-05-25T00:00:00Z | MEM |
| 2015-05-26T00:00:00Z | TUE |
| 2015-05-27T00:00:00Z | WED |
...
| 2015-07-02T00:00:00Z | THU |
| 2015-07-03T00:00:00Z | FRI |
| 2015-07-04T00:00:00Z | JUL4 |
| 2015-07-05T00:00:00Z | SUN |
| 2015-07-06T00:00:00Z | MON |
...
| 2015-09-03T00:00:00Z | THU |
| 2015-09-04T00:00:00Z | FRI |
| 2015-09-05T00:00:00Z | LABOR |
| 2015-09-06T00:00:00Z | LABOR |
| 2015-09-07T00:00:00Z | LABOR |
| 2015-09-08T00:00:00Z | TUE |
| 2015-09-09T00:00:00Z | WED |
...
| 2015-12-23T00:00:00Z | WED |
| 2015-12-24T00:00:00Z | THU |
| 2015-12-25T00:00:00Z | XMAS |
| 2015-12-26T00:00:00Z | SAT |
| 2015-12-27T00:00:00Z | SUN |
| 2015-12-28T00:00:00Z | MON |
| 2015-12-29T00:00:00Z | TUE |
| 2015-12-30T00:00:00Z | WED |
| 2015-12-31T00:00:00Z | NYE |
| 2016-01-01T00:00:00Z | NYD |
| 2016-01-02T00:00:00Z | SAT |
| 2016-01-03T00:00:00Z | SUN |
...
| 2016-05-26T00:00:00Z | THU |
| 2016-05-27T00:00:00Z | FRI |
| 2016-05-28T00:00:00Z | MEM |
| 2016-05-29T00:00:00Z | MEM |
| 2016-05-30T00:00:00Z | MEM |
| 2016-05-31T00:00:00Z | TUE |
| 2016-06-01T00:00:00Z | WED |
...
| 2016-06-30T00:00:00Z | THU |
| 2016-07-01T00:00:00Z | FRI |
| 2016-07-02T00:00:00Z | JUL4 |
| 2016-07-03T00:00:00Z | JUL4 |
| 2016-07-04T00:00:00Z | JUL4 |
| 2016-07-05T00:00:00Z | TUE |
| 2016-07-06T00:00:00Z | WED |
...
| 2016-09-01T00:00:00Z | THU |
| 2016-09-02T00:00:00Z | FRI |
| 2016-09-03T00:00:00Z | LABOR |
| 2016-09-04T00:00:00Z | LABOR |
| 2016-09-05T00:00:00Z | LABOR |
| 2016-09-06T00:00:00Z | TUE |
| 2016-09-07T00:00:00Z | WED |
...
| 2016-12-23T00:00:00Z | FRI |
| 2016-12-24T00:00:00Z | SAT |
| 2016-12-25T00:00:00Z | XMAS |
| 2016-12-26T00:00:00Z | MON |
| 2016-12-27T00:00:00Z | TUE |
| 2016-12-28T00:00:00Z | WED |
| 2016-12-29T00:00:00Z | THU |
| 2016-12-30T00:00:00Z | FRI |
| 2016-12-31T00:00:00Z | NYE |
| 2017-01-01T00:00:00Z | NYD |
| 2017-01-02T00:00:00Z | MON |
| 2017-01-03T00:00:00Z | TUE |
...
| 2017-05-25T00:00:00Z | THU |
| 2017-05-26T00:00:00Z | FRI |
| 2017-05-27T00:00:00Z | MEM |
| 2017-05-28T00:00:00Z | MEM |
| 2017-05-29T00:00:00Z | MEM |
| 2017-05-30T00:00:00Z | TUE |
| 2017-05-31T00:00:00Z | WED |
...
and so on...
...
| 2019-12-23T00:00:00Z | MON |
| 2019-12-24T00:00:00Z | TUE |
| 2019-12-25T00:00:00Z | XMAS |
| 2019-12-26T00:00:00Z | THU |
| 2019-12-27T00:00:00Z | FRI |
| 2019-12-28T00:00:00Z | SAT |
| 2019-12-29T00:00:00Z | SUN |
| 2019-12-30T00:00:00Z | MON |
| 2019-12-31T00:00:00Z | NYE |
推荐阅读
- javascript - 如何使用 Try and Catch 在异步函数中“成功”做某事?
- list - 如何在列表中获取映射值(键值对)
- git - 有没有办法从我以前的 git 提交中更改我的姓名和电子邮件历史记录?
- php - PHP 7 致命错误:未捕获的 TypeError:传递给 Some2::send() 的参数 1 必须是 Path\To 的实例,Path\To\Some 的实例
- python - pytest 在给定函数中使用字符串时出现断言错误
- css - 将第 3 个 div 居中在其他 2 个下方?
- java - 如何从 url Android Studio 下载我的位图到画廊
- java - 从 CompletableFuture 线程中的主线程访问 SessionScoped bean 不起作用
- ionic-framework - 应用程序最小化离子科尔多瓦插件在 iONIC5 应用程序中不起作用
- c++ - 在链表类中实现 removeDuplicates() 方法