Tech
Forums
Jobs
Books
Events
Interviews
Live
More
Learn
Training
Career
Members
Videos
News
Blogs
Login
Sign Up
Ask Question
2
Reply
Aggregates in SQL Server involving 3 tables
Hold On
Sep 8 2017 4:21 AM
168
Reply
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:
credit limit sold paid unpaid remaining credit limit
50,000,000.00 58,232,073.40 32,487,325.40 25,744,748.00 (40,719,398.80)
the paid and unpaid part is correct but on the Remaining Credit Limit is wrong. Please advice. Thank you.
Upload Source Code
Select only zip and rar file.
Post
Reset
Cancel
Answers (
2
)
Next Recommended Forum
scraping the data in web to stored the sql database server
Difference between ado.net and entity framework in.net?