Site Wide Message: (current site time 9/9/2010 8:38:14 AM EDT)
- We want your input! One of our sponsors wants to know your opinion about development related issues. Click here to tell us what you think.
- Are you an emerging/young developer (aged 18-30)? If so, would you like the chance to affect future developer tools and products?
If so, then click here to give your feedback.
|
'Ask the SQL Pros Discussion Forum
You are in:
Discussion Home | programming
| Advanced SQL coding, please help
Advanced SQL coding, please help
|
|
Author:
bob_oswald (208.166.206.19) on 2/8/2006 11:54:46 AM
|
|
Message: |
I have a situation where I need to create 1 or more records based on the following analysis�
I have a Coverage table with records as follows;
MEMBER FROM TO
1 1/1/2000 12/31/2001
1 3/1/2002 4/30/2004
The following PCP table records;
MEMBER PCP FROM TO
1 101 1/1/2000 6/30/2000
1 108 9/1/2000 2/28/2002
1 101 3/1/2002 NULL
The member, based on hi s Coverage records should have the follwing PCP table recs;
MEMBER PCP FROM TO
1 101 1/1/2000 6/30/2000
1 Unknown 7/1/2000 8/30/2000
1 108 9/1/2000 12/31/2001
1 101 3/1/2002 4/30/2004
First rec in PCP table is fine.
Second rec needs to be created to show the time period where member was active with no PCP.
Third rec needs to have To date changed to 12/31/2001 because Coverage was ended at that date.
Last rec needs to have To date that matches coverage ending date of 4/30/2004
What I am doing is making sure that the P CP records match the Coverage dates. i.e. We don�t want any overlapping dates and only want to show PCP dates for when the member was active.
The problem is that the PCP records only tell when a PCP was assigned to that member with no regard to when he/she was active in the plan.
Can anyone give me an example of how to do this in SQL?
Thanks for taking time to help me.
bob
|
To reply, please login using the link on the upper left hand corner of the screen.
|
|
