回答編集履歴

3

追記

2019/04/04 07:34

投稿

sazi
sazi

スコア25138

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

推敲

2019/04/04 07:34

投稿

sazi
sazi

スコア25138

test CHANGED
@@ -16,17 +16,17 @@
16
16
 
17
17
  SELECT
18
18
 
19
- SUM(case when to_char(CREATED_DTM, 'YYYYMM')= to_char(ADD_MONTHS(SYSDATE, -5), 'YYYYMM') then 1 else 0 end) as SUMMARY1
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')= to_char(ADD_MONTHS(SYSDATE, -4), 'YYYYMM') then 1 else 0 end) as SUMMARY2
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')= to_char(ADD_MONTHS(SYSDATE, -3), 'YYYYMM') then 1 else 0 end) as SUMMARY3
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')= to_char(ADD_MONTHS(SYSDATE, -2), 'YYYYMM') then 1 else 0 end) as SUMMARY4
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')= to_char(ADD_MONTHS(SYSDATE, -1), 'YYYYMM') then 1 else 0 end) as SUMMARY5
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')= to_char(SYSDATE, 'YYYYMM') then 1 else 0 end) as SUMMARY6
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

追記

2019/04/04 07:32

投稿

sazi
sazi

スコア25138

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