Solved

Min/Max datediff each time Street field changes

Posted on 2011-09-16
6
657 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
[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
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
The Ultimate Checklist to Optimize Your Website

Websites are getting bigger and complicated by the day. Video, images, custom fonts are all great for showcasing your product/service. But the price to pay in terms of reduced page load times and ultimately, decreased sales, can lead to some difficult decisions about what to cut.

 

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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
What if you have to shut down the entire Citrix infrastructure for hardware maintenance, software upgrades or "the unknown"? I developed this plan for "the unknown" and hope that it helps you as well. This article explains how to properly shut down …
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
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.

717 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