How do I make a query that has different Where statements

Question

I work with an Insurance Company and have to create a report like this: Excel document with how data is supposed to be on SSRS

My problem is I cannot figure out how I count the different #s of quotes, binds, and submissions in different columns since they all are pulling from the same field tbl_Quote.AccountId

I need to basically Join theses three other queries to have their respective counts and totals in different columns in the result set and I'm not sure how to go about this.

Here is the code:

     --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;

I need the query to exactly match the excel document besides the totals since SSRS can do totals.

The code I have provided is only for the Renewal side.

Hi and welcome to SO. What is the issue here? Your queries seem to have the counts you are looking for. — Sean Lange, Dec 18 '18 at 19:51
I need to have a single query that has all the different counts I'm requesting as different columns. There is an excel document with the layout I'm looking for. I need it in a single query since I am creating the report in SSRS. I'm not exactly sure how to go about this. — Tyler Stevens, Dec 18 '18 at 19:53
I highly recommend making use of aliases. They really help with the readability of your code; as does good use of whitespace and line breaks. — Thom A, Dec 18 '18 at 20:00

tecshack · Accepted Answer · 2018-12-18T21:00:05.453

Use derived tables then.

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]

I'm getting an error on the With portion. I've never used that before? — Tyler Stevens, Dec 18 '18 at 20:36
My apologies. I don't do a lot of multiple cte's. This should do what you want now. — tecshack, Dec 18 '18 at 20:48
Thank you for your help but I still am getting an error when I try the query : "Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon." — Tyler Stevens, Dec 18 '18 at 20:53

How do I make a query that has different Where statements

1 Answers1