Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Datediff each time Street field changes

Posted on 2011-09-19
9
Medium Priority
?
350 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
ID: 36583972
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
ID: 36584255
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 2000 total points
ID: 36584808
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

Author Comment

by:BobRosas
ID: 36588146
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
ID: 36588152
Thanks for the great code.
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 36588159
Thanks for the kind words - come again!
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

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

In this article, we’ll look at how to deploy ProxySQL.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
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…

916 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