回答編集履歴
2
sql修正
test
CHANGED
|
@@ -13,6 +13,8 @@
|
|
|
13
13
|
```SQL
|
|
14
14
|
|
|
15
15
|
WITH THIS_YEAR AS (
|
|
16
|
+
|
|
17
|
+
|
|
16
18
|
|
|
17
19
|
SELECT
|
|
18
20
|
|
|
@@ -34,45 +36,45 @@
|
|
|
34
36
|
|
|
35
37
|
WHERE
|
|
36
38
|
|
|
37
|
-
|
|
38
|
-
|
|
39
|
-
date BETWEEN TO_DATE(CONCAT(TO_CHAR(SYSDATE, 'YYYY'),'/03/01'))
|
|
40
|
-
|
|
41
|
-
|
|
39
|
+
date BETWEEN TO_DATE(CONCAT(TO_CHAR(SYSDATE, 'YYYY'),'/03/01')) AND TO_DATE(CONCAT(TO_CHAR(ADD_MONTHS(SYSDATE,12), 'YYYY'),'/03/01')) --今年3月～翌年3月
|
|
42
40
|
|
|
43
41
|
GROUP BY 休日区分
|
|
44
42
|
|
|
43
|
+
|
|
44
|
+
|
|
45
45
|
), NEXT_YEAR AS (
|
|
46
|
-
|
|
47
|
-
SELECT
|
|
48
|
-
|
|
49
|
-
MIN(CASE WHEN 休日区分 = '春休み' THEN date ELSE NULL END) AS SPRING_MIN_DATE --春休み開始日
|
|
50
|
-
|
|
51
|
-
,MAX(CASE WHEN 休日区分 = '春休み' THEN date ELSE NULL END) AS SPRING_MAX_DATE --春休み終了日
|
|
52
|
-
|
|
53
|
-
,MIN(CASE WHEN 休日区分 = '夏休み' THEN date ELSE NULL END) AS SUMMAR_MIN_DATE --夏休み開始日
|
|
54
|
-
|
|
55
|
-
,MAX(CASE WHEN 休日区分 = '夏休み' THEN date ELSE NULL END) AS SUMMAR_MAX_DATE --夏休み終了日
|
|
56
|
-
|
|
57
|
-
,MIN(CASE WHEN 休日区分 = '冬休み' THEN date ELSE NULL END) AS WINTER_MIN_DATE --冬休み開始日
|
|
58
|
-
|
|
59
|
-
,MAX(CASE WHEN 休日区分 = '冬休み' THEN date ELSE NULL END) AS WINTER_MAX_DATE --冬休み終了日
|
|
60
|
-
|
|
61
|
-
FROM
|
|
62
|
-
|
|
63
|
-
calender
|
|
64
|
-
|
|
65
|
-
WHERE
|
|
66
46
|
|
|
67
47
|
|
|
68
48
|
|
|
69
|
-
|
|
49
|
+
SELECT
|
|
70
50
|
|
|
51
|
+
MIN(CASE WHEN 休日区分 = '春休み' THEN date ELSE NULL END) AS SPRING_MIN_DATE --春休み開始日
|
|
52
|
+
|
|
53
|
+
,MAX(CASE WHEN 休日区分 = '春休み' THEN date ELSE NULL END) AS SPRING_MAX_DATE --春休み終了日
|
|
54
|
+
|
|
55
|
+
,MIN(CASE WHEN 休日区分 = '夏休み' THEN date ELSE NULL END) AS SUMMAR_MIN_DATE --夏休み開始日
|
|
56
|
+
|
|
57
|
+
,MAX(CASE WHEN 休日区分 = '夏休み' THEN date ELSE NULL END) AS SUMMAR_MAX_DATE --夏休み終了日
|
|
58
|
+
|
|
59
|
+
,MIN(CASE WHEN 休日区分 = '冬休み' THEN date ELSE NULL END) AS WINTER_MIN_DATE --冬休み開始日
|
|
60
|
+
|
|
61
|
+
,MAX(CASE WHEN 休日区分 = '冬休み' THEN date ELSE NULL END) AS WINTER_MAX_DATE --冬休み終了日
|
|
62
|
+
|
|
63
|
+
FROM
|
|
64
|
+
|
|
65
|
+
calender
|
|
66
|
+
|
|
67
|
+
WHERE
|
|
68
|
+
|
|
71
|
-
|
|
69
|
+
date BETWEEN TO_DATE(CONCAT(TO_CHAR(ADD_MONTHS(SYSDATE,12), 'YYYY'),'/03/01')) AND TO_DATE(CONCAT(TO_CHAR(ADD_MONTHS(SYSDATE,24), 'YYYY'),'/03/01')) --翌年3月～翌々年3月
|
|
72
70
|
|
|
73
71
|
GROUP BY 休日区分
|
|
74
72
|
|
|
73
|
+
|
|
74
|
+
|
|
75
75
|
)
|
|
76
|
+
|
|
77
|
+
|
|
76
78
|
|
|
77
79
|
SELECT * FROM THIS_YEAR
|
|
78
80
|
|
1
sql修正
test
CHANGED
|
@@ -40,7 +40,7 @@
|
|
|
40
40
|
|
|
41
41
|
AND TO_DATE(CONCAT(TO_CHAR(ADD_MONTHS(SYSDATE,12), 'YYYY'),'/03/01')) --今年3月～翌年3月
|
|
42
42
|
|
|
43
|
-
|
|
43
|
+
GROUP BY 休日区分
|
|
44
44
|
|
|
45
45
|
), NEXT_YEAR AS (
|
|
46
46
|
|
|
@@ -70,7 +70,7 @@
|
|
|
70
70
|
|
|
71
71
|
AND TO_DATE(CONCAT(TO_CHAR(ADD_MONTHS(SYSDATE,24), 'YYYY'),'/03/01')) --翌年3月～翌々年3月
|
|
72
72
|
|
|
73
|
-
|
|
73
|
+
GROUP BY 休日区分
|
|
74
74
|
|
|
75
75
|
)
|
|
76
76
|
|