|
Posted by Grey on 01/07/07 21:16
OK, now I have solved the problem by an approach I found at some other post
presented by Joe Celko:
CREATE TABLE Foobar
(id INTEGER NOT NULL PRIMARY KEY,
name CHAR(10) NOT NULL);
INSERT INTO Foobar VALUES (12, 'Johnson');
INSERT INTO Foobar VALUES (57, 'Nelson');
INSERT INTO Foobar VALUES (11, 'Roberts');
INSERT INTO Foobar VALUES (68, 'Smith');
SELECT F1.id, F1.name,
(SELECT MAX (id)
FROM Foobar AS F2
WHERE F2.id < F1.id) AS prev_id,
(SELECT MIN(id)
FROM Foobar AS F3
WHERE F3.id > F1.id) AS next_id
FROM Foobar AS F1;
These subquery expressions are the LUB (least upper
bound) and the GLB (greatest lower bound).
Thats finds the lower records. Than a simply comparation like this:
SELECT CASE WHEN f1.id< (select id from foobar WHERE id =
dbo.get_prev_id(F1.id)) THEN date+1 ELSE date END AS date_past_midnight from
foobar as f1
Function get_prev_id is like:
SELECT (SELECT MAX (id) FROM foobar AS F2 WHERE F2.id < F1.id) AS prev_id
FROM foobar AS F1
where F1.id = @id <--- (argument passed to function)
Thanks for all help
GREY
[Back to original message]
|