Home Forums SQL Server 2008 T-SQL (SS2K8) New to SQL - How to compare two rows and two different columns RE: New to SQL - How to compare two rows and two different columns

  • It depends. The way the current code I provided works it just links the first 2 rows for a specific med_rec_no regardless of gap. So if I have something like this:

    med_rec_no Patient Type AdmitDate DischargeDate

    1 John Smith 1 1/1/2014 13:00 1/1/2014 17:00

    1 John Smith 2 2/1/2014 09:00 2/2/2014 11:00

    My code would return:

    med_rec_no Patient OrigType NewType OrigAdmitDate NewAdmitDate OrigDischargeDate NewDischargeDate

    1 John Smith 1 2 1/1/2014 13:00 2/1/2014 09:00 1/1/2014 17:00 2/2/2014 11:00

    Since this is likely 2 distinct visits you probably don't want that so you may want to add to the join that V1.DischargeDate = V2.AdmitDate, possibly with some fudge factor if it takes a few minutes for the new admission to take place after the "discharge"