This one is now closed, many thanks for posting Garadin.

kind regards,

Phil.

Update AGAIN. OK now amended further.....

(Select SUM(SalesOrderItems.CurItemValue) - (ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') <> 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutStandingBalance,

This appears to return what I am after.....will need...

I have been having a play. I updated line:

(Select SUM(SalesOrderItems.CurItemValue) - SUM(ProgressPayments.InvoiceValue) From SalesOrderItems where ISNULL(salesorderitems.freetextitem,'') = 'Progress Payment' AND SalesOrderItems.SalesOrder = SalesOrders.SalesOrder) AS OutStandingBalance,

Nearly but not quite (well I...

Thanks for that. It returned 0 rows so I guess I am safe to remove (as I thought). Best to check.

Many Thanks,

Phil.

Thanks for posting guys. I will get the desired output via reports.

Phil.

Madhu not sure if you are looking at the most recent query?

Here it is:

SELECT

case when rn = 1 then CustomerName else...

Hi Dugi. I really appreciate the efforts to all of those who have posted.

I am unable to do as requested as I would be in breach of the contractual...

Guys I will test now, I have man flu....:)

Watch this space......6.42am someone pin a medal on me.....

Many Thanks,

Phil.

Update:

row_number() over(

PARTITION BY CUSTOMER,SalesOrders.SalesOrderId

ORDER BY CUSTOMER,SalesOrders.SalesOrderId

) AS rn

This returned error:

Msg 209,...

Jacob that is perfect, what did you change?

Finally was my approach valid in constructing a query to use in a report?

Many Thanks,

Phil.

Update:

Jacob in answer to my own question my approach...

Here is the amended code that returns error:

Msg 8120, Level 16, State 1, Line 1

Column 'r.rn' is invalid in the select list because it is not contained in either an...

Hi guys, yes it was spilt correctly 🙂

New error after changing InvoiceValue to PaymentMade in the outer query

Msg 8120, Level 16, State 1, Line 1

Column 'r.rn' is invalid in the...

Hi Jacob, I appreciate it is difficult working blind. I made the change but now receive:

Many Thanks,

Phil.

Hi Jacob, many thanks for taking time to reply.

I have changed code but now receive different error:

Msg 4104, Level 16, State 1, Line 1

The multi-part identifier "Payments.PaymentDate" could not be...

Thanks Glen.

Regards,

Phil.

Hi Glen, yes I checked alerts.

Thanks,

Phil.