Wednesday, October 5, 2011

Calculating a Customer’s Available Credit

I recently had the challenge to create a SQL query that would calculate a customer’s credit outstanding: that is, the customer’s credit limit less outstanding sales (unposted sales plus unpaid posted sales less unapplied credits).  Enjoy!

SELECT Cust.CUSTNMBR CustomerID, Cust.CUSTNAME CustomerName,
       CASE Cust.CRLMTTYP
              WHEN 0 THEN 'No Credit'
              WHEN 1 THEN 'Unlimited Credit'
              WHEN 2 THEN 'Credit Limit Amount'
              ELSE '(error)' END as CreditLimitType,
       Cust.CRLMTAMT CreditLimit,
       (CustSum.CUSTBLNC + CustSum.UNPSTDSA
+ CustSum.UNPSTOSA + CustSum.ONORDAMT
              - CustSum.UNPSTDCA - CustSum.UNPSTOCA
- CustSum.DEPRECV) CreditConsumed,
       Cust.CRLMTAMT - (CustSum.CUSTBLNC + CustSum.UNPSTDSA
+ CustSum.UNPSTOSA + CustSum.ONORDAMT
              - CustSum.UNPSTDCA - CustSum.UNPSTOCA
- CustSum.DEPRECV) as CreditAvailable
FROM RM00101 Cust (NOLOCK)
INNER JOIN RM00103 CustSum (NOLOCK)
   
ON Cust.CUSTNMBR = CustSum.CUSTNMBR

1 comment:

Post a Comment