MsSQL

 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me



Go Back   Tutorialized ForumsDatabasesMsSQL

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Tutorialized Forums Sponsor:
  #1  
Old November 18th, 2013, 12:44 AM
Harish@dts Harish@dts is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Nov 2013
Posts: 4 Harish@dts User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 2 h 19 m 9 sec
Reputation Power: 0
How to query to select second row column data in first row and how to find the diff

Hi I have a requirement like below can any one help for me.
input table will be like below:
eventdata:
accountID deviceID timestamp speedKPH address
--------- -------- ---------- -------- -------------
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359088796 0 Ullalu Road
preva1 bolero 1359088886 0 Ullalu Road
preva1 bolero 1359088888 8.47 Ullalu Road
preva1 bolero 1359088986 0 Ullalu Road
preva1 bolero 1359088988 45 Ullalu Road
preva1 bolero 1359088996 21 Ullalu Road
preva1 bolero 1359088998 0 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089009 12 Ullalu Road
preva1 bolero 1359089006 15 Ullalu Road
preva1 bolero 1359089016 0 Ullalu Road
preva1 bolero 1359089026 0 Ullalu Road
So here i need output table like below:
stoppagedetails:
accountID deviceID from_timestamp to_timestamp diif
--------- -------- ---------- -------- -------------
preva1 bolero 1359088796 1359088888 92
preva1 bolero 1359088986 1359088988 2
preva1 bolero 1359088998 1359089006 8
preva1 bolero 1359089016

So can any one help how to write mysql query for the above reqirement.Thanks in advance..

Reply With Quote
  #2  
Old November 24th, 2013, 11:25 PM
jethrow jethrow is offline
Registered User
Tutorialized Newbie (0 - 499 posts)
 
Join Date: Aug 2013
Posts: 83 jethrow User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 16 h 59 m 36 sec
Reputation Power: 6
sql Code:
Original - sql Code
  1. WITH
  2. with_rn AS (
  3.     SELECT  *, Row_Number() OVER (ORDER BY a.timestamp) - 1 AS rn
  4.     FROM    eventdata a ),
  5. filtered AS (
  6.     SELECT  a.accountID, a.deviceID, a.timestamp,
  7.             Row_Number() OVER(ORDER BY a.timestamp) AS rn
  8.     FROM    with_rn a
  9.     LEFT    JOIN with_rn b ON a.rn = b.rn+1
  10.     WHERE   1= CASE
  11.                 WHEN a.speedKPH = 0 AND (b.speedKPH <> 0 OR b.speedKPH IS NULL) THEN 1
  12.                 WHEN a.speedKPH <> 0 AND b.speedKPH = 0 THEN 1
  13.                 ELSE 0 END)
  14. SELECT  a.accountID,
  15.         a.deviceID,
  16.         a.timestamp AS from_timestamp,
  17.         b.timestamp AS to_timestamp,
  18.         b.timestamp-a.timestamp AS diif
  19. FROM    filtered a
  20. LEFT    JOIN filtered b ON a.rn = b.rn-1
  21. WHERE   a.rn % 2 = 1

Last edited by jethrow : November 25th, 2013 at 03:02 PM.

Reply With Quote
Reply

Viewing: Tutorialized ForumsDatabasesMsSQL > How to query to select second row column data in first row and how to find the diff


Developer Shed Advertisers and Affiliates


Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump


Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 

Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.

© 2003-2018 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap