How to select the last price based on a date

Question

How to select the last price based on a date

ericverleg

Old Hand

Points: 300
More actions
March 12, 2009 at 10:27 am

#135496

I have the following problem how can a create a select which will combine the two tables. I only can work with views on the database and not with the actual tables.
view Tabel Contract
Nr Pricedate PriceMethode
1 01-12-2008 A
2 10-01-2009 A
2 15-03-2009 A
1 20-01-2009 B
3 01-01-2009 C
3 15-07-2009 C
view Table Price
PriceMethode Date Price
A Null 100
A 01-01-2009 110
A 01-02-2009 120
B Null 200
C 01-06-2009 500
Result should be:
Nr Pricedate PriceMethode Price
1 01-12-2008 A 100
2 10-01-2009 A 110
2 15-03-2009 A 120
1 20-01-2009 B 200
3 01-01-2009 C NULL
3 15-07-2009 C 500
I hope that somebody can give the right directions.
Thanks in advance.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

JKSQL SSCrazy Eights Points: 8481 More actions · Answer 1

view Tabel Contract
Nr Pricedate PriceMethode
1 01-12-2008 A
2 10-01-2009 A
2 15-03-2009 A
1 20-01-2009 B
3 01-01-2009 C
3 15-07-2009 C
view Table Price
PriceMethode Date Price
A Null 100
A 01-01-2009 110
A 01-02-2009 120
B Null 200
C 01-06-2009 500
Result should be:
Nr Pricedate PriceMethode Price
1 01-12-2008 A 100
2 10-01-2009 A 110
2 15-03-2009 A 120
1 20-01-2009 B 200
3 01-01-2009 C NULL
3 15-07-2009 C 500

I think you are just asking for to join the views

SELECT NR, PriceDate, PriceMethode, Price

FROM [view Tabel Contract] VTC

INNER JOIN [view Table Price] VTP ON VTC.PriceMethode = VTP.PriceMethode

Mohit K. Gupta SSChampion Points: 12130 More actions · Answer 2

[font="Courier New"]DECLARE @T AS TABLE (Col1 INT, Col2 DATETIME, Col3 CHAR(1))

DECLARE @P AS TABLE (Col1 CHAR(1), Col2 DATETIME, Col3 INT)

INSERT INTO @T

SELECT 1,'12/1/2008','A' UNION ALL

SELECT 2,'1/10/2009','A' UNION ALL

SELECT 2,'3/15/2009','A' UNION ALL

SELECT 1,'1/20/2009','B' UNION ALL

SELECT 3,'1/1/2009','C' UNION ALL

SELECT 3,'7/15/2009','C'

INSERT INTO @P

SELECT 'A',NULL,100 UNION ALL

SELECT 'A','1/1/2009',110 UNION ALL

SELECT 'A','2/1/2009',120 UNION ALL

SELECT 'B',NULL,200 UNION ALL

SELECT 'C','6/1/2009',500

SELECT T.*,

(SELECT TOP 1 Col3

FROM @P AS P

WHERE (P.Col1 = T.Col3

AND P.Col2 <= T.Col2)

OR (P.Col1 = T.Col3

AND P.Col2 IS NULL)

ORDER BY Col2 DESC) AS Price

FROM @T AS T[/font]

Maybe?

[font="Arial"]---

Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE

* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]

How to ask for help .. Read Best Practices here[/url].