如何生成具有不同Where语句[DUPLICAT]的查询

Question

我在一家保险公司工作，必须创建一个类似这样的报告：Excel文档中的数据是如何在SSR上的

我的问题是，我不知道如何计算不同列中不同的引用、绑定和提交的#，因为它们都是从同一个字段中提取的tbl_quote.accountid

     --RENEWAL BUSINESS

DECLARE @StartDate DATETIME = '05/1/18'
DECLARE @EndDate DATETIME = '11/30/2018'
DECLARE @Policy int = 33

--SUBMISSIONS
SELECT     Month(S.CoverageEffDate) [Month], count(distinct Q.AccountId) as [R#ofsubmissions],(SUM(S.NotTaxableSubTotal + S.NotTaxableSubTotal2 + S.TaxableSubTotal)) as RSubmittedPremium

FROM         tbl_Quote Q INNER JOIN
                      tbl_Quote_Scheduled_Locations S ON Q.id = S.Ouote_ID
WHERE     (S.CoverageEffDate <= @EndDate) AND (S.CoverageEffDate >= @StartDate) AND (Q.Renewal = 1) AND (Q.Cancellation <> 1) and Q.Endorsement=0 and Q.Policy_ID= @policy
Group By Month(S.CoverageEffDate)
ORDER BY Month;

--QUOTES
SELECT     Month(S.CoverageEffDate) [Month], count(distinct Q.AccountId) as [R#ofquotes], (SUM(S.NotTaxableSubTotal + S.NotTaxableSubTotal2 + S.TaxableSubTotal)) as RQuotedPremium

FROM         tbl_Quote Q INNER JOIN
                      tbl_Quote_Scheduled_Locations S ON Q.id = S.Ouote_ID
WHERE     (S.CoverageEffDate <= @EndDate) AND (S.CoverageEffDate >= @StartDate) AND (Q.Renewal = 1) AND  (Q.Cancellation <> 1)  and Q.Endorsement=0 AND (Q.QuoteStatus_OID = 6 OR Q.QuoteStatus_OID = 23 OR Q.QuoteStatus_OID = 10 OR Q.QuoteStatus_OID =8 ) and Q.Policy_ID= @policy
Group By Month(S.CoverageEffDate)
ORDER BY Month;


--BINDS
SELECT     Month(S.CoverageEffDate) [Month], count(distinct Q.AccountId) as [R#ofbinds], (SUM(S.NotTaxableSubTotal + S.NotTaxableSubTotal2 + S.TaxableSubTotal)) as RBoundPremium

FROM         tbl_Quote Q INNER JOIN
                      tbl_Quote_Scheduled_Locations S ON Q.id = S.Ouote_ID
WHERE     (S.CoverageEffDate <= @EndDate) AND (S.CoverageEffDate >= @StartDate) AND (Q.Renewal = 1) AND  (Q.Cancellation <> 1)  and Q.Endorsement=0 AND (S.Bound = 1 or Q.QuoteStatus_OID = 23) and Q.Policy_ID= @policy
Group By Month(S.CoverageEffDate)
ORDER BY Month;

Answer 1

然后使用派生表。

SELECT S.[Month]
    , S.[R#ofsubmissions]
    , S.[RSubmittedTotalTIV]
    , S.RSubmittedPremium
    , Q.[R#ofquotes]
    , Q.[RQuotedTotalTIV]
    , Q.RQuotedPremium
    , B.[R#ofbinds]
    , B.[RBoundTotalTIV]
    , B.RBoundPremium
FROM 
(
    SELECT     
        Month(L.CoverageEffDate) [Month], 
        count(distinct Q.AccountId) as [R#ofsubmissions], 
        SUM(L.TIV) as [RSubmittedTotalTIV],
        (SUM(L.NotTaxableSubTotal + L.NotTaxableSubTotal2 + L.TaxableSubTotal)) as RSubmittedPremium
    FROM tbl_Quote Q
    INNER JOIN tbl_Quote_Scheduled_Locations L
        ON Q.id = L.Ouote_ID
    WHERE (L.CoverageEffDate <= @EndDate) 
        AND (L.CoverageEffDate >= @StartDate) 
        AND (Q.Renewal = 1) AND (Q.Cancellation <> 1) 
        and Q.Endorsement=0 
        and Q.Policy_ID= @policy
    Group By Month(L.CoverageEffDate)
) S
    INNER JOIN 
( 
    SELECT     
        Month(L.CoverageEffDate) [Month], 
        count(distinct Q.AccountId) as [R#ofquotes], 
        SUM(L.TIV) as [RQuotedTotalTIV],
        (SUM(L.NotTaxableSubTotal + L.NotTaxableSubTotal2 + L.TaxableSubTotal)) as RQuotedPremium
    FROM tbl_Quote Q
    INNER JOIN L 
        ON Q.id = L.Ouote_ID
    WHERE (L.CoverageEffDate <= @EndDate) 
        AND (L.CoverageEffDate >= @StartDate) 
        AND (Q.Renewal = 1) 
        AND  (Q.Cancellation <> 1)  
        and Q.Endorsement=0 
        AND (Q.QuoteStatus_OID = 6 OR Q.QuoteStatus_OID = 23 OR Q.QuoteStatus_OID = 10 OR Q.QuoteStatus_OID =8 ) 
        and Q.Policy_ID= @policy
    Group By Month(L.CoverageEffDate)
) Q
        ON S.[Month] = Q.[Month]
    INNER JOIN 
(
    SELECT     
        Month(L.CoverageEffDate) [Month], 
        count(distinct Q.AccountId) as [R#ofbinds], 
        SUM(L.TIV) as [RBoundTotalTIV],
        (SUM(L.NotTaxableSubTotal + L.NotTaxableSubTotal2 + L.TaxableSubTotal)) as RBoundPremium
    FROM tbl_Quote Q
    INNER JOIN tbl_Quote_Scheduled_Locations L
        ON Q.id = L.Ouote_ID
    WHERE (L.CoverageEffDate <= @EndDate) 
        AND (L.CoverageEffDate >= @StartDate) 
        AND (Q.Renewal = 1) 
        AND  (Q.Cancellation <> 1)  
        and Q.Endorsement=0 
        AND (L.Bound = 1 or Q.QuoteStatus_OID = 23) 
        and Q.Policy_ID= @policy
    Group By Month(L.CoverageEffDate)
) B
        ON S.[Month] = B.[Month]
ORDER BY S.[Month]