Help With Multi-Row comparison logic 1

[hneal98]

I am trying to do a comparison of material loaded to different machines at different times/dates. I need to find 8 minute gaps and 1 hour gaps in dates.

Here is an example of data:
<code>
Row_id Date LotID Amount Machine
59ACD 2013-07-10 08:50:21.000 209 54.72 m3
5F68B 2013-08-05 12:20:26.000 212 92.69 m4
8404E 2013-08-06 20:39:50.000 212 96.72 m4
870BE 2013-08-14 05:38:18.000 208 96.72 m4
C726C 2013-08-08 09:25:24.000 213 96.72 m4
7893A 2013-07-15 12:06:34.000 210 60.72 m4
E0674 2013-07-12 21:05:45.000 210 78.48 m2
58A16 2013-08-12 09:50:42.000 214 95.04 m4
46F4F 2013-07-26 00:58:42.000 211 95.04 m4
DC0FA 2013-07-01 10:01:06.000 209 50.4 m4
63199 2013-07-09 05:19:58.000 209 44.44 m3
DA71D 2013-07-09 18:00:59.000 209 46.56 m2
454F3 2013-07-24 11:34:29.000 211 95.04 m2
</code>

Sorry, the table columns don't separate very well.

I need to compare one row to another to find the time gaps. As you can see, the row_id is pretty random, so I can't rely on that to compare one less than the other. Any help is appreciated. Ask if you need more info.

Thanks,

Harv

 

[fredericofonseca]

gap in time only, or time per machine?

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[hneal98]

Gap in time only. I can probably figure out the rest myself. I just need to know how to identify where there were 8 minutes or 1 hour gaps.

Thanks

 

[fredericofonseca]

the following will give you the rows you need in EXACT difference of 8 or 60 minutes - including the seconds. Should also enable you to tweak for other differences, including the >8 and >60

;with base_t as
(
select Row_id 
      ,Date
      ,LotID
      ,Amount
      ,Machine
      ,row_number() over (order by date) as rownum
from tbl_name
)
select *
from base_t t
inner join base_t t1
on t1.rownum < t.rownum
and datediff(minute, t1.date, t.date) in (8,60)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[hneal98]

Interesting. I never thought of doing it that way. I will give it a try.

Thanks.

 

[fredericofonseca]

Just noticed that the previous sample wasn't exactly what I had in mind.

The previous code would give any row before the current that had the desired interval.
This one gives the rows where the previous row in time has the desired difference

;with base_t as
(
select Row_id 
      ,Date
      ,LotID
      ,Amount
      ,Machine
      ,row_number() over (order by date) as rownum
from tbl_name
)
select *
from base_t t
inner join base_t t1
on t1.rownum = t.rownum - 1
and datediff(minute, t1.date, t.date) in (8,60)

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[hneal98]

I did notice that and made a change to it.

It does work. Thank you for your help.