Link to home
Start Free TrialLog in
Avatar of BobRosas
BobRosas

asked on

Datediff in SQL between 1st and last record

I'm using Report Services in Visual Studio and I have the attached SQL code.  The database table (GPSData) has data like...
RecID                           UserID      Timetag                            Street      
74901684557      104639      2011-06-05 21:16:43.000      JOHNS PEAK RD      
74901684558      104639      2011-06-05 21:24:44.000      JOHNS PEAK RD      
74901808346      104639      2011-06-05 21:28:44.000      MAIN STREET      
74901808347      104639      2011-06-05 21:36:45.000      MAIN STREET      
74901808348      104639      2011-06-05 21:48:47.000      MAIN STREET      
74901808349      104639      2011-06-05 21:56:49.000      WASHINGTON AVE      
74901914096      104639      2011-06-05 21:59:55.000      WASHINGTON AVE      

What I want is for any given Day, Street and Name (a joined table) show how long (hours and minuts) between the first and last record.
In my code I'm using Top1 to get 1st record and Max to get last record and then compare them.  I get the error...
An expression of non-boolean type specified in a context where a condition is expected, near 'order'.
select convert(datetime,convert(varchar(8),[TimeTag],112)) as Date, Name, Street,
       convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+ 
       substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin
from
(
 SELECT  u.Name, Street,  datediff(ss,t1.Timein,t2.Timeout) as ts_seconds
 FROM
 (
   Select UserID, Street,Timetag, 
         ((Select Top(1) Timetag as Timein
                   from dbo.GPSData t2 
                   where t2.UserID = t1.UserID 
          and convert(varchar(8),t2.Timetag) = convert(varchar(8),t1.Timetag)    -- must be same day
          and MAX (Timetag)
           order by t2.Timetag asc, RecID asc),0) as Timeout  --get error here
          from dbo.GPSData t1
  ) times
 
  INNER JOIN dbo.Users U ON (times.UserID = U.UserID)
 
  GROUP BY   u.Name,  times.Street,ts_Day, TS_Day2
) daily
 
GROUP BY name, street, ts_Day,TS_Day2, ts_seconds
 
ORDER BY name, street, ts_Day, TS_Day2,  ts_seconds

Open in new window

Avatar of Saurabh Bhadauria
Saurabh Bhadauria
Flag of India image

Might be you can write your code like this...


select convert(datetime,convert(varchar(8),[TimeTag],112)) as Date, Name, Street,
       convert(varchar,datediff(hh,0,(dateadd(ss,sum(ts_seconds) ,0))))+ 
       substring((convert(varchar(8),(dateadd(ss,sum(ts_seconds) ,0)),114)),3,3) as HrsMin
from
(SELECT  u.Name, Street,  datediff(ss,t1.Timein,t2.Timeout) as ts_seconds
 FROM
 (
   Select UserID, Street,Timetag, min(Timetag) as Timein,max(Timetag) as Timeout        
          from dbo.GPSData t1
  group by UserID, Street,Timetag
  ) times INNER JOIN dbo.Users U ON (times.UserID = U.UserID)
)Daily

Open in new window

Avatar of BobRosas
BobRosas

ASKER

Thank you for your fast and concise code.  I'm hoping it will work but now I have an error...
     Invalid column name "TimeTag" on line 1.
So I have not been able to compile and run the code to see if it will work.  I've tried changing timetag to TimeIn and t1.TimeIn etc as well as add to my group but so far I haven't got it to run yet.  Any thoughts?
ASKER CERTIFIED SOLUTION
Avatar of Kevin Cross
Kevin Cross
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Thank you so much!  Every line of your code worked as well as your added code in your last comment.  I apologize for taking so long to get back to you.  Thanks again!
You are most welcome!
Best regards and happy coding,

Kevin
Thanks again for all your help