InstantForum.NET v4.1.4Teradata Forumshttp://www.teradata.com/teradataforum/info@teradata.comWed, 03 Dec 2008 16:58:11 GMT20http://www.teradata.com/teradataforum/Topic11370-9-1.aspxThanks a lot for solution....But I want to know what is role of r2-r1 in group by.And I want to know more abt rank() function.so can u suggest any site or else.Thanks,gander_ssTue, 13 May 2008 04:39:30 GMTgander_sshttp://www.teradata.com/teradataforum/Topic11370-9-1.aspxThere are lots of solutions, i'lll show just two of them.You'll have to pick one appropriate for your problem.DieterDROP TABLE dropme;CREATE TABLE dropme( City_Id INT ,From_dt DATE ,To_dt DATE ,Loc_cd INT);INSERT INTO dropme VALUES(1, DATE '2008-01-01', DATE '2008-02-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-02-02', DATE '2008-03-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-03-02', DATE '2008-04-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-04-02', DATE '2008-04-01', 1);INSERT INTO dropme VALUES(1, DATE '2008-05-02', DATE '2008-05-01', 1);INSERT INTO dropme VALUES(1, DATE '2008-06-02', DATE '2008-06-01', 1);INSERT INTO dropme VALUES(1, DATE '2008-07-02', DATE '2008-07-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-08-02', DATE '2008-08-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-09-02', DATE '2008-09-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-10-02', DATE '2008-10-01', 0);INSERT INTO dropme VALUES(1, DATE '2008-11-02', DATE '2008-11-01', 1);INSERT INTO dropme SELECT city_id + 1, from_dt + 20, to_dt + 20, loc_cd FROM dropme;SELECT city_id ,MIN(from_dt) ,MAX(to_dt) ,loc_cdFROM ( SELECT City_Id ,From_dt ,To_dt ,Loc_cd ,RANK() OVER (PARTITION BY city_id ORDER BY from_dt) AS r1 ,RANK() OVER (PARTITION BY city_id, loc_cd ORDER BY from_dt) AS r2 FROM dropme ) dtGROUP BY city_id, loc_cd, r2-r1ORDER BY 1,2;SELECT city_id ,MIN(from_dt) ,MAX(to_dt) ,loc_cdFROM ( SELECT city_id ,from_dt ,to_dt ,loc_cd ,SUM(x) OVER (PARTITION BY city_id ORDER BY from_dt ROWS UNBOUNDED PRECEDING) AS grp FROM ( SELECT City_Id ,From_dt ,To_dt ,Loc_cd ,MIN(loc_cd) OVER (PARTITION BY city_id ORDER BY from_dt ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS prev ,CASE WHEN prev = loc_cd THEN 0 ELSE 1 END AS x FROM dropme ) dt ) dtGROUP BY city_id, loc_cd,grpORDER BY 1,2Thu, 08 May 2008 10:49:40 GMTdnoethhttp://www.teradata.com/teradataforum/Topic11370-9-1.aspxHi all,I am having following sort of i/p.City_Id From_dt To_dt Loc_cd1 A B 01 B+1 C 01 C+1 D 11 D+1 E 11 E+1 F 01 F+1 G 02 A' B' 02 B'+1 C' 02 C'+1 D' 12 D'+1 E' 12 E'+1 F' 02 F'+1 G' 0And O/P SHOULD BE :City_Id From_dt To_dt Loc_cd1 A C 01 C+1 E 11 E+1 G 02 A' C' 02 C'+1 E' 12 E'+1 F' 0So is it possible using SQL only?Regards,Gander_ssTue, 06 May 2008 01:15:26 GMTgander_ss