Subtracting dates that are not in the same row

Question

Here is my issues.  I need to subtract dates that live in different rows.  For example

Admit Date                      Transfer Date                 Resumption of Care

01/01/12                           01/05/12                           01/08/12

                                        01/16/12                            01/20/12

I need to subtract 01/16/12 from 01/08/12 to get 8 days between transfers.  I am posting my SQL with this.  Any help would be appreciated.

SELECT DISTINCT PT_BASIC.NAME_FULL,
                PT_STATUS.STATUS_CODE,
                PT_STATUS.STATUS_DATE,
                O_DATASET.DATASET_NAME,
                C_DIAGNOSIS.DIAGNOSIS,
                C_DIAGNOSIS.ICD9_CODE,
                PT_ADMISSION.ADMIT_DATE,
                Case when PT_STATUS.STATUS_DATE >= PT_ADMISSION.ADMIT_DATE then 'Y' else 'N' end Case_when_PT_STATUS_STATUS,
                DATEADD(day,1,PT_STATUS.STATUS_END_DATE) DATEADD_day_1_PT_STATUS_S,
                DATEDIFF(DD, PT_ADMISSION.ADMIT_DATE, ISNULL(PT_STATUS.STATUS_DATE, GETDATE())) DATEDIFF_DD_PT_ADMISSION,
                DATEDIFF(DD, PT_STATUS.STATUS_DATE, ISNULL(DATEADD(day,1,PT_STATUS.STATUS_END_DATE), GETDATE())) DATEDIFF_DD_PT_STATUS_STA
FROM PT_STATUS PT_STATUS
      INNER JOIN PT_BASIC PT_BASIC ON
     (PT_BASIC.PATIENT_ID = PT_STATUS.PATIENT_ID)
      AND (PT_BASIC.DATASET_ID = PT_STATUS.DATASET_ID)
      INNER JOIN O_DATASET O_DATASET ON
     (O_DATASET.DATASET_ID = PT_BASIC.DATASET_ID)
      INNER JOIN PT_ADMISSION PT_ADMISSION ON
     (PT_ADMISSION.PATIENT_ID = PT_STATUS.PATIENT_ID)
      AND (PT_ADMISSION.ADMISSION_ID = PT_STATUS.ADMISSION_ID)
      INNER JOIN PTC_DIAGNOSIS PTC_DIAGNOSIS ON
     (PTC_DIAGNOSIS.PT_DIAGNOSIS_ID = PT_STATUS.PRIMARY_DIAGNOSIS_ID)
      INNER JOIN C_DIAGNOSIS C_DIAGNOSIS ON
     (C_DIAGNOSIS.DIAGNOSIS_ID = PTC_DIAGNOSIS.DIAGNOSIS_ID)
      AND (C_DIAGNOSIS.DIAGNOSIS_SET_ID = PTC_DIAGNOSIS.DIAGNOSIS_SET_ID)
WHERE ( O_DATASET.DATASET_NAME = 'Home Health' )
       AND (( PT_STATUS.STATUS_CODE = 'T01' )
       OR ( PT_STATUS.STATUS_CODE = 'F34' ))
       AND ( PT_ADMISSION.ADMIT_DATE IS NOT NULL  )
       AND ( PT_ADMISSION.TERMINATION_DATE IS NULL  )
       AND ( C_DIAGNOSIS.ICD9_CODE IN ('486','428.0','493.22') )
       AND ( Case when PT_STATUS.STATUS_DATE >= PT_ADMISSION.ADMIT_DATE then 'Y' else 'N' end = 'Y' )
ORDER BY C_DIAGNOSIS.ICD9_CODE

Answer 1

Hi

You can try with following example

Declare @tab Table(AdminDate Datetime,TransferDate Datetime,Care Datetime)
set dateformat mdy
Insert into @tab Values
('01/01/12','01/05/12','01/08/12')
,(null,'01/16/12','01/20/12')
Select AdminDate,TransferDate,Care,DateDiff(Day,Care,(Select Max(TransferDate) From @tab where TransferDate<=Care))
from @tab

---

Mark as Answer If Reply Was Helpful
Thanks
Kuldeep Bisht
Technical Lead @ Simplion Technologies
Blog : www.dbsimplified.com

Answer 2

Please explain the logic behind the scene? MAX transfer date and MIN resumption???

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 3

The problem with entering the dates is that the table is dynamic so the dates change and more can be added.  Patients get discharged so I have to use a variable that can grab what I need.