• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

Min/Max datediff each time Street field changes

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
BobRosas
Asked:
BobRosas
  • 3
  • 3
1 Solution
 
dqmqCommented:
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
 
dqmqCommented:
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
 
BobRosasAuthor Commented:
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
Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

 
BobRosasAuthor Commented:
Thank you again for you code and your help!
0
 
dqmqCommented:
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
 
BobRosasAuthor Commented:
GREAT!  I will add that code to what you gave me!  Thank you again for the additional help!  
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now