Solved

Min/Max datediff each time Street field changes

Posted on 2011-09-16
6
642 Views
Last Modified: 2012-05-12
I posted an EE question  and got code to do exactly what I asked for.  EE is Great!  
Unfortunately it wasn’t what I actually needed.  I’m not sure how to change what I have into what I need.  I have tracking data that shows where a truck is and for how long.  But the database is updated every couple of minutes.  So I just want to know how long the truck is at an address  but only until the street name changes.  Sample data is…
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 first and last time for each Street address (which is what I thought I wanted because I was grouping by street)
Results Example of what I have…
576 N Fir is from 0:00 to 1:26
385 N Forest from 0:35 to 1:06
But what I actually need is to re-calculate the time each time the addresses changes.  If the address repeats itself later then get the min/max at that point.
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
Hopefully that makes sense.  I tried changing my grouping but I’m still not getting the results I need.  I could really use your expertise.  Thanks in advance!

SELECT     CONVERT(varchar(10), CONVERT(datetime, T.Timetag), 101) AS Date
, CONVERT(char(5), CONVERT(datetime, T.TimeIn), 108) AS TimeIn
, CONVERT(char(5), CONVERT(datetime, T.TimeOut), 108) AS TimeOut
, U.Name AS Truck
, T.Street
, CONVERT(CHAR(5), DATEADD(ss, T.ts_mins, 0), 108) AS HrsMin
FROM   (SELECT  UserID, Street, DATEDIFF(dd, 0, Timetag) AS Timetag
, DATEDIFF(ss, MIN(Timetag), MAX(Timetag)) AS ts_mins
, MIN(Timetag) AS TimeIn
, MAX(Timetag) AS TimeOut
FROM   GPSData
GROUP BY UserID, Street, DATEDIFF(dd, 0, Timetag)) 
AS T 
INNER JOIN  Users AS U ON T.UserID = U.UserID
WHERE     (U.Name = @Truck) 
AND (CONVERT(char(10), CONVERT(datetime, T.Timetag), 101) BETWEEN @From AND @To) 
AND (CONVERT(CHAR(5), DATEADD(ss, T.ts_mins, 0),  108) <> '00:00')

Open in new window

0
Comment
Question by:BobRosas
  • 3
  • 3
6 Comments
 
LVL 42

Expert Comment

by:dqmq
ID: 36550536
Can do it in SQL, but very challenging (you may want to consider upping the points).  If you prefer to noodle through a TSQL procedure, then it's more straight forward.
0
 
LVL 42

Accepted Solution

by:
dqmq earned 500 total points
ID: 36550676
Points be dammed...I couldn't resist the challenge.

Here's some SQL that I believe works.  You need to tweek the section labeled "main result set" to get the date/time  formatting that you desire


;With SeqGPS as
(
SELECT *, row_number() over(partition by userid order by timetag) as seq from gpsdata
)

---this is the main result set
select 
  u.name as Truck
, arr.street
, arr.timetag as timein
, dep.timetag 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
(Select userid, name from Users WHERE Users.Name = @Truck) as U
on arr.userid = u.userid
order by timein

Open in new window

0
 

Author Comment

by:BobRosas
ID: 36551036
Wow!  Thank you!  I maxd out points and will close this question.  You went to a lot of work and I need to go thru the code and try to understand it all.   WOW!  I also need to do  a lot of verification.  

The first record looks like...
Truck                                          Street                    Timein                           Timeout                       Minute
TRUCK #1     JACKSON CO.      JOHNS PEAK RD   6/5/2011 8:15:33 PM    6/6/2011 2:22:43 AM  367

It's the only record that shows Timein and Timeout as 2 different dates.  And the remaining record result only shows the date 6/6/2011.  That might be because of the "Edit as Text" I'm using in Visual Studio.  Maybe it doesn't show the full results.  I wasn't able to use query Designer (which is what I'm use to) because I get the error.  "The OVER SQL construct tor statement is not supported."  

I'll post related questions as needed but thank you again for all this code and your expertise!
0
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

Author Closing Comment

by:BobRosas
ID: 36551049
Thank you again for you code and your help!
0
 
LVL 42

Expert Comment

by:dqmq
ID: 36551875
The CTE sequences each record chronologically within user
The sequence number is used to identify arrival rows and departure rows: those not having an adjacent record for the same user and street.
Arrival rows and departure rows are then sequenced chronologically and joined based on matching sequence and userid.  

  note: to be safe that join should probably also include street:
         on  arr.userid=dep.userid
         and arr.aseq = dep.dseq
        and arr.street = dep.street

Finally, the joined arrival-departure pairs are used to compute the elapsed time and further joined to the users table to filter by truck.

0
 

Author Comment

by:BobRosas
ID: 36551949
GREAT!  I will add that code to what you gave me!  Thank you again for the additional help!  
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

757 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

20 Experts available now in Live!

Get 1:1 Help Now