Looping through a database using multiple tables (and returning a SUM)-CodePudding

I currently have a database with a table of transactions that an apartment owner makes in a set of apartments

When I run this query:

SELECT Account, Sum([Debit]) - SUM([Credit]) as Balance
  FROM [Boschendal Manor BC].[dbo].[PostGL] as PostGl 
  Inner JOIN [Boschendal Manor BC].[dbo].[Client] as Client on Client.DCLink = DrCrAccount
  where DrCrAccount = '2' and AccountLink <> '104' and TxDate < '2021/11/30' Group By Account

It shows the Account number and the Balance before the 30th Of November for unit 1 - and if I increase DrCrAccount to 3, it will show the same for unit 2 ... and so on

So I need to loop through the number of clients in the database and get each account number and balance.

I have figured out how to get the number of clients, like so:

select COUNT(*) from [Boschendal Manor BC].[dbo].[Client] where DCBalance is not NULL

This returns 130

Does anyone know how I can loop through the PostGL table for as long as the Clients table is , and then return the sum of debit and credit where the date is before 2021/11/30?

CodePudding user response：

adding DrCrAccount to group by and delete the filter will give you all clients data. Like this :

SELECT   DrCrAccount , Account, Sum([ Debit ]) - SUM([ Credit ]) as Balance
  FROM [ Boschendal Manor BC ] . [ dbo ] . [ PostGL ] as PostGl
 Inner JOIN [ Boschendal Manor BC ] . [ dbo ] . [ Client ] as Client
    on Client.DCLink = DrCrAccount
 where  AccountLink <> '104'
   and TxDate < '2021/11/30'
 Group By Account , DrCrAccount

Edit Regarding to your comment on this answer : You cannot filter alias like balance directly.

One solution is to put it as sub query and filter it afterwards like this :

Select * from 
(
SELECT   DrCrAccount , Account, Sum([ Debit ]) - SUM([ Credit ]) as Balance
  FROM [ Boschendal Manor BC ] . [ dbo ] . [ PostGL ] as PostGl
 Inner JOIN [ Boschendal Manor BC ] . [ dbo ] . [ Client ] as Client
    on Client.DCLink = DrCrAccount
 where  AccountLink <> '104'
   and TxDate < '2021/11/30'
 Group By Account , DrCrAccount
) tbl
where tbl.Balance > 200