Solved

Subtracting rows as opposed to columns

Posted on 2013-12-01
1
171 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 24

Accepted Solution

by:
chaau earned 500 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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
As a database administrator, you may need to audit your table(s) to determine whether the data types are optimal for your real-world data needs.  This Article is intended to be a resource for such a task. Preface The other day, I was involved …
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now