How to get the time diff from on item to another

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

How to get the time diff from on item to another

Author

Topic

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-06-10 : 06:51:44

I have a transaction that has multiple associated items. I need to get the time difference from 1 item to the next. I had a query that initially did work, but it relies on the RecordSequenceNumber of the item being sequential. However, I have discovered this is not the case, and is not something we can control. Therefore, I have the following query, which I need to change to cater for RecordSequenceNumbers that are not always 1,2,3... and can be 1,4,5,8,20 etc

begin tran
CREATE TABLE ItemFastFact
(TransactionID int
, RecordDateTime datetime
, RecordSeqNo int
, ScanGap int
, IsFirstScannedItemFlg bit
, IsLastScannedItemFlg bit
, IsInFirstThirdScannedItemFlg bit
, IsInLastThirdScannedItemFlg bit)

INSERT INTO ItemFastFact 
select 1, '20090101 17:23:11', 1, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:14', 2, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:16', 3, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:21', 4, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:26', 5, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:30', 6, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:33', 7, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:34', 8, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:34', 9, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:24:12', 1, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:25:14', 2, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:25:17', 3, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:25:30', 4, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:12', 2, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:14', 4, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:17', 6, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:30', 9, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:32', 15, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:39', 16, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:45', 17, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:25:47', 20, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:26:41', 22, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:26:45', 25, null, 0, 0, 0, 0

-- Calculate the difference in time from when an item is scanned and the subsequent item
-- **********************************************************************
-- This identifies the first item in a transaction
UPDATE	ItemFastFact 
SET		ScanGap = 0
FROM	ItemFastFact iff1
INNER JOIN 
		(
		SELECT	  TransactionID
				, MIN(RecordSeqNo)AS MinRecordSeqNo
		FROM ItemFastFact
		GROUP BY TransactionID
		)AS iff2
ON		iff1.TransactionID	= iff2.TransactionID
AND		iff1.RecordSeqNo	= iff2.MinRecordSeqNo


-- Update the difference in time from one record to the next
UPDATE	iff1 
SET		ScanGap = DATEDIFF(ss,iff2.RecordDateTime,iff1.RecordDateTime) 
FROM	ItemFastFact iff1
INNER JOIN ItemFastFact iff2
ON		iff1.TransactionID	= iff2.TransactionID
AND		iff1.RecordSeqNo	= iff2.RecordSeqNo+1
-- *************************************************************************************
rollback tran

Any ideas? I presume I can use the ROW_NUMBER() function, but am not really sure how to code it to be most performant?

Thanks

Hearty head pats

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 07:14:53

[code]CREATE TABLE ItemFastFact
(TransactionID int
, RecordDateTime datetime
, RecordSeqNo int
, ScanGap int
, IsFirstScannedItemFlg bit
, IsLastScannedItemFlg bit
, IsInFirstThirdScannedItemFlg bit
, IsInLastThirdScannedItemFlg bit)

INSERT INTO ItemFastFact
select 1, '20090101 17:23:11', 1, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:14', 2, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:16', 3, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:21', 4, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:26', 5, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:30', 6, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:33', 7, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:34', 8, null, 0, 0, 0, 0
UNION ALL
select 1, '20090101 17:23:34', 9, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:24:12', 1, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:25:14', 2, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:25:17', 3, null, 0, 0, 0, 0
UNION ALL
select 2, '20090101 17:25:30', 4, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:12', 2, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:14', 4, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:17', 6, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:30', 9, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:32', 15, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:39', 16, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:23:45', 17, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:25:47', 20, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:26:41', 22, null, 0, 0, 0, 0
UNION ALL
select 3, '20090101 17:26:45', 25, null, 0, 0, 0, 0

-- Calculate the difference in time from when an item is scanned and the subsequent item
-- **********************************************************************
-- This identifies the first item in a transaction
UPDATE ItemFastFact
SET ScanGap = 0
FROM ItemFastFact iff1
INNER JOIN
(
SELECT TransactionID
, MIN(RecordSeqNo)AS MinRecordSeqNo
FROM ItemFastFact
GROUP BY TransactionID
)AS iff2
ON iff1.TransactionID = iff2.TransactionID
AND iff1.RecordSeqNo = iff2.MinRecordSeqNo

-- Update the difference in time from one record to the next
--UPDATE iff1
--SET ScanGap = DATEDIFF(ss,iff2.RecordDateTime,iff1.RecordDateTime)
--FROM ItemFastFact iff1
--INNER JOIN ItemFastFact iff2
--ON iff1.TransactionID = iff2.TransactionID
--AND iff1.RecordSeqNo = iff2.RecordSeqNo+1

update iff
set ScanGap = DATEDIFF(ss,iff2.RecordDateTime,iff1.RecordDateTime)
from ItemFastFact iff
join
(
select ScanGap,TransactionID,RecordDateTime,ROW_NUMBER() over (PARTITION by TransactionID order by RecordDateTime) as rownum from ItemFastFact
) as iff1
on iff.TransactionID = iff1.TransactionID
AND iff.RecordDateTime = iff1.RecordDateTime
join
(
select TransactionID,RecordDateTime,ROW_NUMBER() over (PARTITION by TransactionID order by RecordDateTime) as rownum from ItemFastFact
) as iff2
ON iff1.TransactionID = iff2.TransactionID
AND iff1.rownum = iff2.rownum+1

select * from ItemFastFact
drop table ItemFastFact[/code]

No, you're never too old to Yak'n'Roll if you're too young to die.

Bex
Aged Yak Warrior

580 Posts

Posted - 2009-06-10 : 07:42:14

Thanks so much! My solution was rubbish and taking ages, but yours is so much better!!
Thanks, I'm a happy chappy

Hearty head pats

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 07:56:57

welcome

Maybe there is someone to have a look to make this solution much better but I was straightened to give a solution quickly.

No, you're never too old to Yak'n'Roll if you're too young to die.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-10 : 10:03:43

try this. I just do a quit modify from webfred's solution, didn't really execute the query ..


;with iff (TransactionID, RecordDateTime, ScanGap, rownum)
as
(
	select	TransactionID, RecordDateTime, ScanGap, 
		ROW_NUMBER() over (PARTITION by TransactionID order by RecordDateTime) as rownum
	from	ItemFastFact
) 
update	iff1
set	ScanGap	= isnull(datediff(second, iff2.RecordDateTime, iff1.RecordDateTime), 0)
from	iff as iff1
	left join iff as iff2	on	iff1.TransactionID	= iff2.TransactionID
				and	iff1.rownum		= iff2.rownum + 1

KH
[spoiler]Time is always against us[/spoiler]

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-10 : 10:38:18

Old Fox!

No, you're never too old to Yak'n'Roll if you're too young to die.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources