All Forums Database
sujithwarrier 1 post Joined 05/13
23 Jul 2014
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:
krishaneesh 140 posts Joined 04/13
28 Jul 2014

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.

You must sign in to leave a comment.