This is a bit lengthy, but lets say we have three tables

a) tblSaleStatementCustomer
b) tblCreditors
c) tblReceiptDue

which shows records like below

Table 1 - tblSaleStatementCustomer

ID   CustomerName     VoucherType    Outbound     Inbound     CustomerType
----------------------------------------------------------------------------------------------
1     ABC                              Sales                10000              0                   Dealer
2     MNC                             Sales                  9000              0                   Dealer
3     MNC                             Sales                  4000              0                   Dealer

Table 2 -  tblCreditors

ID   Name     OpeningBalance
----------------------------------------------------------------------------------------------
1     ABC          20000  
2     MNC         15000 
3     XBM         18000
4     XYZ          12000

Table 3 -  tblReceiptDue ( this table is blank, just have three columns)

ID   Dealer     Amount
----------------------------------------------------------------------------------------------

Now, I am executing the below query

WITH cte AS (SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT from tblSaleStatementCustomer  WHERE CustomerType = 'Dealer' GROUP BY CustomerName) INSERT INTO tblReceiptDue (Dealer, Amount) SELECT CustomerName ,SUM(AMOUNT+OpeningBalance) as AMOUNT FROM cte JOIN tblCreditors ON cte.CustomerName=tblCreditors.Name GROUP BY CustomerName

Actually, the above query looks for Dealers in CustomerName  column of tblSaleStatement that also exist in the Name columns of tblCreditors, and then sum up  Outbound Column of tblSaleStatement with the OpeningBalance column of tblCreditors. So we get the result similar to below

Table 3 -  tblReceiptDue ( data inserted after the query)

ID   Dealer     Amount
----------------------------------------------------------------------------------------------
1     ABC         30000
2     MNC        28000

But the problem is that, the data generated after the query only shows those dealers that were found in both the tables (tblSaleStatementCustomer and tblCreditors respectively). I want that the result also include those dealers as well those are listed in tblCreditors, doesn't matter if they exist in tblSaleCustomer or not, so we should get the result similar to below.

ID   Dealer     Amount
----------------------------------------------------------------------------------------------
1     ABC         30000
2     MNC        28000
3     XBM         18000
4     XYZ          12000

Can somebody help me out please.

The following should only be used if you do NOT have multiple rows in tblCreditors with the same Name.

;WITH cte AS (SELECT CustomerName, SUM(CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END) AS AMOUNT 
from tblSaleStatementCustomer  
WHERE CustomerType = 'Dealer' 
GROUP BY CustomerName) 
INSERT INTO tblReceiptDue (Dealer, Amount) 
SELECT tblCreditors.Name, ISNULL(AMOUNT, 0) + OpeningBalance as AMOUNT 
FROM tblCreditors
LEFT JOIN cte ON cte.CustomerName=tblCreditors.Name

If you have (or might have) multiple rows in tblCreditors with the same Name, then in order to get the correct query, you will have to tell us what result you want in that case.

Tom

• Edited by Tom Cooper 8 hours 2 minutes ago
• Proposed as answer by Naomi NModerator 5 hours 56 minutes ago
• Marked as answer by Sid Williams 1 hour 31 minutes ago

Try

;WITH cte AS (SELECT CustomerName, CASE WHEN VoucherType = 'Sales' then Outbound ELSE  - Inbound END AS AMOUNT 
from tblSaleStatementCustomer  
WHERE CustomerType = 'Dealer' 
) 
INSERT INTO tblReceiptDue (Dealer, Amount) 
SELECT tblCreditors.Name, SUM(ISNULL(AMOUNT, 0) + OpeningBalance) as AMOUNT 
FROM tblCreditors
LEFT JOIN cte ON cte.CustomerName=tblCreditors.Name
GROUP BY tblCreditors.Name