CREATE TABLE #t1 ( foo_type int , bar_type int , timestamp_ts datetime );
INSERT #t1 VALUES( 0 , 1000 , '1/12/00 00:00:00' );
INSERT #t1 VALUES( 0 , 1001 , '1/12/00 00:00:00' );
INSERT #t1 VALUES( 0 , 1002 , '1/12/00 00:00:00' );
INSERT #t1 VALUES( 1 , 1000 , '1/13/00 00:00:00' );
INSERT #t1 VALUES( 1 , 1001 , '1/13/00 00:00:00' );
INSERT #t1 VALUES( 1 , 1002 , '1/13/00 00:00:00' );
INSERT #t1 VALUES( 2 , 1000 , '1/14/00 00:00:00' );
INSERT #t1 VALUES( 2 , 1001 , '1/14/00 00:00:00' );
INSERT #t1 VALUES( 2 , 1002 , '1/14/00 00:00:00' );
INSERT #t1 VALUES( 3 , 1000 , '1/15/00 00:00:00' );
SELECT bar_type , foo_type , CONVERT( varchar , timestamp_ts , 101 ) AS timestamp_ts
FROM #t1
ORDER BY bar_type , foo_type DESC , timestamp_ts;
-- Sample Output
/*
bar_type foo_type timestamp_ts
----------- ----------- ------------------------------
1000 3 01/15/2000
1000 2 01/14/2000
1000 1 01/13/2000
1000 0 01/12/2000
1001 2 01/14/2000
1001 1 01/13/2000
1001 0 01/12/2000
1002 2 01/14/2000
1002 1 01/13/2000
1002 0 01/12/2000
*/
-- Desired Output:
-- Maximum foo_type for each bar_type & the timestamp values ( upto 4 )
SELECT t.bar_type ,
MAX( t.foo_type ) AS foo_type ,
MIN( CASE t.ts_cnt WHEN 1 THEN t.timestamp_ts END ) AS time1_ts ,
MIN( CASE t.ts_cnt WHEN 2 THEN t.timestamp_ts END ) AS time2_ts ,
MIN( CASE t.ts_cnt WHEN 3 THEN t.timestamp_ts END ) AS time3_ts ,
MIN( CASE t.ts_cnt WHEN 4 THEN t.timestamp_ts END ) AS time4_ts
FROM (
SELECT foo_type , bar_type , timestamp_ts ,
-- Get count of each timestamp value within a bar_type
-- This is used to generate the columns later
( SELECT COUNT( * ) FROM #t1 AS t2
WHERE t2.bar_type = t1.bar_type And
t2.timestamp_ts <= t1.timestamp_ts ) AS ts_cnt
FROM #t1 AS t1
) AS t
GROUP BY t.bar_type
ORDER BY t.bar_type;
/*
bar_type foo_type time1_ts time2_ts time3_ts time4_ts
-------- -------- ----------------------- ----------------------- ----------------------- -----------------------
1000 3 2000-01-12 00:00:00.000 2000-01-13 00:00:00.000 2000-01-14 00:00:00.000 2000-01-15 00:00:00.000
1001 2 2000-01-12 00:00:00.000 2000-01-13 00:00:00.000 2000-01-14 00:00:00.000 NULL
1002 2 2000-01-12 00:00:00.000 2000-01-13 00:00:00.000 2000-01-14 00:00:00.000 NULL
*/
This page was last updated on May 01, 2006 04:28 PM.