|
|
|
Forum Member
 
Group: Forum Members
Last Login: 5/23/2008 12:35:46 PM
Posts: 29,
Visits: 63
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 6/25/2008 7:26:06 AM
Posts: 474,
Visits: 202
|
|
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
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 5/23/2008 12:35:46 PM
Posts: 29,
Visits: 63
|
|
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
|
|
|
|