Vendor's payments

 4 Replies
 2 Subscribed to this topic
 43 Subscribed to this forum
Sort:
Author
Messages
Chesca
Veteran Member
Posts: 490
Veteran Member
    Could anybody help me out with writing an SQL statement to get total check amount for each vendor in a given period? Here is my select statement but I still need to include vendor name, tax ID and address:

    select company,vendor, sum(check_chk_amt)
    from
    lawson.appayment
    where check_date BETWEEN to_date('1/1/15','MM/DD/YY') and to_date('12/31/15','MM/DD/YY')
    Group by company,vendor
    HAVING SUM(bank_chk_amt) >= 600.00
    order by company,vendor;
    Kat V
    Veteran Member
    Posts: 1020
    Veteran Member
      I have a similar query - but I've got it sum(TRAN_CHK_AMT) not BANK. No clue why - it's a leftover from my predecessors.
      Chesca
      Veteran Member
      Posts: 490
      Veteran Member
        I was not able to get the vendor's address because we have multiple address per vendor. So I ended up joining the vendor and payment table only in order to get vendor, tax ID, income code and total check amount per vendor.
        JonA
        Veteran Member
        Posts: 1163
        Veteran Member
          How about joining APPAYMENT with APVENADDR on APPAYMENT.REMIT_TO_CODE = APVENADDR.LOCATION_CODE to get the address information for the payment?
          Jon Athey - Sr. Supply Chain Analyst - Materials Management - MyMichigan Health
          Chesca
          Veteran Member
          Posts: 490
          Veteran Member
            I am able to get the vendor's address now. Thanks all!