Multiple Join Multipliers_问答_开发者_运维开发者技术经验分享

I have 3 tables: Insurance Policies, Claims and Receivables

I need a query that will return one row per policy period per policy. The query needs to include the policy start and end date, total claims for each period, total paid and O/S for each period, and total amount received for each period.

I've managed to do everything but the recievables. When this is introduced to the query, everything is multiplied by the number of rows in that table.

Here is my data

Policies

PolNo   Version   TransType   InceptionDate   RenewalDate
0021          0   New         01/开发者_高级运维01/2008      01/01/2009
0021          1   MTA         01/01/2008      01/01/2009
0021          2   MTA         01/01/2008      01/01/2009
0021          3   Renewal     01/01/2009      01/01/2010

Claims

PolNo   ClaimNo   ClaimDate      Paid    Outstanding
0021    0001      01/05/2008   300.00        -100.00
0021    0002      01/06/2008   500.00         200.00
0021    0003      01/07/2008   200.00         300.00
0021    0004      01/08/2008   800.00           0.00
0021    0005      01/02/2009     0.00           0.00
0021    0006      01/10/2009     0.00        1000.00

Receivables

PolNo   Version   RecvdDate    Amount
0021          0   02/01/2008   150.00
0021          0   01/02/2008   150.00
0021          0   01/03/2008   150.00
0021          0   01/04/2008   150.00
0021          0   01/05/2008   150.00
0021          0   01/06/2008   150.00
0021          0   01/07/2008   150.00
0021          0   01/08/2008   150.00
0021          2   01/09/2008   150.00
0021          2   01/10/2008   150.00
0021          3   02/01/2009   500.00
0021          3   01/02/2009   500.00

Here is my working query

select distinct(a.InceptionDate) as InceptionDate, a.RenewalDate as RenewalDate,
count(b.ClaimNo) as ClaimCount, sum(b.Paid) as TotPaid, sum(b.Outstanding) as TotalO/S
from Policies a, Claims b
where a.PolNo='0021'
and a.PolNo=b.PolNo
and b.ClaimDate between a.InceptionDate and a.RenewalDate
and a.TransType in ('New','Renewal')
group by a.InceptionDate, a.RenewalDate

Result:

InceptionDate   RenewalDate   ClaimCount      TotPaid       TotalO/S
01/01/2008      01/01/2009        4           1800.00       400.00
01/01/2009      01/01/2010        2           0             1000.00

I had to add TransType to the query as I was getting a multiplier for claims data, but this query works.

Now when I add the third table, I get a multiplier. Everything from Claims is multiplied by the number or rows in Receivables for the period, and AmountRecvd is multiplied by the number of rows in Claims for the period.

Here is the query:

select distinct(a.InceptionDate) as InceptionDate, a.RenewalDate as RenewalDate,
count(b.ClaimNo) as ClaimCount, sum(b.Paid) as TotPaid, sum(b.Outstanding) asTotalO/S,
sum(c.Amount) as RecvdAmount
from Policies a, Claims b, Receivables c
where a.PolNo='0021'
and a.PolNo=b.PolNo
and a.PolNo=c.PolNo
and b.ClaimDate between a.InceptionDate and a.RenewalDate
and c.RecvdDate between a.InceptionDate and a.RenewalDate
and a.TransType in ('New','Renewal')
group by a.InceptionDate, a.RenewalDate

Result

InceptionDate   RenewalDate   ClaimCount   TotPaid   TotalO/S   AmountRecvd
01/01/2008      01/01/2009       40         18000       4000       6000.00
01/01/2009      01/01/2010       20             0      10000       2000.00

I cant see that it is possible to join Receivables with Claims. I tried joining them on PolNo but had the same result.

Can anyone see a solution?

Cheers

Updated This query doesn't muliply ClaimsCount, but AmountRevd still multiplying by Claims (TotPaid removed for simplicity):

SELECT
    p.InceptionDate,
    p.RenewalDate,
    sum(c1.ClaimCount) AS ClaimsCount,
    sum(r1.TotRecvd) AS AmountRecvd
FROM Policies p
LEFT JOIN (SELECT 
        p1.InceptionDate, 
        p1.RenewalDate, 
        c.Polno, 
        c.ClaimDate,
        COUNT(c.SubCount) AS ClaimCount
    FROM Policies p1 
    INNER JOIN (SELECT 
            PolNo,
            ClaimDate,
            Count(*) as SubCount
        FROM Claims 
        GROUP BY PolNo, ClaimDate) c
        ON p1.PolNo=c.PolNo
    WHERE c.ClaimDate BETWEEN p1.InceptionDate AND p1.RenewalDate
    AND p1.TransType IN ('New','Renewal')
    GROUP BY p1.InceptionDate, p1.RenewalDate, c.Polno, c.ClaimDate) c1
    ON p.PolNo=c1.PolNo
