Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Fri, 05 Mar 2004 @ 21:57:30 GMT


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


Subj:   Re: Crosstab SQL
 
From:   Nakos, Greg

Below is sample code. First version for summing values and second to count distinct values. As you will see, I am using a reference table to drive the banding down the side and you can to the same for values along the top. Let me know if you need further explanation.


Regards,

Greg Nakos
Senior Consultant
Teradata Solutions Group

SELECT  LU_GENDER
,       COALESCE(BANDING_ORDER_ID, 99)
,       COALESCE(BANDING_DESC, 'Unknown') AS Age
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver < INTERVAL '0 00:01:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '<1 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 00:01:00' day to second
AND
INTERVAL '0 00:10:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '1-10 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 00:10:01' day to second
AND
INTERVAL '0 00:30:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '10-30 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 00:30:01' day to second
AND
INTERVAL '0 00:60:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '30-60 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 01:00:01' day to second
AND
INTERVAL '0 12:00:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '1-12 Hours')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 12:00:01' day to second
AND
INTERVAL '0 24:00:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '12-24 Hours')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver > INTERVAL '0 24:00:00' day to second
        AND MO_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '>24 Hours')
FROM    DEV_W.CARS_R_01
LEFT OUTER JOIN
        dev_w.CARS_BANDING
ON Age BETWEEN LOWER_LIMIT AND UPPER_LIMIT
AND BANDING_TYPE = 'Age'
GROUP BY 1,2,3
ORDER BY 1,2;



SELECT  LU_GENDER
,       COALESCE(BANDING_ORDER_ID, 99)
,       COALESCE(BANDING_DESC, 'Unknown') AS Age
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver < INTERVAL '0 00:01:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '<1 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 00:01:00' day to second
AND
INTERVAL '0 00:10:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '1-10 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 00:10:01' day to second
AND
INTERVAL '0 00:30:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '10-30 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 00:30:01' day to second
AND
INTERVAL '0 00:60:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '30-60 Min')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 01:00:01' day to second
AND
INTERVAL '0 12:00:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '1-12 Hours')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver BETWEEN INTERVAL '0 12:00:01' day to second
AND
INTERVAL '0 24:00:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '12-24 Hours')
,       COUNT(DISTINCT(CASE
        WHEN TimeToDeliver > INTERVAL '0 24:00:00' day to second
        AND MT_VOLUME <> 0
        THEN SUBSCRIBER_NUMBER
        ELSE NULL
                END)) (TITLE '>24 Hours')
FROM    DEV_W.CARS_R_01
LEFT OUTER JOIN
        dev_w.CARS_BANDING
ON Age BETWEEN LOWER_LIMIT AND UPPER_LIMIT
AND BANDING_TYPE = 'Age'
GROUP BY 1,2,3
ORDER BY 1,2;


     
  <Prev Next>   <<First <Prev Next> Last>>  
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 15 Jun 2023