我想按照季度求和当输入201002时求 01,02的和
当输入201006时求 04,05,06的和
当输入201008时求 07,08的和
以此类推...代码类似如下,但是这个语句出错
大家帮帮我怎么写这个SQL
SELECT '200902' DATE_ID,
T_COPLAN.MANAGECOM_ID MANAGECOM_ID,
SUM(T_COPLAN."VALUE") VALUE
FROM T_COPLAN T_COPLAN
WHERE T_COPLAN.DEPT_ID = 1
AND T_COPLAN.SUBJECT_ID = 1
AND length(T_COPLAN.DATE_ID) = 6
AND length(T_COPLAN.MANAGECOM_ID) = 4
AND( case
when substr('200902',5,2) in ('01','02','03') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '01', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
when substr('200902',5,2) in ('04','05','06') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '04', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
when substr('200902',5,2) in ('07','08','09') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '07', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
when substr('200902',5,2) in ('10','11','12') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '10', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
end
)
group by T_COPLAN.MANAGECOM_ID
当输入201006时求 04,05,06的和
当输入201008时求 07,08的和
以此类推...代码类似如下,但是这个语句出错
大家帮帮我怎么写这个SQL
SELECT '200902' DATE_ID,
T_COPLAN.MANAGECOM_ID MANAGECOM_ID,
SUM(T_COPLAN."VALUE") VALUE
FROM T_COPLAN T_COPLAN
WHERE T_COPLAN.DEPT_ID = 1
AND T_COPLAN.SUBJECT_ID = 1
AND length(T_COPLAN.DATE_ID) = 6
AND length(T_COPLAN.MANAGECOM_ID) = 4
AND( case
when substr('200902',5,2) in ('01','02','03') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '01', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
when substr('200902',5,2) in ('04','05','06') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '04', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
when substr('200902',5,2) in ('07','08','09') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '07', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
when substr('200902',5,2) in ('10','11','12') then
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN
to_date(substr('200902', 1, 4) || '10', 'YYYYMM') AND
to_date('200902', 'YYYYMM')
end
)
group by T_COPLAN.MANAGECOM_ID
FROM T_COPLAN T_COPLAN
WHERE T_COPLAN.DEPT_ID = 1 AND
T_COPLAN.SUBJECT_ID = 1 AND
length(T_COPLAN.DATE_ID) = 6 AND
length(T_COPLAN.MANAGECOM_ID) = 4 AND
to_date(T_COPLAN.DATE_ID, 'yyyymm') BETWEEN trunc(to_date('200902', 'yyyymm'), 'Q') AND
last_day(to_date('200902', 'yyyymm'))
GROUP BY T_COPLAN.MANAGECOM_ID
T_COPLAN.MANAGECOM_ID MANAGECOM_ID,
SUM(T_COPLAN."VALUE") VALUE
FROM T_COPLAN T_COPLAN
WHERE T_COPLAN.DEPT_ID = 1
AND T_COPLAN.SUBJECT_ID = 1
AND length(T_COPLAN.DATE_ID) = 6
AND length(T_COPLAN.MANAGECOM_ID) = 4
and (T_COPLAN.DATE_ID>=to_char(trunc(to_date('200902','yyyymm'),'q'),'yyyymm')
and t_coplan.date_id<to_char(add_months(trunc(to_date('200902','yyyymm'),'q'),3),'yyyymm'))
group by T_COPLAN.MANAGECOM_ID
08月份 是第三季度的所以求07,08 这里09也是第三季度但是输入的时间截止08所以09不能加入
我上面的'200902'是模拟出入参数的值我先试试楼上两位的SQL语句
SELECT '200902' DATE_ID, T_COPLAN.MANAGECOM_ID MANAGECOM_ID, SUM(T_COPLAN."VALUE") VALUE
FROM T_COPLAN T_COPLAN
WHERE T_COPLAN.DEPT_ID = 1 AND
T_COPLAN.SUBJECT_ID = 1 AND
length(T_COPLAN.DATE_ID) = 6 AND
length(T_COPLAN.MANAGECOM_ID) = 4 AND
T_COPLAN.DATE_ID BETWEEN to_char(trunc(to_date('200902', 'yyyymm'), 'Q'), 'yyyymm') AND
'200902'
GROUP BY T_COPLAN.MANAGECOM_ID
那么上限改下
(T_COPLAN.DATE_ID>=to_char(trunc(to_date('200902','yyyymm'),'q'),'yyyymm')
and t_coplan.date_id<='200902')
date_id是yyyymm格式的6位字符串吧,这样就行了