February 5, 2009 at 4:39 pm
Hi All,
I have a look up table
IDTimeStampClsoe
88825/01/20091
88826/01/20090
90625/01/20091
90626/01/20091.5
Real Table
IDTimeStampClsoe
88825/01/20091
90625/01/20091
90626/01/20091.5
When the close price from the look up table is zero it has to take the previous day's close price
in the ex 888 26/01/2009 close price is 0 so it should have the value as 888 26/01/2009 1
Expected result
IDTimeStampClsoe
88825/01/20091
88826/01/20091
90625/01/20091
90626/01/20091.5
Can some one please help me with the query.
Thanks
February 6, 2009 at 6:41 am
This works. There may be a more efficient way, but now you will be on the front page of "Active Threads" again too.
SET DATEFORMAT dmy
DECLARE @lookup TABLE
(
ID INT,
[TIMESTAMP] DATETIME,
[CLOSE] DECIMAL(8, 2)
)
INSERT INTO
@lookup
SELECT
888,
'24/01/2009',
2
UNION ALL
SELECT
888,
'25/01/2009',
1
UNION ALL
SELECT
888,
'26/01/2009',
0
UNION ALL
SELECT
906,
'25/01/2009',
1
UNION ALL
SELECT
906,
'26/01/2009',
1.5 ;
WITH cteData AS
(
SELECT
-- this makes sure I get the max as I basically have a cross join
ROW_NUMBER() OVER (PARTITION BY L.ID, L2.[timestamp] ORDER BY L2.[timestamp] desc) AS row_id,
L.id,
L.[TIMESTAMP],
CASE WHEN L.[CLOSE] = 0 THEN L2.[CLOSE]
ELSE L.[CLOSE]
END AS [close]
FROM
@lookup L LEFT JOIN
@lookup L2
ON L.id = L2.id AND
L.[TIMESTAMP] > L2.[timestamp]
)
SELECT
*
FROM
cteData
WHERE
row_id = 1
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
February 8, 2009 at 12:35 pm
[font="Verdana"]I hadn't thought of using timestamp that way. Clever![/font]
February 8, 2009 at 2:06 pm
Thanks Bruce. Means a lot coming from you. I've definitely noticed the quality of your responses. You are definitely an asset to the community. Glad you are on board and being active.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply