Query to calculate time difference between current and previous record

Transact-SQL
#1

I need to calculate how many days went by between the first record and the second record and continue all the way down the list.example

The record marked as "View Ready" will be the first one and will not have any value as "Actual Interval". The second record, marked as "Inspection" should display the difference in days between ActualDate of 1/20/2017 and 1/1/2017. The third record should display the difference between ActualDate and the ActualDate from the previous Inspection and so on.

anyone can try to explain how I should do it?

Thanks
rick

#3

Hi Pasi...what happened...I was just about to test your query.

#4

Select ItemID, TypeiD,Status,ActualDate, Atcual Interval,IdealInterval, Gap
LAG(ActualDate,1,null) OVER (PARTITION BY TYPEID
ORDER BY AcualDate) AS PreviousRecord
FROM dbo.yourdB

1 Like
#5

I think i'm in the right path...let me mess with that a bit more. I'll let you know. For now, thank you very much.

#6

YW. , this worked for me on one of my queries but not sure of yours? also look into
DATEDIFF( day, orders.ship_time, orders.arrival_time)
DATE_PART('day', orders.arrival_time - orders.ship_time)

1 Like
#7

It's almost perfect. The problem is that I can't get the value for the first inspection. Let me show you.

WITH CTE
AS
(
SELECT
p.ah4r_id,
i.inspection_type_id,
i.submitted_at,
i.created_at,
st.display_name PrimoStatus,
0 RecurrentInspection
FROM primoStaging.vInspections i
INNER JOIN primoStaging.vProperties p ON i.property_id=p.id
INNER JOIN primoStaging.vScriptTypes st ON i.inspection_type_id=st.id
WHERE i.inspection_type_id=4
AND p.ah4r_id = 'CO12424'
AND i.submitted_at IS NOT NULL
UNION ALL
SELECT
p.ah4r_id,
i.inspection_type_id,
i.submitted_at,
i.created_at,
st.display_name PrimoStatus,
ROW_NUMBER() OVER(PARTITION BY i.inspection_type_id ORDER BY i.submitted_at ASC) RecurrentInspection
FROM primoStaging.vInspections i
INNER JOIN primoStaging.vProperties p ON i.property_id=p.id
INNER JOIN primoStaging.vScriptTypes st ON i.inspection_type_id=st.id
WHERE i.inspection_type_id IN (5,6)
AND p.ah4r_id = 'CO12424'
AND i.submitted_at IS NOT NULL
)
SELECT
CTE.ah4r_id PropertyID,
CTE.inspection_type_id InspectionTypeID,
CTE.submitted_at PerformedDateTime,
CTE.PrimoStatus InspectionStatus,
LAG(CTE.submitted_at) OVER (PARTITION BY CTE.inspection_type_id ORDER BY CTE.submitted_at) PreviousValue,
--LEAD(CTE.submitted_at) OVER (ORDER BY CTE.RecurrentInspection) NextValue,
--CASE
-- WHEN DATEDIFF(dd,CTE1.submitted_at,CTE2.submitted_at) IS NOT NULL THEN DATEDIFF(dd,CTE1.submitted_at,CTE2.submitted_at)
-- ELSE DATEDIFF(dd,CTE2.submitted_at,CTE3.submitted_at)
--END Days,
CTE.RecurrentInspection
FROM CTE CTE
ORDER BY CTE.submitted_atexample1

#8

If I partition by cte.ah4r_id, then it works.example2

#9

I know it's not part of your question, but may I suggest you try and eliminate the "union all" in the cte, as both selects are almost identical. I think this might work (but I couldn't test as you didn't provide consumable data)

Change for cte 
select p.ah4r_id
      ,i.inspection_type_id
      ,i.submitted_at
      ,i.created_at
      ,st.display_name as PrimoStatus
      ,case
          when i.inspection_type_id=4
          then 0
          else row_number() over(partition by i.inspection_type_id
                                     order by i.submitted_at asc
                                )
       end as RecurrentInspection
  from primoStaging.vInspections as i
       inner join primoStaging.vProperties as p
               on p.id=i.property_id
              and p.ah4r_id='CO12424'
       inner join primoStaging.vScriptTypes as st
               on st.id=i.inspection_type_id
 where i.inspection_type_id in (4,5,6)
   and i.submitted_at is not null
2 Likes
#10

funny you mentioned it...I just did. there's no need for that now.
Thank you very much for all your help. You saved me hours of work and headaches.

1 Like