Need help on a case scenario

Database

Need help on a case scenario

Hi All,

I am bit stuck with a case for getting the exact sql. The scenario is we have to pick the price from Table1 with respect to Table2 and create an output. The price should be picked from the latest date and if we have duplicate price for same date then exact match must be considered first.

Price might be given at ID and Country level or Country level alone. First preference goes for ID and Country level , but the Date from price must be latest with Qty pick.

Table1 
DATE ID COUNTRY PRICE
2014-07-11 1 US 10
2014-07-12 N\A US 50
2014-07-21 2 US 20
2014-07-21 N\A US 30

Table2
DATE ID COUNTRY QTY
2014-07-20 1 US 20
2014-07-25 2 US 40

Result
DATE ID COUNTRY QTY*PRICE
2014-07-20 1 US 20*50
2014-07-25 2 US 40*20
Tags (1)
1 REPLY

Re: Need help on a case scenario

in Table1 how will you be able to identify that the 2nd row pertains to ID1 and 4th row to ID 2 or other ID. if you have the ID in 2nd row to be 1, then what you can do is a row number over date partition by id =1 and then do a direct qty*price.