Solved

Min/Max datediff each time Street field changes

Posted on 2011-09-16
6
651 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

Question has a verified solution.

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

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

863 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

19 Experts available now in Live!

Get 1:1 Help Now