SQL Server 2005 summation query

Question

Hi,

I'm trying to create a query on SQL server 2005 that will check if the sum of some fields in some detail records is equal to the total field in a header record. This will be used for creating electronic deposit of checks for the company I'm working for.

The hierarchy looks like this: Deposits --> Batches --> Checks

Before creating the file for electronic deposit, replication might not have fully completed for each table, so I need to check to ensure that each record is there before creating the file. Basically, I want to select a list of locations and ids from my Deposits table where the correct number of batches and the correct number of checks do exist in their respective tables.

Here is the logic that I want to have.

select location, d.id from closing_balance..cb_deposits as d
left outer join closing_balance..cb_checkbatchhf as b on d.id = b.deposit_id and d.location = b.loc
left outer join closing_balance..cb_checkdf as c on b.id = c.batch_id and b.loc = c.loc
where sum(c.check_amt) = b.scanned_subtotal and sum(b.scanned_subtotal) = d.amount and sum(b.num_checks_scanned) = d.count

The above doesn't work because you can't have the aggregate function sum in the where clause. I can easily do this programatically, but if there is a clever way to do this quickly in a SQL statement, that would be best.

Any help is greatly appreciated.

Thanks!

Answer 1

You can't do a sum in a WHERE clause, but you can use the HAVING clause. for example

SELECT
    Id
FROM
    Table
GROUP BY
    Id
HAVING
    SUM(Amount) = 100

Answer 2

You can move your sum check to the having clause, after the where clause:

select location, d.id 
from closing_balance..cb_deposits as d
left outer join closing_balance..cb_checkbatchhf as b 
  on d.id = b.deposit_id and d.location = b.loc
left outer join closing_balance..cb_checkdf as c 
  on b.id = c.batch_id and b.loc = c.loc
group by location, d.id, b.scanned_subtotal, d.amount, d.count
having sum(c.check_amt) = b.scanned_subtotal 
   and sum(b.scanned_subtotal) = d.amount 
   and sum(b.num_checks_scanned) = d.count

Answer 3

replication might not have fully completed for each table! surely this is the problem that needs addressing instead of a work around record count check.

How are you replicating the data?