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.