how to get datediff of less than 6 days for same equipment

Question

I want to see how many days between the equipment have been inspected, the idea is that the equipment # must be compared with another equipment with same # so for example equipment #804 must have a 3 days difference. I have been trying to use datediff and equipment that only shows once can be neglected so equipment # 1000 and 1008 can be remove

1   804 6/20/2019 13:25
2   804 6/23/2019 11:35
3   1000    6/5/2019 10:00
4   1001    6/8/2019 15:28
5   1001    6/6/2019 11:19
6   1004    6/12/2019 9:43
7   1004    6/14/2019 0:44
8   1006    6/11/2019 12:51
9   1006    6/10/2019 9:47
10  1008    6/19/2019 10:06

WITH tracker as (
select 
ROW_NUMBER() OVER (order by equipment) as RowNumber, equipment , inspected
FROM inspections_table
where inspected >= to_date('06-01-2019', 'mm-dd-yyyy')
and inspected <= to_date('06-30-2019', 'mm-dd-yyyy')
)

select * from tracker

804 is 3 days 1001 is 2 days 1004 is 2 days 1006 is 1 day

You may have more than two records for particular equipment ID — IgorM, Jul 19 '19 at 19:49

IgorM · Answer 1 · 2019-07-19T20:09:48.073

Here is the code

    -- All data
;WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY eqID) AS RowNo, eqID, maintDate FROM TblData)

SELECT data1.eqID, data1.maintDate, ISNULL(DATEDIFF(d, data2.maintDate, data1.maintDate), 0) AS [DiffDays]
FROM CTE data1
    LEFT OUTER JOIN CTE data2 ON data1.RowNo = data2.RowNo + 1
                              AND data1.eqID = data2.eqID
ORDER BY data1.eqID

-- Only relevant data: equipment id and date diff
;WITH CTE AS (SELECT ROW_NUMBER() OVER (ORDER BY eqID) AS RowNo, eqID, maintDate FROM TblData)

SELECT data1.eqID, /*data1.maintDate,*/ ISNULL(DATEDIFF(d, data2.maintDate, data1.maintDate), 0) AS [DiffDays]
FROM CTE data1
    LEFT OUTER JOIN CTE data2 ON data1.RowNo = data2.RowNo + 1
                              AND data1.eqID = data2.eqID
WHERE ISNULL(DATEDIFF(d, data2.maintDate, data1.maintDate), 0) <> 0
ORDER BY data1.eqID

It is important to state that there is similar answer here SQL Datediff - find datediff between rows

I just had to address equipment ID as following AND data1.eqID = data2.eqID

TblData looks as following:

And here is the result of both queries:

how to get datediff of less than 6 days for same equipment

1 Answers1