asked on

comparing two rows

Hi Experts,

Is it possible to output the difference in secs between two rows? Basically I am trying to find out the amount of time a student spent on Slide

Month		Time		Topic				Slide	SlideAction		timespent
Fri Sep 23	07:51:41	Likande la Linali - Kalulo 2	slide2	SlideOpened			
Fri Sep 23	07:51:42	Likande la Linali - Kalulo 2	slide3	SlideOpened			
Fri Sep 23	07:51:42	Likande la Linali - Kalulo 2	slide4	SlideOpened			
Fri Sep 23	07:51:43	Likande la Linali - Kalulo 2	slide3	SlideOpened			
Fri Sep 23	07:51:44	Likande la Linali - Kalulo 2	slide2	SlideOpened			
Fri Sep 23	07:51:49	Likande la Linali - Kalulo 2	slide3	SlideOpened			
Fri Sep 23	07:51:51	Likande la Linali - Kalulo 2	slide4	SlideOpened			
Fri Sep 23	07:51:52	Likande la Linali - Kalulo 2	slide5	SlideOpened			
Fri Sep 23	07:51:53	Likande la Linali - Kalulo 2	slide6	SlideOpened

Open in new window

Thanks

Mike

Allan Nisbet

Hi

If you have an id on the table lets call it presentationid ans table called presentations

SELECT A.presentationid, A.time, TIMESTAMPDIFF(SECOND,A.time,B.time) AS timedifference 
FROM presentations A INNER JOIN presentations B ON B.presentationid = (A.presentationid + 1) 
ORDER BY A.presentationid ASC

Open in new window

That should show you the difference in time between rows

Hope that Helps

Mike Page

ASKER

so I will have to recreate the table with an ID? as none exists

Allan Nisbet

You need something unique to be able to cycle through to get your comparisons,

ALso timediffernce should be timespent to match your table

Sharath S

What is your database with version? What is your expected result?

Mike Page

ASKER

sorry for the late reply

Microsoft SQL Server Management Studio      17.0

expected results.. just want to find the duration spent on a particular slide

for example, I can tell he spent 1 sec on slide 2 and 5 secs on slide 3

1) Fri Sep 23      07:51:44      Likande la Linali - Kalulo 2      slide2      SlideOpened            1
2) Fri Sep 23      07:51:49      Likande la Linali - Kalulo 2      slide3      SlideOpened            5

Allan Nisbet

Best practice is to have a Primary Key in the table
If you dont have this and you know the person will not return toa slide after viewing you could maybe use slide as your unique item, but if the table will contain other people viewing the slides then it wont work as well.

Put an auto incremental column at the start of the table and anytime a new record is created the id number increases by 1

Hope this Helps

Mike Page

ASKER

@Allan

I have already done this just didn't paste the revised data.. but here is the code based on yours:-

SELECT A.IDCol, A.time, DateDIFF(SECOND,A.time,B.time) AS timedifference 
FROM RefinedData A  JOIN RefinedData B ON B.IDCol = (A.IDCol + 1) 
ORDER BY A.IDCol ASC

Open in new window

I am not sure if mine produced the right answer

IDCol	time	timedifference
1	       07:51:41	0
2	       07:51:41	1
3	       07:51:42	0
4	       07:51:42	0
5	       07:51:42	0
6	       07:51:42	0
7	       07:51:42	1
8	       07:51:43	0
9	       07:51:43	0
10	       07:51:43	1

Open in new window

Allan Nisbet

Its producing the expected result but the expected result isnt what your after I think

there is no difference between row 1 and 2 so difference is 0
between 2 and 3 there is 1 second

By looking at your data im guessing id 11 is 07:51:44

Can you paste your raw table thats includes slide information and the new id's and ill think of a way to tweak it to make it as you expect

Hope this Helps

ASKER CERTIFIED SOLUTION

Allan Nisbet

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Mike Page

ASKER

cool.. I believe the way you explained it now makes sense... thanks mate!!