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 Go to next message
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 Go to previous message
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
Previous Topic: how to gather statistics in oracle 9i
Next Topic: how to rewrite this query
Goto Forum:
  


Current Time: Mon Feb 10 01:22:17 CST 2025