Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Subtracting rows as opposed to columns

Posted on 2013-12-01
1
Medium Priority
?
180 Views
Last Modified: 2013-12-02
I have a table that holds the end time, activity id, record number. What I need to do is subtract record 2's time from record 1' time, record 4's time from record 3's time.

table data
started      activity Id      record Id
11/14/2013 11:06:29 PM      1257      1
11/15/2013 2:09:57 PM      1291      2
11/18/2013 4:26:34 AM      1257      3
11/18/2013 4:48:53 PM      1291      4
11/19/2013 2:54:52 AM      1257      5
11/19/2013 3:42:00 PM      1291      6
11/21/2013 2:24:38 AM      1257      7
11/21/2013 2:08:06 PM      1291      8

I have no idea how to do this, can someone please assist.
0
Comment
Question by:portlight
1 Comment
 
LVL 25

Accepted Solution

by:
chaau earned 2000 total points
ID: 39688703
If the `record id` of the second row is always bigger by one, then this statement will calculate you the time difference (in seconds)
select a.started a_started, 
b.started b_started, 
timestampdiff(second, a.started, b.started) secondsPassed
FROM Table1 a INNER JOIN Table1 b
ON a.`record Id` = b.`record id` - 1

Open in new window

0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses
Course of the Month11 days, 13 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question