Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    4

    Unanswered: Need help with query please

    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

  2. #2
    Join Date
    Aug 2006
    Location
    The Netherlands
    Posts
    248
    It seems to me that you're question is answered here: http://www.dbforums.com/showthread.php?t=1606682
    Try to solve it with correlated subqueries in the SELECT clause.

    Regards

  3. #3
    Join Date
    Nov 2004
    Posts
    4
    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

  4. #4
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    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

  5. #5
    Join Date
    Nov 2004
    Posts
    4
    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.

  6. #6
    Join Date
    Nov 2004
    Posts
    4

    Smile

    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!!!

  7. #7
    Join Date
    Jan 2003
    Location
    Pittsburgh, PA
    Posts
    86
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •