Facing problem in Cross tab query using multiple tables

Database

Facing problem in Cross tab query using multiple tables

Hi,

I am a bit new in teradata assistant , i am trying to create across tab query where in i am trying to show row wise Outgoing STD MOUS (usage )slabs vs Age on network of the customer (Month wise - M0,M1,M2) having value as Incoming MOUs. i am anable to get the desired result, suggest if any one could help me out here ----:)

PFB query for reference;-

SELECT

     x.CIRCLE,

 CASE WHEN x.STD_OG_MOU = 0 OR x.STD_OG_MOU IS NULL THEN 'A-0'

      WHEN x.STD_OG_MOU > 0 AND x.STD_OG_MOU <=10 THEN 'B 1- 10'

      WHEN x.STD_OG_MOU > 10 AND x.STD_OG_MOU <=20 THEN 'C 11- 20'

      WHEN x.STD_OG_MOU > 20 AND x.STD_OG_MOU <=30 THEN 'D 21- 30'

      WHEN x.STD_OG_MOU > 30 AND x.STD_OG_MOU <=50 THEN 'E 31- 50'

      WHEN x.STD_OG_MOU > 50 AND x.STD_OG_MOU <=70 THEN 'F 51- 70'

      WHEN x.STD_OG_MOU > 70 AND x.STD_OG_MOU <=100 THEN 'G 71- 100'

      WHEN x.STD_OG_MOU > 100 AND x.STD_OG_MOU <=200 THEN 'H 101- 200'

      WHEN x.STD_OG_MOU > 200 AND x.STD_OG_MOU <=300 THEN 'I 201- 300'

      WHEN x.STD_OG_MOU > 300 AND x.STD_OG_MOU <=500 THEN 'J 301- 500'

      WHEN x.STD_OG_MOU > 500  THEN 'K >500' END AS "STD_OG_Bucket",

 sum(CASE  WHEN y.AON > 0 AND y.AON <=30 THEN  y.STD_ic_MOU  end )as "M0"

from

(

SELECT

  VW_CIRCLE.CIRCLE_NAME as circle,

  VW_DIM_CUSTOMER_ACCOUNT.MSISDN as msisdn,

  (current_date - VW_DIM_CUSTOMER_ACCOUNT.C_ACTIVATION_DATE) as AON,

  sum(VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DURATION)/60 as STD_OG_MOU

FROM

  VW_CIRCLE,

  VW_DIM_CUSTOMER_ACCOUNT,

  VW_FACT_MSC_CDR_AGGR_DAILY,

  VW_DIM_CALL_DIRECTION,

  VW_DIM_CALL_TYPE

WHERE

  ( VW_FACT_MSC_CDR_AGGR_DAILY.CALL_TYPE_KEY=VW_DIM_CALL_TYPE.CALL_TYPE_KEY  )

  AND  ( VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY  )

  AND  ( VW_DIM_CUSTOMER_ACCOUNT.CUSTOMER_ACCOUNT_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CP_ACCOUNT_KEY  )

  AND  ( VW_CIRCLE.GEOGRAPHY_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CIRCLE_KEY  )

  AND 

  (

   VW_CIRCLE.CIRCLE_NAME  IN  ( 'HIMACHAL PRADESH'  )

   AND

   VW_DIM_CUSTOMER_ACCOUNT.C_STATUS  IN  ( 'Active'  )

   AND

   VW_FACT_MSC_CDR_AGGR_DAILY.TODAY_DATE  BETWEEN  {d '2015-03-01'}  AND  {d '2015-03-02'}

   AND

   COALESCE(VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID,'SMS_MO','SMMO','SMS_MT','SMMT','DIAMETER','GPRS',VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID)  =  'MOC'

  AND  (VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID  NOT IN  ('TRANSIT-O','TRANSIT-I','TRANS','TRANSIT','TRA','TRAN','-901','-902','0','50','1','2','3'))

   AND

   VW_DIM_CALL_TYPE.CALL_TYPE_DESC  =  'STD'

  )

GROUP BY

  1,

  2,

  3

)x

left join

(

SELECT

  VW_CIRCLE.CIRCLE_NAME as circle,

  VW_DIM_CUSTOMER_ACCOUNT.MSISDN as msisdn,

  (current_date - VW_DIM_CUSTOMER_ACCOUNT.C_ACTIVATION_DATE) as AON,

  sum(VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DURATION)/60 as STD_IC_MOU

FROM

  VW_CIRCLE,

  VW_DIM_CUSTOMER_ACCOUNT,

  VW_FACT_MSC_CDR_AGGR_DAILY,

  VW_DIM_CALL_DIRECTION,

  VW_DIM_CALL_TYPE

WHERE

  ( VW_FACT_MSC_CDR_AGGR_DAILY.CALL_TYPE_KEY=VW_DIM_CALL_TYPE.CALL_TYPE_KEY  )

  AND  ( VW_DIM_CALL_DIRECTION.CALL_DIRECTION_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CALL_DIRECTION_KEY  )

  AND  ( VW_DIM_CUSTOMER_ACCOUNT.CUSTOMER_ACCOUNT_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CP_ACCOUNT_KEY  )

  AND  ( VW_CIRCLE.GEOGRAPHY_KEY=VW_FACT_MSC_CDR_AGGR_DAILY.CIRCLE_KEY  )

  AND 

  (

   VW_CIRCLE.CIRCLE_NAME  IN  ( 'HIMACHAL PRADESH'  )

   AND

   VW_DIM_CUSTOMER_ACCOUNT.C_STATUS  IN  ( 'Active'  )

   AND

   VW_FACT_MSC_CDR_AGGR_DAILY.TODAY_DATE  BETWEEN  {d '2015-03-01'}  AND  {d '2015-03-02'}

   AND

   COALESCE(VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID,'SMS_MO','SMMO','SMS_MT','SMMT','DIAMETER','GPRS',VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID)  =  'MTC'

  AND  (VW_DIM_CALL_DIRECTION.CALL_DIRECTION_ID  NOT IN  ('TRANSIT-O','TRANSIT-I','TRANS','TRANSIT','TRA','TRAN','-901','-902','0','50','1','2','3'))

   AND

   VW_DIM_CALL_TYPE.CALL_TYPE_DESC  =  'STD'

  )

GROUP BY

  1,

  2,

  3

)y

on x.msisdn = y.msisdn

group by 1,2