Calculating Time Difference between two rows while resetting for a group - Hot, and Thank you Community

Question

In my case, I have different Partners (ENCOUNTER_ID). Each one can have one or multiple assessments. I need to calculate the time difference between each each one of their assessments. With time resetting for each partner. Time Difference field is the field I would like created.

In the case below Joe had two assessments, the first marked w 0 since it is the first in the instance, the second one being 81 minutes from the first one.

Mike Only had one assessment, so no calculation is needed.

Olivia had 5 assessments, the first marked as 0 since it is the first, and then each one has time difference from prior assessment.

I am attaching the sample data with the ?? field being the one I need assistance with. I am also attaching my current qry that I am using. I am using this as part of an intersect so I am attaching the code as well.

Thank you all so much, I know this is probably a piece of cake for many of you.

I tried using CTE's with row number and Over partitions, I tried using Lag window functions, and could not get the right results.

this is what I do have that is working but not giving me the right result for the time difference field.

Select distinct
   e.ENCOUNTER_ID as ENCNTR_ID
  ,d.FORM_DT_TM Assessment_DtTm
  ,count(DISTINCT e.ENCOUNTER_ID) as Assessment_Count
  ,case when e.ENCOUNTER_ID Is NOT NULL then datediff(second,d.FORM_DT_TM,lag(d.FORM_DT_TM) over(order     
    by d.FORM_DT_TM)) else null end

from KDHCD_P607.PERSON P
 join KDHCD_EDW_MILL_CDS.MD_F_ENCOUNTER E on P.person_id=E.person_id --and e.active_ind=1
 join KDHCD_P607.DCP_FormS_Activity D on P.person_id=D.person_ID and e.ENCOUNTER_ID=d.encntr_id and    
                                                          d.DCP_FORMS_REF_ID  IN  ( 2384507453  )
 join KDHCD_P607.DCP_FORMS_ACTIVITY_COMP DC on D.DCP_FORMS_ACTIVITY_ID=DC.DCP_FORMS_ACTIVITY_ID and 
                                           D.active_ind=1 AND DC.PARENT_ENTITY_NAME = 'CLINICAL_EVENT'
 join kdhcd_p607.clinical_event cs on cs.PARENT_EVENT_ID=dc.PARENT_ENTITY_ID and  Cs.VIEW_LEVEL=0 
 join  kdhcd_p607.clinical_event cd on cs.EVENT_ID=Cd.PARENT_EVENT_ID and Cd.VIEW_LEVEL=1 and 
                              cd.event_Cd in (703614)  --Field that differs from both queries

where p.ACTIVE_IND=1 
  and e.REGISTRATION_DT_TM > '11/1/2023'
group by 
  e.ENCOUNTER_ID 
 ,d.FORM_DT_TM

Answer 1

I figured it out. thank you all either way.

**,case when e.ENCOUNTER_ID is not null 
        then (datediff(MINUTE,d.FORM_DT_TM,lag(d.FORM_DT_TM) over(Partition by 
 e.ENCOUNTER_ID order by d.FORM_DT_TM)))* -1 
        else -1 end**