 |
 |
Archives of the TeradataForum
Message Posted: Fri, 05 Mar 2004 @ 21:57:30 GMT
| 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;
| |