LEFT JOIN (SELECT 
        p2.InceptionDate, 
        p2.RenewalDate, 
        r.Polno,
        SUM(r.SubTot) AS TotalRecvd
    FROM Policies p2
    INNER JOIN (SELECT
            PolNo,
            RecvdDate,
            SUM(Amount) as SubTot
        FROM Receivables
        GROUP BY Polno,RecvdDate) r
        ON p2.PolNo=r.PolNo
    WHERE r.RecvdDate BETWEEN p2.InceptionDate AND p2.RenewalDate
    AND p2.TransType IN ('New','Renewal')
    GROUPBY p2.InceptionDate, p2.RenewalDate, r.Polno) r1
    ON p.PolNo=r1.PolNo
WHERE p.PolNo = '0021'
AND p.TransType IN ('New','Renewal')
AND p.PolNo = c1.PolNo
AND p.PolNo = r1.PolNo
AND p.InceptionDate = c1.InceptionDate
AND p.RenewalDate = c1.RenewalDate
AND p.InceptionDate = r1.InceptionDate
AND p.RenewalDate = r1.RenewalDate
GROUP BY p.InceptionDate, p.RenewalDate

-- Query for the periods:
WITH Periods AS (
    SELECT DISTINCT
        PolNo, InceptionDate, RenewalDate
    FROM Policies
    WHERE TransType in ('New','Renewal')
),

-- Query for the claims
PeriodClaims AS (
    SELECT
        p.PolNo, p.InceptionDate, p.RenewalDate,
        COUNT(*) AS CountClaims,
        SUM(c.Paid) AS PaidClaims,
        SUM(c.Outstanding) AS OutstandingClaims
    FROM Periods p
    INNER JOIN Claims c ON c.PolNo = p.PolNo
    WHERE c.ClaimDate BETWEEN p.InceptionDate AND p.RenewalDate
    GROUP BY p.PolNo, p.InceptionDate, p.RenewalDate
),

-- Query for the receivables
PeriodRecieved AS (
    SELECT
        p.PolNo, p.InceptionDate, p.RenewalDate,
        COUNT(*) AS CountReceived,
        SUM(r.Amount) AS AmountReceived
    FROM Periods p
    INNER JOIN Receivables r ON r.PolNo = p.PolNo
    WHERE r.RecvdDate BETWEEN p.InceptionDate AND p.RenewalDate
    GROUP BY p.PolNo, p.InceptionDate, p.RenewalDate
)

-- All together now
SELECT 
    p.PolNo, p.InceptionDate, p.RenewalDate,
    c.CountClaims, c.PaidClaims, c.OutstandingClaims,
    r.CountReceived, r.AmountReceived
FROM Periods p
LEFT JOIN PeriodClaims c
    ON c.PolNo = p.PolNo
    AND c.InceptionDate = p.InceptionDate
    AND c.RenewalDate = p.RenewalDate
LEFT JOIN PeriodRecieved r
    ON r.PolNo = p.PolNo
    AND r.InceptionDate = p.InceptionDate
    AND r.RenewalDate = p.RenewalDate

Or, without CTE:

SELECT 
    p.PolNo, p.InceptionDate, p.RenewalDate,
    c.CountClaims, c.PaidClaims, c.OutstandingClaims,
    r.CountReceived, r.AmountReceived
FROM (
    -- Query for the periods:
    SELECT DISTINCT
        PolNo, InceptionDate, RenewalDate
    FROM Policies
    WHERE TransType in ('New','Renewal')
) p
LEFT JOIN (
    -- Query for the claims
    SELECT
        p.PolNo, p.InceptionDate, p.RenewalDate,
        COUNT(*) AS CountClaims,
        SUM(c.Paid) AS PaidClaims,
        SUM(c.Outstanding) AS OutstandingClaims
    FROM (
        -- Query for the periods:
        SELECT DISTINCT
            PolNo, InceptionDate, RenewalDate
        FROM Policies
        WHERE TransType in ('New','Renewal')
    ) p
    INNER JOIN Claims c ON c.PolNo = p.PolNo
    WHERE c.ClaimDate BETWEEN p.InceptionDate AND p.RenewalDate
    GROUP BY p.PolNo, p.InceptionDate, p.RenewalDate
) c
    ON c.PolNo = p.PolNo
    AND c.InceptionDate = p.InceptionDate
    AND c.RenewalDate = p.RenewalDate
LEFT JOIN (
    -- Query for the receivables
    SELECT
        p.PolNo, p.InceptionDate, p.RenewalDate,
        COUNT(*) AS CountReceived,
        SUM(r.Amount) AS AmountReceived
    FROM (
        -- Query for the periods:
        SELECT DISTINCT
            PolNo, InceptionDate, RenewalDate
        FROM Policies
        WHERE TransType in ('New','Renewal')
    ) p
    INNER JOIN Receivables r ON r.PolNo = p.PolNo
    WHERE r.RecvdDate BETWEEN p.InceptionDate AND p.RenewalDate
    GROUP BY p.PolNo, p.InceptionDate, p.RenewalDate
) r
    ON r.PolNo = p.PolNo
    AND r.InceptionDate = p.InceptionDate
    AND r.RenewalDate = p.RenewalDate

Output (transposed):

PolNo                       21           21
InceptionDate       2008-01-01   2009-01-01
RenewalDate         2009-01-01   2010-01-01
CountClaims                  4            2
PaidClaims             1800.00         0.00
OutstandingClaims       400.00      1000.00
CountReceived               10            2
AmountReceived         1500.00      1000.00