Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


a question about creating a group in oracle SQL

a question about creating a group in oracle SQL

a question about creating a group in oracle SQL

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


RE: a question about creating a group in oracle SQL

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)

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close