I have a bit of a complex problem. I have a table that contains hospital admissions, skilled nursing admissions and hospice admissions.
There are also admission dates and discharge dates and the member patient number. There are 125715 rows that are not distinct. The table looks similiar to this.
id admit disch srvc
111 8/13/2010 8/18/2010 hospital
111 8/18/2010 8/20/2010 skilled nursing
111 8/20/2010 8/30/2010 hospice
222 7/1/2010 7/15/2010 hospital
333 9/19/2011 9/22/2011 hospital
333 9/26/2011 9/29/2011 hospital
333 9/29/2011 10/6/2011 skilled nursing
I need to separate out those that are really hospital such as id 222 and the 1 id 333 because the one that is 333 was discharged 9/22/2011 and the next admission is not until 9/26/2011. The ones that are skilled nursing but also hospital need to be in another table that have a disch date the same as admission date. So I should end up with 2 tables that look like this.
id admit disch srvc
222 7/2/2010 7/15/2010 hospital
333 9/19/2011 9/22/2011 hospital
the 2nd table
id admit disch srvc
111 8/13/2010 8/18/2010 hospital
111 8/18/2010 8/20/2010 skilled nursing
111 8/20/2010 8/30/2010 hospice
333 9/26/2011 9/29/2011 hospital
333 9/29/2011 10/6/2011 skilled nursing
Just not sure how to query this to make it work.