回答編集履歴
3
追記
test
CHANGED
@@ -11,6 +11,8 @@
|
|
11
11
|
```
|
12
12
|
|
13
13
|
基準の月より6か月分なら、こんな感じ。※sysdateを使用して現時点より6か月分
|
14
|
+
|
15
|
+
[ADD_MONTHS](https://www.shift-the-oracle.com/sql/functions/add_months.html)
|
14
16
|
|
15
17
|
```SQL
|
16
18
|
|
2
推敲
test
CHANGED
@@ -16,17 +16,17 @@
|
|
16
16
|
|
17
17
|
SELECT
|
18
18
|
|
19
|
-
SUM(case when to_char(CREATED_DTM, 'YYYYMM')=
|
19
|
+
SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1
|
20
20
|
|
21
|
-
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=
|
21
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -4), 'YYYYMM') then 1 else 0 end) as SUMMARY2
|
22
22
|
|
23
|
-
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=
|
23
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -3), 'YYYYMM') then 1 else 0 end) as SUMMARY3
|
24
24
|
|
25
|
-
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=
|
25
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -2), 'YYYYMM') then 1 else 0 end) as SUMMARY4
|
26
26
|
|
27
|
-
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=
|
27
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') then 1 else 0 end) as SUMMARY5
|
28
28
|
|
29
|
-
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=
|
29
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')=to_char(SYSDATE, 'YYYYMM') then 1 else 0 end) as SUMMARY6
|
30
30
|
|
31
31
|
from テーブル
|
32
32
|
|
1
追記
test
CHANGED
@@ -9,3 +9,27 @@
|
|
9
9
|
group by to_char(CREATED_DTM, 'YYYYMM')
|
10
10
|
|
11
11
|
```
|
12
|
+
|
13
|
+
基準の月より6か月分なら、こんな感じ。※sysdateを使用して現時点より6か月分
|
14
|
+
|
15
|
+
```SQL
|
16
|
+
|
17
|
+
SELECT
|
18
|
+
|
19
|
+
SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1
|
20
|
+
|
21
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -4), 'YYYYMM') then 1 else 0 end) as SUMMARY2
|
22
|
+
|
23
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -3), 'YYYYMM') then 1 else 0 end) as SUMMARY3
|
24
|
+
|
25
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -2), 'YYYYMM') then 1 else 0 end) as SUMMARY4
|
26
|
+
|
27
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') then 1 else 0 end) as SUMMARY5
|
28
|
+
|
29
|
+
, SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(SYSDATE, 'YYYYMM') then 1 else 0 end) as SUMMARY6
|
30
|
+
|
31
|
+
from テーブル
|
32
|
+
|
33
|
+
where to_char(CREATED_DTM, 'YYYYMM') >= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM')
|
34
|
+
|
35
|
+
```
|