June 12, 2009 at 12:34 pm
I'm having problems writing a query that's supposed to return what the next value is in the sequence. I've probably made a stupidly simple error but I can't seem to spot it and I've been banging my head on this for 2 hours without any luck...
What I have is a table called track with three relevant columns: start_date, order_id and process_id. The combination of start_date and process_id will point to a unique value, whereas order_id can have multiple entries. What I need to do is write a query that gives me, for a given process_id, the start_dates, the order_id and also the next order_id chronologically. I can write a query that comes back with a start_date, the associated order_id and the next start_date in sequence:
SELECT "track"."start_date", "track"."order_id",
(SELECT MIN("datefind"."start_date") FROM "flute_data"."dbo"."track" "datefind" WHERE "datefind"."start_date">"track"."start_date" AND "datefind"."process_id"=1)
FROM "flute_data"."dbo"."track" "track"
WHERE "track"."process_id"=1 AND "track"."start_date">={ts '2009-05-01 19:36:16'}
ORDER BY "track"."start_date"
That works and comes back with this (a snippet of a much longer report):
start_dateorder_idNextTime
05/04/2009 7:05:16 AM82160305/04/2009 8:20:08 AM
05/04/2009 8:20:08 AM82120405/04/2009 9:15:09 AM
05/04/2009 9:15:09 AM82119805/04/2009 9:49:54 AM
05/04/2009 9:49:54 AM82149705/04/2009 10:32:49 AM
That's pretty close, and I thought the next step would be using the NextTime value plus the process_id to get the associated order_id so I have this for a table:
start_dateorder_idNextOrder
05/04/2009 7:05:16 AM821603821204
05/04/2009 8:20:08 AM821204821198
05/04/2009 9:15:09 AM821198821497
05/04/2009 9:49:54 AM821497821649
This is the query as I changed it to:
SELECT "track"."start_date", "track"."order_id",
(SELECT "look"."order_id" FROM FROM "flute_data"."dbo"."track" "look" WHERE "look"."process_id"=1 AND "look"."start_date"=
(SELECT MIN("datefind"."start_date") FROM "flute_data"."dbo"."track" "datefind" WHERE "datefind"."start_date">"track"."start_date" AND "datefind"."process_id"=1)) AS NextOrder
FROM "flute_data"."dbo"."track" "track"
WHERE "track"."process_id"=1 AND "track"."start_date">={ts '2009-05-01 19:36:16'}
ORDER BY "track"."start_date"
But instead I'm getting an "Incorrect Syntax near the keyword 'FROM'" error message, and I can't spot what I did wrong...
June 12, 2009 at 2:27 pm
you have FROM FROM on line 2.
sometimes you just need someone else to look at the code 😉
If its still not giving the results you want, post the table definition and some sample data (just a few lines) - and expected results from the sample.
June 12, 2009 at 2:28 pm
Duh... that'll teach me about trying to code SQL on a Friday afternoon. I had a FROM statement in there twice...
Nevermind folks... nothing to see! 😀
June 12, 2009 at 2:34 pm
You can use a CTE to get a row-number over your order-id:
DECLARE @t TABLE (OrderId INT, StartDate DATETIME)
INSERT INTO @t
SELECT 1000, '2009-06-01'
UNION ALL SELECT 1001, '2009-06-02'
UNION ALL SELECT 1200, '2009-06-03'
; WITH cte (OrderId, StartDate, RowNum) AS
(
SELECT
OrderId,
StartDate,
ROW_NUMBER() OVER (ORDER BY OrderId)
FROM @t
)
SELECT
t1.OrderId,
t1.StartDate,
t2.OrderId Next_Order
FROM cte t1
LEFT JOIN cte t2 ON t1.RowNum = t2.RowNum - 1
Flo
June 12, 2009 at 2:35 pm
Tom Brown (6/12/2009)
you have FROM FROM on line 2.sometimes you just need someone else to look at the code 😉
LOL... that or a second look after an hour's break and some Tylenol for the headache caused by banging my head against the keys. 🙂
June 12, 2009 at 2:41 pm
Florian Reischl (6/12/2009)
You can use a CTE to get a row-number over your order-id:Flo
Hmm... never knew this feature existed... time to start swatting up on the Common Table Expressions.
Thank you for the tip!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply