Solved

Subtracting rows as opposed to columns

Posted on 2013-12-01
1
177 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
1 Comment
 
LVL 25

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…

688 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