Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Datediff each time Street field changes

Posted on 2011-09-19
9
Medium Priority
?
344 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
[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
  • 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
Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

 

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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

705 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