Archives of the TeradataForum

Message Posted: Tue, 05 Sep 2006 @ 14:48:22 GMT

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

Here is a sample SQL assistant query that can be adapted for integer dates:

     /*shortest interval coalesce/pack query*/

     DROP TABLE INTERVALS;

     CREATE TABLE Intervals
     (
     x INT NOT NULL,
     y INT NOT NULL,
     CHECK (y > x),
     PRIMARY KEY (x,y)
     );
     INSERT INTO Intervals (x,y) VALUES ( 1, 3); /*VALUES (start date, end
     date)*/
     INSERT INTO Intervals (x,y) VALUES ( 2, 5);
     INSERT INTO Intervals (x,y) VALUES ( 4,11);
     INSERT INTO Intervals (x,y) VALUES (10,12);
     INSERT INTO Intervals (x,y) VALUES (20,21);

     select * from intervals;

     SELECT MIN(I.x) AS cover_x, cover_y
       FROM (SELECT I.x AS x, MIN(Y.y) as cover_y
               FROM (SELECT I1.y AS y
                       FROM Intervals AS I1
                      WHERE NOT EXISTS(SELECT *
                                         FROM Intervals AS I2
                                        WHERE I2.y > I1.y
                                          AND I2.x <= I1.y
                                       )
                    ) AS Y
             INNER JOIN
             Intervals AS I
             ON I.y <= Y.y
             GROUP BY I.x
            ) AS I
     GROUP BY cover_y;

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