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'.
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
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?
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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
Best regards and happy coding,
Kevin
ASKER
Thanks again for all your help
Open in new window