Hope you can help, and I've put this in the right section.
My predicament is as follows -
I have a table of outstanding invoices which I've ranked in order of date, all fine there.
I have another table containing payments and I want to assign each individual payment to an individual invoice using an UPDATE statement.
I was hoping to join on the ranks of each table and do something like following -
SET Payment =
FROM [Stage].[S_XLS_Allocation] alloc
(Gross = OutstandingAmount
OutstandingAmount - Gross BETWEEN 0 AND 2)
AND RANK() OVER (PARTITION BY Asset,Serial ORDER BY PaymentDate) = RankSequence
AND IndividualItemID = alloc.Asset
But this gives me the following error -
Windowed functions can only appear in the SELECT or ORDER BY clauses
Can anyone suggest another way of doing this in a single hit UPDATE without having to loop through the table variable?
Thanks in advance