Query help in join

Transact-SQL
1

Hi,

there are two tables Sales and Promotion, Item can sold on different days the corresponding promotion createdkey <= to item created key only should be joined. the before or on the date of sales only should be joined for that sales

Salesid---Itemid----Createdkey---RevRecord------CurrRecord
1000-----123----------100-----------N-----------------N
1200-----123-----------101----------Y------------------N
1300------123----------101---------N------------------Y

promoid----itemid-------createdkey
201-----------123----------100
202------------123----------101
203-------------123----------101

when above two table joined I want the output query, join the promotion createdkey same as sales and less than. Also in case of Revrecord is 'Y' only before record should be joined.

Salesid---Itemid----Createdkey---RevRecord------CurrRecord-----Promoid
1000-----123----------100-----------N-----------------N--------------------201
1200-----123-----------101----------Y------------------N-------------------201
1300------123----------101---------N------------------Y--------------------201
1300------123----------101---------N------------------Y--------------------202
1300------123----------101---------N------------------Y--------------------203

Any pointer will be helpful
thanks

2 
SELECT S.SalesId, S.ItemId, S.CreatedKey, S.RevRecord, S.CurrRecord, P.PromoId
FROM Sales S
	JOIN Promotions P
		ON S.ItemId = P.ItemId
WHERE S.RevRecord <> 'Y' AND S.CreatedKey = P.CreatedKey
	OR S.CreatedKey > P.CreatedKey;
3

hi

thanks for the solution Ifor

but i have a doubt
i would expect to see 2 records for 1200 based on <=

please help me understand

image

4

True but I think the OP said to use <, not <=, if RevRecord = 'Y'. I may have confused things as I started with Sales, not Promotions, so am using > in the WHERE clause.

5

thanks Ifor and harishgg1 for your time. it worked out great!!