Solved

Datediff in SQL between 1st and last record

Posted on 2011-09-13
7
522 Views
Last Modified: 2012-05-12
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

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
7 Comments
 
LVL 12

Expert Comment

by:Saurabh Bhadauria
ID: 36531618
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

0
 

Author Comment

by:BobRosas
ID: 36531812
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?
0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 500 total points
ID: 36533437
Multiple issues here:
-- If you group by UserID, Street,Timetag ==> MIN(Timetag) and MAX(Timetag) will just be Timetag, because you grouped on that column. I suspect previous Expert meant:
Select UserID, Street
     , DateDiff(dd, 0, Timetag) as Timetag
     , min(Timetag) as Timein, max(Timetag) as Timeout        
from dbo.GPSData t1
group by UserID, Street, DateDiff(dd, 0, Timetag)

Open in new window

-- Then you have to make sure to pass Timetag upward through all the derived tables. I think there is an extra one involved.
SELECT T.[Timetag] as [Date], U.Name, T.Street
     , CONVERT(CHAR(8), DATEADD(mi, T.ts_mins, 0), 108) AS HrsMin
FROM (
   select UserID, Street
        , datediff(dd, 0, Timetag) as Timetag
        , datediff(mi, min(Timetag), max(Timetag)) as ts_mins        
   from dbo.GPSData
   group by UserID, Street, datediff(dd, 0, Timetag)
) T
INNER JOIN dbo.Users U ON (T.UserID = U.UserID)
;

Open in new window

0
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 
LVL 59

Assisted Solution

by:Kevin Cross
Kevin Cross earned 500 total points
ID: 36533467
If you wanted seconds, then change datediff(mi, min(...), max(...)) back to datediff(ss, min(...), max(...)) as I see that was in YOUR original query. I just read Hours and Minutes to mean you didn't need seconds precision. It doesn't hurt and you will find that the 108 format code shows seconds too, so will work perfectly if you want that level of granularity.

Additionally, I think I left the date field as a number. In the outer query or the select of the derived table aliased T, you will want to do this: dateadd(dd, {timetag column or datediff}, 0) or simply convert(datetime, {timetag column or datediff}) or cast({timetag column or datediff} as datetime) to get back to a date.

You can even take that further to:
convert(char(8), convert(datetime, {timetag column or datediff}), 112) ==> yyyymmdd
Which appears to be your goal in the outer query.
0
 

Author Comment

by:BobRosas
ID: 36539217
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!
0
 
LVL 59

Expert Comment

by:Kevin Cross
ID: 36539222
You are most welcome!
Best regards and happy coding,

Kevin
0
 

Author Closing Comment

by:BobRosas
ID: 36539224
Thanks again for all your help
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

735 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