0

I have a table which looks like this: Source Table

I'm trying to write a query which will return this: Desired result

I'm trying to merge records based on the effect_date, but only if the end_dates are within the effect_date and end_date range.

Improve this question
2
  • 5
    How can the effect_date value not be within a range that contains itself as one of the boundaries? Commented Feb 19, 2012 at 4:09
  • The end_date of 1/21/2012 is not between effect_date 1/22/2012 and end_date 2/4/2012. Commented Feb 19, 2012 at 4:33

2 Answers 2

Reset to default
1 
select employee , effect_date , max(end_date) end_date , max(clinical_fte) , max(admin_fte) , max(mgmt_fte) , max(other_fte) from table group by employee , effect_date

As stated before by Chris Farmer, the second requirement that end date has to be between effect_date and end_date is silly, because it will always be true. I've chosen max for all records you want to merge, because you haven't stated how you want to merge them. Feel free to adjust to your needs ;)

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

He said merge, I would have assumed sum(...) on the numerics would be more appropriate?
Using max(end_date) solves part of the problem. That merges the first 2 records together. I now need to merge the 2nd and 3rd records together. It looks like I explained the issue poorly.
0

Try this SQL Query:

select * from TABLE where end_date between effect_date and end_date
Improve this answer

1 Comment

If you'll notice, I'm merging the first 2 records into 1, using the effect_date and the greatest end_date. I then need to merge the last 2 records into 1, retaining the effect_date and again getting the greatest end_date, which in this case is the same.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.