Here's my query so far:
-----------------------------------
select o.custid, oi.prodcode, oi.status, oi.orderitemid, pay.pmtid, pay.amount as paid, oi.amount as owed

from csm.dbo.orders o
inner join csm.dbo.orderitems oi on oi.orderid = o.orderid and oi.status in ('01', '06', '05')
inner join csm.dbo.trxdetail trx on trx.orderitemid = oi.orderitemid and trx.pmtid is not null
inner join csm.dbo.payments pay on trx.pmtid = pay.pmtid
where ( SUBSTRING(oi.prodcode, 1, 3) = 'GDE'
or SUBSTRING(oi.prodcode, 1, 3) = 'TGD'
or SUBSTRING(oi.prodcode, 1, 3) = 'CUS'
or SUBSTRING(oi.prodcode, 1, 3) = 'WEB'
or oi.prodcode = 'ABCD')
and pay.amount < oi.amount
order by prodcode, custid
-------------------------------------

My question is how to write query to include a sum of all payments for each orderitem i.e. sum(pay.amount). Also, how to calculate the difference between sum(paid) and owed amount?

Any help is appreciated. Thanks.

order by prodcode, custid

It seems to me that you're question is answered here: Cumulative Field
Try to solve it with correlated subqueries in the SELECT clause.

Regards

No that thread doesnt replicate my problem. Interesting reading though and I'm sure I'll find a use for that someday. In my case I'm not trying to calculate a cumulative or running total. I just have the potential for multiple payment records associated with a single orderitem. That order item contains the Price. So relating those tables and getting a sum of all payments with just one orderitem price was not working for me.

So I can get:

Product Orderitem# Price Payment# PayAmount Balance
ABC 12345 195.00 99988 100.00 95.00 ??
ABC 12345 195.00 99970 15.00 180.00 ??
DEF 15555 50.00 90000 50.00 0.00

I want one record per Product/Orderitem

Product Orderitem# Price PayAmount Balance
ABC 12345 195.00 115.00 80.00
DEF 15555 50.00 50.00 0.00

I would think a grouping and sum would work for you, unless I'm misunderstanding your data.

select o.custid, oi.prodcode, oi.status, oi.orderitemid, pay.pmtid, sum(pay.amount) as paid, oi.amount as owed, (oi.amount-sum(pay.amount)) as difference
from csm.dbo.orders o
inner join csm.dbo.orderitems oi on oi.orderid = o.orderid and oi.status in ('01', '06', '05')
inner join csm.dbo.trxdetail trx on trx.orderitemid = oi.orderitemid and trx.pmtid is not null
inner join csm.dbo.payments pay on trx.pmtid = pay.pmtid
where ( SUBSTRING(oi.prodcode, 1, 3) = 'GDE'
or SUBSTRING(oi.prodcode, 1, 3) = 'TGD'
or SUBSTRING(oi.prodcode, 1, 3) = 'CUS'
or SUBSTRING(oi.prodcode, 1, 3) = 'WEB'
or oi.prodcode = 'ABCD')
group by o.custid, oi.prodcode, oi.status, oi.orderitemid, pay.pmtid, oi.amount as owed
having sum(pay.amount) < oi.amount
order by prodcode, custid

Thanks. I was having trouble figuring out how to use the having clause.

But that's still not quite right because I get records like this:

Custid Prod Status OrderID PmtID Paid Owed Balance
796774 GDEBR 05 1046008 7645725 19.95 373.00 353.05
796774 GDEBR 05 1046008 7645724 195.95 373.00 177.05
796774 GDEBR 05 1046008 9029586 177.05 373.00 195.95

I need one record per Custid with an accurate balance. This customer shows up as owing when in fact the sum of his payments means he has a small credit.

Guess What.. It worked. I realized as I posted the last time that I was getting multiple records per customer because I was asking for PaymentID. Duh! Take the payment ID out of the equation and it works fine.

Thanks!!!

Okay, I see a little more clearly what your data is. Remove the PmtID from the group and select, and they you should be fine. Inclusion of the PmtID is causing each distinct payment record to be seperated into its own group, and you want them cumulated per customer, product and order item id. Depending on what 'status' means, you may need to remove that from your grouping too.

select o.custid, oi.prodcode, oi.status, oi.orderitemid, sum(pay.amount) as paid, oi.amount as owed, (oi.amount-sum(pay.amount)) as difference
from csm.dbo.orders o
inner join csm.dbo.orderitems oi on oi.orderid = o.orderid and oi.status in ('01', '06', '05')
inner join csm.dbo.trxdetail trx on trx.orderitemid = oi.orderitemid and trx.pmtid is not null
inner join csm.dbo.payments pay on trx.pmtid = pay.pmtid
where ( SUBSTRING(oi.prodcode, 1, 3) = 'GDE'
or SUBSTRING(oi.prodcode, 1, 3) = 'TGD'
or SUBSTRING(oi.prodcode, 1, 3) = 'CUS'
or SUBSTRING(oi.prodcode, 1, 3) = 'WEB'
or oi.prodcode = 'ABCD')
group by o.custid, oi.prodcode, oi.status, oi.orderitemid, oi.amount as owed
having sum(pay.amount) < oi.amount
order by prodcode, custid