teratail
回答率
85.30%
teratail header banner
teratail header banner
質問するログイン新規登録
トップC#に関する質問指定した日付のn年分、ｎか月ぶんのデータを取得したい

編集履歴

回答編集履歴

3

変更

2017/04/27 00:59

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -14,8 +14,7 @@
14
14
 
15
15
  select sum_year, sum_date, user_cnt from t_hoge
16
16
  where sum_year between to_char('2014-04-01'::date,'YYYY')::numeric and to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric
17
- and not (sum_year = to_char('2014-04-01'::date,'YYYY')::numeric and sum_monthly > 1 and sum_monthly < substr('2014-04-01',6,2)::numeric)
17
+ and not (sum_year = to_char('2014-04-01'::date,'YYYY')::numeric and sum_monthly < substr('2014-04-01',6,2)::numeric)
18
- and not (sum_year = to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric
19
- and sum_monthly < 12 and sum_monthly >= to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric)
18
+ and not (sum_year = to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric and sum_monthly >= to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric)
20
- and sum_day = substr('2014-04-01',9,2)::numeric;
19
+ and sum_day = substr('2014-04-01',9,2)::numeric;
21
20
  ```

2

変更

2017/04/27 00:59

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -14,8 +14,8 @@
14
14
 
15
15
  select sum_year, sum_date, user_cnt from t_hoge
16
16
  where sum_year between to_char('2014-04-01'::date,'YYYY')::numeric and to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric
17
+ and not (sum_year = to_char('2014-04-01'::date,'YYYY')::numeric and sum_monthly > 1 and sum_monthly < substr('2014-04-01',6,2)::numeric)
17
- and sum_monthly between substr('2014-04-01',6,2)::numeric and 12
18
+ and not (sum_year = to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric
18
- and sum_monthly >= 1 and sum_monthly < to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric
19
+ and sum_monthly < 12 and sum_monthly >= to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric)
19
20
  and sum_day = substr('2014-04-01',9,2)::numeric;
20
-
21
21
  ```

1

変更

2017/04/27 00:54

投稿

A.Ichi
A.Ichi

スコア4070

answer CHANGED
@@ -15,7 +15,7 @@
15
15
  select sum_year, sum_date, user_cnt from t_hoge
16
16
  where sum_year between to_char('2014-04-01'::date,'YYYY')::numeric and to_char((date '2014-04-01' +interval '3 month'),'YYYY')::numeric
17
17
  and sum_monthly between substr('2014-04-01',6,2)::numeric and 12
18
- and sum_monthly > 1 and sum_monthly < to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric
18
+ and sum_monthly >= 1 and sum_monthly < to_char((date '2014-04-01' +interval '3 month'),'mm')::numeric
19
19
  and sum_day = substr('2014-04-01',9,2)::numeric;
20
20
 
21
21
  ```