Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Update by rank using subquery Expand / Collapse
Author
Message
Posted Friday, October 25, 2013 4:08 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 4:39 AM
Points: 5, Visits: 74
Hi folks

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 -

UPDATE @Temp_Allocations
SET Payment =
(SELECT Gross
FROM [Stage].[S_XLS_Allocation] alloc
AND
(Gross = OutstandingAmount
OR
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
Phil
Post #1508394
Posted Friday, October 25, 2013 6:47 AM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Yesterday @ 11:56 AM
Points: 620, Visits: 2,123
Can you post table definitions and some sample data?
Post #1508440
Posted Friday, October 25, 2013 6:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, April 30, 2014 4:39 AM
Points: 5, Visits: 74
I think I've sorted now thank you - Just finalising and I'll post details but I'm putting the payments and their row numbers in a second temp table and then using an update to marry up the temp invoices and temp payments
Post #1508443
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse