Archives of the TeradataForum

Message Posted: Thu, 05 Sep 2002 @ 16:02:36 GMT

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

Arun:

If you stop at the SUBQ1 level, you will get the bill count for each of the cycle dates...

select SUBQ1.LINE_ACT_DT, SUBQ1.CYCLE_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 ORDER BY 1

Regards,

Iyer Kasinath

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