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

• Next in forum: Re: INNER JOIN/Index Threshold?
• Prev in forum: Re: Link Server doesn't work except through query analyzer: MSSQL freezing / timing out
• Thread view: Re: How to find a lower date

[Reply to this message]