a question about creating a group in oracle SQL

[jamesflowers1974]

hi
I have a dataset based upon spells in hospitals and the wards stayed on during that stay
spell_id , id , ward , spell_start , spell end , ward_start , ward end
1200 , 1 , red ,01/02/2015 , 31/04/2015 ,01/02/2015 , 21/02/2015
1200 , 2 , red ,01/02/2015 , 31/04/2015 ,22/02/2015 , 27/02/2015
1200 , 3 , red ,01/02/2015 , 31/04/2015 ,27/02/2015 , 28/02/2015
1200 , 4 , blue ,01/02/2015 , 31/04/2015 ,01/03/2015 , 15/03/2015
1200 , 5 , red ,01/02/2015 , 31/04/2015 ,15/03/2015 , 31/03/2015
1200 , 4 , green,01/02/2015 , 31/04/2015 ,01/04/2015 , 31/04/2015

as you can see there are differing ward start dates within the same spell for the same ward.
I need to extract the ward start and wad end for each consecutive spell.

spell_id , ward , spell_start , spell end , ward_start , ward end
1200 , red ,01/02/2015 , 31/04/2015 ,01/02/2015 , 28/02/2015
1200 , blue ,01/02/2015 , 31/04/2015 ,01/03/2015 , 15/03/2015
1200 , red ,01/02/2015 , 31/04/2015 ,15/03/2015 , 31/03/2015
1200 , green,01/02/2015 , 31/04/2015 ,01/04/2015 , 31/04/2015

a simple maximum or min grouping wont work as this will read the 4th line of the red ward as the max and only return one line whereas I need to see both.

many thanks

James

 

[johnherman]

Not sure what to say here. You're displaying data that looks very text-like, like a csv file. Is this data loaded in tables? For the question at hand, if the data is stored as you indicate, then I recommend a cursor solution in PL/SQL. Order the cursor as you have with the demo: spell_id , id , ward , spell_start , spell end , ward_start , ward end. Fetch rows and compare the spell_id, ward, and spell_start. When these change, output your results. If the spell_start remains the same, then collect your ward_start and ward_end data for that spell until, as before, either the spell_id, ward, or spell_start change.


==================================
adaptive uber info galaxies (bigger, better, faster, and more adept than agile big data clouds)