Urgent

Database
Enthusiast

Urgent

Hi all,

I am having following sort of i/p.

City_Id From_dt To_dt Loc_cd
1 A B 0
1 B+1 C 0
1 C+1 D 1
1 D+1 E 1
1 E+1 F 0
1 F+1 G 0
2 A' B' 0
2 B'+1 C' 0
2 C'+1 D' 1
2 D'+1 E' 1
2 E'+1 F' 0
2 F'+1 G' 0

And O/P SHOULD BE :

City_Id From_dt To_dt Loc_cd
1 A C 0
1 C+1 E 1
1 E+1 G 0
2 A' C' 0
2 C'+1 E' 1
2 E'+1 F' 0

So is it possible using SQL only?

Regards,
Gander_ss
2 REPLIES
Senior Apprentice

Re: Urgent

There are lots of solutions, i'lll show just two of them.
You'll have to pick one appropriate for your problem.

Dieter

DROP 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_cd
FROM
(
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
) dt
GROUP BY city_id, loc_cd, r2-r1
ORDER BY 1,2;

SELECT
city_id
,MIN(from_dt)
,MAX(to_dt)
,loc_cd
FROM
(
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
) dt
GROUP BY city_id, loc_cd,grp
ORDER BY 1,2

Enthusiast

Re: Urgent

Thanks 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_ss