Solved

Datediff each time Street field changes

Posted on 2011-09-19
9
312 Views
Last Modified: 2012-05-12
EE helped me with code on another question - Thank You!  But I didn't account for the time between stops and I haven't been able to figure out how to change the code.  I made simple format changes but I think (hope) the code I need will make the code I currently have easier.  I want to get the "Timetag" as TimeIn of the first "Street" then when the street changes use the "Timetag" of that record as the timeOut.  Here is sample data that I hope makes it more clear…
RecID      UserID                      Timetag      Street
79253218871      104636      9/1/11 0:00      576 N FIR ST
79253265123      104636      9/1/11 0:05      576 N FIR ST
79253359782      104636      9/1/11 0:14      576 N FIR ST
79253549386      104636      9/1/11 0:33      576 N FIR ST
79253596497      104636      9/1/11 0:34      576 N FIR ST
79253596499      104636      9/1/11 0:35      584 N FOREST ST
79253689846      104636      9/1/11 0:45      584 N FOREST ST
79253783631      104636      9/1/11 0:54      584 N FOREST ST
79253879577      104636      9/1/11 1:06      584 N FOREST ST
79253927275      104636      9/1/11 1:08      576 N FIR ST
79253976483      104636      9/1/11 1:13      576 N FIR ST
79254072623      104636      9/1/11 1:23      576 N FIR ST
79254121169      104636      9/1/11 1:26      576 N FIR ST
The code I currently have gives me the TimeIn and TimeOut if the "Street" feild matches...
At 576 N Fir from 0:00 to 0:34
At 385 N Forest from 0:35 to 1:06
At 576 N Fir from 1:08 to 1:26
But what I should have asked for was to include the time between "Streets"...
At 576 N Fir from 0:00 to 0:35
At 385 N Forest from 0:35 to 1:08
At 576 N Fir from 1:08 to 1:26

Hopefully that makes sense. Once again  I could really use your expertise.  Thanks in advance!

===============
Prior related question:
http:/Q_27312333.html
---this is the main result set
select 
  u.name as Truck
, arr.Street
, CONVERT(varchar(10), CONVERT(datetime, arr.Timetag), 101) AS Date
, right(CONVERT(char(20), CONVERT(datetime, arr.Timetag), 100),8) as Timein
, right(CONVERT(char(20), CONVERT(datetime, dep.Timetag), 100),8) as Timeout
, datediff(mi,arr.timetag, dep.timetag) as Minutes
------------
from 
(
select *, row_number() over(partition by userid order by timetag) as dseq
 from SeqGPS a
 where not exists 
    (select * from SeqGPS b 
     where b.userid = a.userid
       and b.street = a.street  
       and b.seq = a.seq + 1 )
) as dep
inner join 
(
select *, row_number() over(partition by userid order by timetag) as aseq
from SeqGPS b 
 where not exists 
    (select * from SeqGPS a 
     where b.userid=a.userid
       and b.street = a.street  
       and b.seq = a.seq + 1 )
) as arr
on  arr.userid=dep.userid
and arr.aseq = dep.dseq
inner join
Users AS U ON arr.UserID = U.UserID WHERE (U.Name = @Truck) AND (CONVERT(char(10), CONVERT(datetime, arr.Timetag), 101) BETWEEN @From AND @To) AND 
(CONVERT(CHAR(5),datediff(mi,arr.timetag, dep.timetag), 108) <> '0')

order by dep.timetag

Open in new window

0
Comment
Question by:BobRosas
  • 3
  • 3
9 Comments
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
Here's how I would do it:

create view Beginnings as -- returns beginnings of periods on each street
SELECT     row_number() OVER (ORDER BY timetag) AS seqno, recid, timetag, userid
FROM         seqgps s0
WHERE     NOT EXISTS
                          (SELECT     1
                            FROM          seqgps s
                            WHERE      s.userid = s0.userid AND s.street = s0.street AND s.recid =
                                                       (SELECT     max(recid)
                                                         FROM          seqgps s2
                                                         WHERE      s2.userid = s.userid AND s2.recid < s0.recid))


final query:

