Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 178
  • Last Modified:

Filter query by balance

In the query below, I do not want to see any of the bonds where the balance of the bond's fees-payments are equal to 0...sum(bondid.fees)-sum(bondid.payments) <>0

SELECT d.Lastname + ', ' + d.Firstname as Defendant, b.BondNo, D, c.CompanyName,  t.paymentamount AS Payments, t.amount AS Fees
      FROM bonds b
      LEFT OUTER JOIN
      (
      SELECT bondid, paymentdate AS D, paymentamount, 0 amount
            FROM payments p
            LEFT OUTER JOIN lstPayType lp on lp.PaymentTypeID = p.PaymentTypeID
            WHERE lp.PaymentTypeID = 1
            UNION ALL
      SELECT bondid, paiddate AS D, 0 paymentamount, amount
            FROM fees f
            LEFT OUTER JOIN lstFeeTypes lf on lf.FeeTypeID = f.FeeTypeID
            WHERE lf.FeeTypeID = 1
) t on t.bondid = b.id

LEFT OUTER JOIN Defendants d on d.DefendantID = b.DefendantID
INNER JOIN Company c on c.CompanyID = d.CompanyID

WHERE (d.Lastname + ', ' + d.Firstname = @Defendant OR @Defendant = '_ALL_')  

ORDER BY d.Lastname + ', ' + d.Firstname, b.BondNo, d
0
williak7
Asked:
williak7
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:

SELECT d.Lastname + ', ' + d.Firstname as Defendant, b.BondNo, D, c.CompanyName,  t.paymentamount AS Payments, t.amount AS Fees
      FROM bonds b
      LEFT OUTER JOIN
      (
      SELECT bondid, paymentdate AS D, paymentamount, 0 amount
            FROM payments p
            JOIN lstPayType lp on lp.PaymentTypeID = p.PaymentTypeID
            AND lp.PaymentTypeID = 1
      UNION ALL
      SELECT bondid, paiddate AS D, 0 paymentamount, amount
            FROM fees f
            JOIN lstFeeTypes lf on lf.FeeTypeID = f.FeeTypeID
            AND lf.FeeTypeID = 1
) t on t.bondid = b.id
LEFT OUTER JOIN Defendants d on d.DefendantID = b.DefendantID
INNER JOIN Company c on c.CompanyID = d.CompanyID
WHERE (d.Lastname + ', ' + d.Firstname = @Defendant OR @Defendant = '_ALL_')  
AND ( SELECT SUM(p.paymentamount)
     FROM payments p
            JOIN lstPayType lp on lp.PaymentTypeID = p.PaymentTypeID
            AND lp.PaymentTypeID = 1
            AND p.bondid = b.id
    )
 <> ( SELECT SUM(f.amount )
       FROM fees f
       JOIN lstFeeTypes lf on lf.FeeTypeID = f.FeeTypeID
       AND lf.FeeTypeID = 1
       WHERE f.bondid = b.id
    )
ORDER BY d.Lastname + ', ' + d.Firstname, b.BondNo, d
0
 
williak7Author Commented:
Thanks AngelII,

I had something close to that, but couldn't quite get it.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now