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.
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.
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
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)
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_at
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