CODEWITHSUNDEEP
sqlsql-serversql-server-2008group-by
PM 77-1
PM 77-1

Reputation: 13334

SQL Query for time intervals. Syntax errors.

This is the case where a seemingly simple exercise turned to be nothing but. I couldn't even get my syntax straight.

Readings table contains coordinates and time readings for multiple users. All I want at this time to organize it as consecutive time intervals for each user.

Data:

CREATE TABLE [Readings] (
user_id varchar(10),
reading_time int, 
x decimal(10,2),
y decimal(10,2) );


INSERT INTO Readings VALUES  
('u1', 60, 345, 400),
('u1', 100, 560,300), 
('u2', 35, 1024, 250), 
('u1', 90, 450, 450), 
('u3', 150, 600, 100), 
('u3', 100, 500, 125);

My erroneous code:

SELECT r.user_id, r.reading_time start_time, rm.reading_time end_time, 
       (CONVERT(varchar, r.x)+' ; '+CONVERT(varchar, r.y)) start_point, 
       (CONVERT(varchar, rm.x)+' ; '+CONVERT(varchar, rm.y)) end_point 
FROM Readings r 
JOIN (SELECT TOP 1 r2.user_id, r2.reading_time, r2.x, r2.y
      FROM Readings r2
      WHERE r2.user_id=r.user_id and 
            r2.reading_time < r.reading_time
      ORDER BY r2.reading time desc) rm 
ON r.user_id=rm.user_id
ORDER BY 1,2 desc;

Can you please point out my syntax error(s)?

Upvotes: 1

Views: 448

Answers (2)

Taryn
Taryn

Reputation: 247730

Well part of your problem is you are referencing a table alias inside of a subquery, you can only do that if you are using an APPLY:

SELECT r.user_id, 
  r.reading_time start_time, 
  rm.reading_time end_time, 
  (CONVERT(varchar(10), r.x)+' ; '+CONVERT(varchar(10), r.y)) start_point, 
  (CONVERT(varchar(10), rm.x)+' ; '+CONVERT(varchar(10), rm.y)) end_point 
FROM Readings r 
CROSS APPLY
(
  SELECT TOP 1 r2.user_id, r2.reading_time, r2.x, r2.y
  FROM Readings r2
  WHERE r2.user_id=r.user_id and 
      r2.reading_time < r.reading_time
  ORDER BY r2.reading_time desc
) rm 
ORDER BY 1,2 desc;

See SQL Fiddle with Demo

Upvotes: 2

Lamak
Lamak

Reputation: 70658

You can't use the JOIN like that, you need to use either CROSS APPLY or OUTER APPLY (assuming SQL Server). Also, please add length to varchars. Now, try this:

SELECT r.user_id, r.reading_time start_time, rm.reading_time end_time, 
       (CONVERT(varchar(10), r.x)+' ; '+CONVERT(varchar(10), r.y)) start_point, 
       (CONVERT(varchar(10), rm.x)+' ; '+CONVERT(varchar(10), rm.y)) end_point 
FROM Readings r 
OUTER APPLY (SELECT TOP 1 r2.user_id, r2.reading_time, r2.x, r2.y
             FROM Readings r2
             WHERE r2.user_id=r.user_id and 
                   r2.reading_time < r.reading_time
             ORDER BY r2.reading_time desc) rm 
ORDER BY 1,2 desc;

Here is a demo for you to try.

Upvotes: 3

Related Questions