quote:

---

Originally posted by krish.vak

i have 2 tables invoice , repcomm which has the following structure

invoice
invno invdate rep gtotal
001 06/01/2011 kumar 5000
002 08/01/2011 raj 2500
003 05/05/2011 kumar 3000


repcom
rep rdate commPer
kumar 01/01/2011 10
kumar 01/05/2011 15
raj 01/01/2011 5
raj 01/05/2011 10



the "repcom" table contains the percentage of commission which will be raised or lowered during the representative's service.

while calculating the rep commission for a particular invoice i need to use a query that fetch the "commPer" from the table "repcom" with respect to the invdate in "invoice" table



krish

---

SELECT i.*,r.commPer
FROM invoice i
CROSS APPLY (SELECT TOP 1 commPer
             FROM repcom
             WHERE rep = i.rep
             AND rdate<=invdate
             ORDER BY rdate DESC)r