Home » SQL & PL/SQL » SQL & PL/SQL » Identifying Dates and Create Coverage Periods (Oracle, 10.1.0.2.0, Windows XP SP2)
Identifying Dates and Create Coverage Periods [message #417641] |
Mon, 10 August 2009 13:13  |
SteveStL
Messages: 1 Registered: August 2009
|
Junior Member |
|
|
Thanks for reading my post. I am a new Mbr to the Forum.
I have been given a tricky assignment and thought I'd ask the group to see if anyone has done this themselves or anything similar to it. It involves transforming Eligibility Coverage Periods, and there are some complex rules around what is being pulled from the Source ODS Tables. What I need to do is probably best explained with an example. There is a Memberid, Groups, Startdates and Termdates.
A Mbr belongs to Group abc with one Coverage Period.
mbrid: 123
group: abc
Startdate: 1/1/2000
Enddate: 12/31/2005
and this member also belongs to Group xyz with several periods.
mbrid: 123
group: xyz
Startdate: 1/1/2001
Enddate: 12/31/2001
mbrid: 123
group: xyz
Startdate: 1/1/2003
Enddate: 12/31/2003
mbrid: 123
group: xyz
Startdate: 1/1/2005
Enddate: 12/31/2005
In this example Group xyz's coverage periods takes priority so I need to pull those periods first
123, xyz, 1/1/2001, 12/31/2001
123, xyz, 1/1/2003, 12/31/2003
123, xyz, 1/1/2005, 12/31/2005
Now I need to recognize that group abc has one large period which overlaps the ones I just imported and so I need to make smaller spans for years 2000, 2002, and 2004.
Final Results should look like this
123, abc, 1/1/2000, 12/31/2000
123, xyz, 1/1/2001, 12/31/2001
123, abc, 1/1/2002, 12/31/2002
123, xyz, 1/1/2003, 12/31/2003
123, abc, 1/1/2004, 12/31/2004
123, xyz, 1/1/2005, 12/31/2005
Any advice, code, thoughts, etc. is greatly appreciated
Steve
|
|
|
Re: Identifying Dates and Create Coverage Periods [message #417642 is a reply to message #417641] |
Mon, 10 August 2009 13:17  |
 |
Michel Cadot
Messages: 68733 Registered: March 2007 Location: Saint-Maur, France, https...
|
Senior Member Account Moderator |
|
|
Welcome to the forum.
Use SQL*Plus and copy and paste what you already tried.
Post a working Test case: create table and insert statements along with the result you want with these data.
Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter), use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Regards
Michel
|
|
|
Goto Forum:
Current Time: Mon Feb 10 01:22:17 CST 2025
|