Aggregates in SQL Server involving 3 tables

Hello DBA's, I need some help. I have 3 tables namely: Credit Limit, Sold, Paid. What I need are the following:

1. I need to subtract the Paid from the Sold (we'll call this Unpaid)

2. I need to get the remaining Credit Limit by subtracting Credit Limit against Unpaid.

below is my query but it gives me a wrong result:

declare @CreditLimit table
(
[AgentName] [nvarchar](50),
[AgentID] [nvarchar](50),
[CreditLimit] [decimal](18, 2)
)
INSERT into @CreditLimit
SELECT
a.AgentName,
a.AgentID,
a.CreditLimit
FROM [dbo].[Agents] a
DECLARE @SoldInsurance table
(
[AgentID] nvarchar(50),
[TotalPremium] [decimal](18, 2)
)
insert into @SoldInsurance
SELECT m.AgentID, SUM(m.TotalPremium)
FROM tblMotorInsurance_eCI M
group by m.AgentID
declare @PaidInsurance table
(
[AgentID] nvarchar(50),
[ConvertedNetPremium] [decimal](18, 2),
[ConvertedRegistryFee] [decimal](18, 2),
[ConvertedVAT] [decimal](18, 2)
)
INSERT INTO @PaidInsurance
SELECT t.AgentID, sum(isnull(t.ConvertedNetPremium,0)), sum(isnull(t.[ConvertedRegistryFee],0)), sum(ISNULL(t.[ConvertedVAT],0)) AS PAYMENT
FROM TaxInvoiceDetails t
group by t.AgentID
SELECT c.AgentName,
C.AgentID,
isnull(C.CreditLimit,0) as CreditLimit,
isnull(m.TotalPremium,0) as InsuranceSold,
isnull(T.ConvertedNetPremium,0) + isnull(t.ConvertedRegistryFee,0) + isnull(t.ConvertedVAT,0) as PaidInsurance,
isnull(M.TotalPremium,0) - isnull(T.ConvertedNetPremium,0) - isnull(t.ConvertedRegistryFee,0) - isnull(t.ConvertedVAT,0) as UnpaidInsurance,
ISNULL(c.CreditLimit,0) - isnull(M.TotalPremium,0) - isnull(T.ConvertedNetPremium,0) - isnull(t.ConvertedRegistryFee,0) - isnull(t.ConvertedVAT,0) as RemainingCreditLimit
FROM @CreditLimit c left outer join @SoldInsurance m on c.AgentID = m.AgentID
left outer join @PaidInsurance t on m.AgentID = t.AgentID
where m.AgentID = '00003'
--where 1=1

I ran it and it gaves me this:

the paid and unpaid part is correct but on the Remaining Credit Limit is wrong. Please advice. Thank you.

Hi,

There is a small missing in your query. You are missing parentheses ()

Try below query -

SELECT c.AgentName,
C.AgentID,
isnull(C.CreditLimit,0) as CreditLimit,
isnull(m.TotalPremium,0) as InsuranceSold,
isnull(T.ConvertedNetPremium,0) + isnull(t.ConvertedRegistryFee,0) + isnull(t.ConvertedVAT,0) as PaidInsurance,
isnull(M.TotalPremium,0) - isnull(T.ConvertedNetPremium,0) - isnull(t.ConvertedRegistryFee,0) - isnull(t.ConvertedVAT,0) as UnpaidInsurance,
ISNULL(c.CreditLimit,0) - (isnull(M.TotalPremium,0) - isnull(T.ConvertedNetPremium,0) - isnull(t.ConvertedRegistryFee,0) - isnull(t.ConvertedVAT,0)) as RemainingCreditLimit
FROM @CreditLimit c left outer join @SoldInsurance m on c.AgentID = m.AgentID
left outer join @PaidInsurance t on m.AgentID = t.AgentID
where m.AgentID = '00003'

2 Replies

Know the answer? Post it — somebody with the same question will find it here.

Midhun Tp9y ago
Accepted answer
Hi,

There is a small missing in your query. You are missing parentheses ()

Try below query -
SELECT  c.AgentName,
         C.AgentID,
        isnull(C.CreditLimit,0) as CreditLimit,
        isnull(m.TotalPremium,0) as InsuranceSold,
        isnull(T.ConvertedNetPremium,0) + isnull(t.ConvertedRegistryFee,0) + isnull(t.ConvertedVAT,0) as PaidInsurance,
        isnull(M.TotalPremium,0) - isnull(T.ConvertedNetPremium,0) - isnull(t.ConvertedRegistryFee,0) - isnull(t.ConvertedVAT,0) as UnpaidInsurance,
        ISNULL(c.CreditLimit,0) - (isnull(M.TotalPremium,0) - isnull(T.ConvertedNetPremium,0) - isnull(t.ConvertedRegistryFee,0) - isnull(t.ConvertedVAT,0)) as RemainingCreditLimit
FROM @CreditLimit c left outer join @SoldInsurance m on c.AgentID = m.AgentID
            left outer join @PaidInsurance t on m.AgentID = t.AgentID
where m.AgentID = '00003'
Jes Sie9y ago
Thank you so much Midhun! It works now! Why I didn't see that? Oh...I gotta rest. Thanks again.