Solved

Min/Max datediff each time Street field changes

Posted on 2011-09-16
6
656 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 

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

How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

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.
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…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.

739 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