Getting time difference from last transaction

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 2000 Forums

Transact-SQL (2000)

Getting time difference from last transaction

Author

Topic

hakridge
Starting Member

4 Posts

Posted - 2006-03-27 : 17:05:05

I've got a system that might need a cursor written for it (although trying to stay away from this option if it can be avoided).

I have a group of RFID's, and transactions from the first read to the final read. I need to go from transaction 1-xxx in that RFID set, and get the interval from row 1, to 2, to 3, to 4, and so forth.

Anything besides a cursor anyone can think of that I can persue as an alternative? Many thanks!

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-27 : 17:29:52

Not sure I understand the problem, what are rfid's? Anyway, maybe the article bellow can be usefull: http://www.sql-server-performance.com/mm_cursor_friendly_problem.asp

hakridge
Starting Member

4 Posts

Posted - 2006-03-27 : 18:08:40

Similar to that article you posted, however instead of a running total, a difference in seconds between the timestamps is needed. :)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-03-28 : 01:39:21

Instead of sum(), use DateDiff(second,date1,date2)

Madhivanan

Failing to plan is Planning to fail

mmarovic
Aged Yak Warrior

518 Posts

Posted - 2006-03-28 : 01:51:04

So I guess you know how to do it now?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-03-28 : 01:52:54

"what are rfid's?"
RFID = Radio Frequency Identification

"I need to go from transaction 1-xxx in that RFID set, and get the interval from row 1, to 2, to 3, to 4, and so forth."
You mean you need to find the time difference between one transaction and next transaction ?

Please post your table DDL, some sample data and the required result.

KH
Choice is an illusion, created between those with power, and those without.
Concordantly, while your first question may be the most pertinent, you may or may not realize it is also the most irrelevant

hakridge
Starting Member

4 Posts

Posted - 2006-03-29 : 11:41:50

CREATE TABLE Trans (
rfid varchar(100) NOT NULL,
loc int NOT NULL,
type char(1) NOT NULL,
last_update datetime NOT NULL,
uid int default NULL,
ant int NOT NULL default '0',
)

INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29513,'A','2006-03-26 07:41:19',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29513,'A','2006-03-27 07:54:58',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29513,'R','2006-03-26 07:41:19',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29513,'R','2006-03-27 07:55:03',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29514,'A','2006-03-27 07:55:06',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29514,'R','2006-03-27 07:55:07',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29515,'A','2006-03-26 07:41:44',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29515,'A','2006-03-27 07:55:07',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29515,'R','2006-03-26 07:41:45',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29515,'R','2006-03-27 07:55:08',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29517,'A','2006-03-26 07:42:51',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29517,'R','2006-03-26 07:42:52',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29518,'A','2006-03-26 07:43:25',1,1) INSERT INTO Trans (rfid,loc,type,last_update,uid,ant) VALUES
('0x2A51000000000000610282145590',29518,'R','2006-03-26 07:43:26',1,1);

Within the same rfid, ordered by the date. I need to know the interval difference between the A, then R transaction, then R and A, and so forth. Could be a new column called interval. Currently my idea is to use a cursor to go through each of the sets and mark the interval difference between each transaction, however I've heard that's incredibly slow for SQL Server. My question is: is there a better way to accomplish the same goal than using a cursor?

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2006-03-29 : 13:46:26

Does this work?:


select	cur.rfid
	,cur.type
	,nxt.type next_type
	,cur.last_update
	,nxt.last_update next_last_update
	,datediff(second, cur.last_update, nxt.last_update) interval
from	(
	select	cur.rfid
		,cur.type
		,cur.last_update
		,min(nxt.last_update) next_last_update
	from	Trans cur
	join	Trans nxt
			on nxt.rfid = cur.rfid
			and nxt.last_update > cur.last_update
			and nxt.type <> cur.type
	group by cur.rfid
		,cur.type
		,cur.last_update
	) cur
join	Trans nxt
		on nxt.rfid = cur.rfid
		and nxt.last_update = cur.next_last_update
		and nxt.type <> cur.type
order by 1,4

Be One with the Optimizer
TG

hakridge
Starting Member

4 Posts

Posted - 2006-03-29 : 16:17:44

TG,

Your idea helped tons. It wasn't exact, but it set me on the right path. I created a join on a condition that there would only be two rows returned. The A, and the corresponding R for that location. So then I inserted the join based on that, and the results are sheer magic sir.

This runs extremely fast, it only works on the "A" rows, but I can use the intervals given on the A to determine how long the R was for (the remaining time interval).

Much appreciated! :)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources