Archives of the TeradataForum

Message Posted: Thu, 05 Sep 2002 @ 16:00:06 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Arun:

Try this -

SELECT SUBQ2.LINE_ACT_DT, SUM(FEB_KOUNT), SUM(MAR_KOUNT), SUM(APR_KOUNT)
FROM
(select SUBQ1.LINE_ACT_DT,
           (case when SUBQ1.LINE_ACT_DT = 'Feb , 2002'
                      and SUBQ1.CYCLE_DT BETWEEN '2002-04-01' and '2002-06-30'
                      then    SUM(SUBQ1.THE_COUNT)
                      else 0
                      end ) AS FEB_KOUNT,

              (case when SUBQ1.LINE_ACT_DT = 'Mar , 2002'
                      and SUBQ1.CYCLE_DT BETWEEN '2002-05-01' and '2002-07-31'
                      then    SUM(SUBQ1.THE_COUNT)
                      else 0
                      end ) AS MAR_KOUNT,

              (case when SUBQ1.LINE_ACT_DT = 'Apr , 2002'
                      and SUBQ1.CYCLE_DT BETWEEN '2002-06-01' and '2002-08-30'
                      then    SUM(SUBQ1.THE_COUNT)
                      else 0
                      end ) AS APR_KOUNT
from
(select   CAST(( LINE_ACT_DT (format 'mmmb,byyyy')) AS CHAR(12)) AS LINE_ACT_DT, CYCLE_DT,
count(distinct(cust_id || cust_line_seq_id||extract (month from cycle_dt))) AS THE_COUNT
from   SDW_PRDUSR_ALLVM.VISION_P2K_ACTS_V
where  LINE_ACT_DT between '2002-02-01' and '2002-04-30'
and    CYCLE_DT BETWEEN '2002-04-01' and '2002-08-30'
group by CAST(( LINE_ACT_DT (format 'mmmb,byyyy')) AS CHAR(12)), CYCLE_DT) SUBQ1
GROUP BY SUBQ1.LINE_ACT_DT, SUBQ1.CYCLE_DT) SUBQ2
GROUP BY SUBQ2.LINE_ACT_DT ORDER BY 1

Regards,

Iyer Kasinath

	<Prev	Next>		<<First	<Prev	Next>	Last>>