Query

[amits]

I have a table having following records .
SUR_ID ID TYPE START_DATE END_DATE
---------- ---------- -- ---------- ----------
53240 11000358 CB 1 2
53241 11000358 CB 2 10
53242 11000358 CB 10 20
53243 11000358 WI 20 25
53244 11000358 WI 25 35
53245 11000358 WI 35 40
53246 11000358 WI 40 45
53247 11000358 WI 45 50
53248 11000358 CB 55 60
53249 11000358 CB 60 65
53250 11000358 CB 65 999999
My Requirement is to write a query to get the results like this i.e
id type startdate enddate
11000358 CB 1 20
11000358 WI 20 50
11000358 CB 60 999999.
Can any one help me.

[SANJAY_G]

If I understood right, this is what you want..

SELECT id, type, MIN(start_date), MAX(end_date)
FROM my_table
GROUP BY id, type;


Sanjay

[amits]

No this will give me.
11000358 CB 1 999999
11000358 WI 20 50 .
But what i want is different ,I want the result as:
11000358 CB 1 20
11000358 WI 20 50
11000358 CB 55 999999.

[grjohnson]

I'd try doing it in PL/SQL rather than raw SQL.

[amits]

I am not able to do in PL/SQL also.Actually i am not good in Pl/sql.Can you elaborate how to do it.

[SANJAY_G]

What is your criteria to associate start_date and end_date with id and type?

Sanjay

[amits]

Logic is like this :
From start date 1 to 20 continuosly and then from start date 20 to 50 the type is WI and from 55 to 999999 again CB is there .So the selection criterion for this particular case :
one record for CB type for this id should come with start date 1 and end date 20 and then one record for 20 to 50 one record for WI with start date 20 and end date 5o and again it changes to CB so one record from 55 to 999999 should come .
Tell me if you are having any doubt.