SELECT     beginnings.seqno, SeqGPS.Street, SeqGPS.Timetag, SeqGPS_next.Timetag AS Expr1
FROM         beginnings
INNER JOIN                      beginnings AS beginnings_next
ON beginnings.seqno + 1 = beginnings_next.seqno AND beginnings.userid = beginnings_next.userid INNER JOIN                      SeqGPS ON beginnings.recid = SeqGPS.RecID
INNER JOIN                      SeqGPS AS SeqGPS_next ON beginnings_next.recid = SeqGPS_next.RecID

Note that the last period is not returned because we don't know the next one.
0
 

Author Comment

by:BobRosas
Comment Utility
Thank you so much for your help!  
I only made a couple changes and was able to run the code.  I have data but it's not what I expected.  The results are coming from the Beginning query and not the final query.  So I don't have "TimeIn" and "TimeOut" data... I attached an Image.  I'm getting ready to leave for the day but I will spend more time with it tomorrow and get back to you as soon as I can.
Thanks again!
--create view Beginnings as -- returns beginnings of periods on each street
SELECT row_number() OVER (ORDER BY timetag) AS seqno, recid, timetag, userid
FROM GPSData s0
WHERE NOT EXISTS
      (SELECT 1
       FROM GPSData s
       WHERE s.userid = s0.userid AND s.street = s0.street AND s.recid =
        (SELECT max(recid)
         FROM GPSData s2
          WHERE s2.userid = s.userid AND s2.recid < s0.recid))
--final query
SELECT beginnings.seqno, GPSData.Street, GPSData.Timetag, GPSData_next.Timetag AS Expr1
FROM beginnings 
INNER JOIN beginnings AS beginnings_next 
ON beginnings.seqno + 1 = beginnings_next.seqno AND beginnings.userid = beginnings_next.userid INNER JOIN                      
GPSData ON beginnings.recid = GPSData.RecID 
INNER JOIN                      
GPSData AS GPSData_next ON beginnings_next.recid = GPSData_next.RecID

Open in new window

GPS.jpg
0
 
LVL 40

Accepted Solution

by:
Vadim Rapp earned 500 total points
Comment Utility
CREATE TABLE [dbo].[GPSData](
      [RecID] [bigint] NOT NULL primary key,
      [UserID] [varchar](50) NULL,
      [Timetag] [datetime2](7) NULL,
      [Street] [varchar](50) NULL
go

data:  data.csv


CREATE VIEW [dbo].[beginnings]
AS
SELECT     row_number() OVER (ORDER BY timetag) AS seqno, recid, timetag, userid
FROM         gpsdata s0
WHERE     NOT EXISTS
                          (SELECT     1
                            FROM          gpsdata s
                            WHERE      s.userid = s0.userid AND s.street = s0.street AND s.recid =
                                                       (SELECT     max(recid)
                                                         FROM          gpsdata s2
                                                         WHERE      s2.userid = s.userid AND s2.recid < s0.recid))





Query:
SELECT beginnings.seqno, GPSData.Street, GPSData.Timetag, GPSData_next.Timetag AS Expr1
FROM beginnings
INNER JOIN beginnings AS beginnings_next
ON beginnings.seqno + 1 = beginnings_next.seqno AND beginnings.userid = beginnings_next.userid INNER JOIN                      
GPSData ON beginnings.recid = GPSData.RecID
INNER JOIN                      
GPSData AS GPSData_next ON beginnings_next.recid = GPSData_next.RecID


result:
1      576 N FIR ST      2011-09-01 00:00:00.0000000      2011-09-01 00:34:00.0000000
2      584 N FOREST ST      2011-09-01 00:34:00.0000000      2011-09-01 01:08:00.0000000
0
What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

 

Author Comment

by:BobRosas
Comment Utility
Thank you so much!  The code appears to give me just what I asked for.  I really appreciate all your help.  I've maxd out the points and will close this and ask related question if needed.
0
 

Author Closing Comment

by:BobRosas
Comment Utility
Thanks for the great code.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
Comment Utility
Thanks for the kind words - come again!
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
SLQ View not updating 10 46
SSRS displaying blank space between rows 6 10
encyps queries mssql 15 24
Sql query 34 16
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how the fundamental information of how to create a table.

772 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

10 Experts available now in Live!

Get 1:1 Help Now