How do I make a query that has different Where statements [duplicate]

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
– Sean Lange, Commented Dec 18, 2018 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
– Tyler Stevens, Commented Dec 18, 2018 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
– Thom A ♦, Commented Dec 18, 2018 at 20:00

tecshack · Accepted Answer · 2018-12-18 21:00:05Z

0

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]

edited Dec 18, 2018 at 21:00

answered Dec 18, 2018 at 20:17

tecshack

1087 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

Tyler Stevens Over a year ago

I'm getting an error on the With portion. I've never used that before?

tecshack Over a year ago

My apologies. I don't do a lot of multiple cte's. This should do what you want now.

Tyler Stevens Over a year ago

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."

tecshack Over a year ago

Switched it from cte to derived tables.

Tyler Stevens Over a year ago

Thank YOU SO MUCH! That worked!!

Collectives™ on Stack Overflow

How do I make a query that has different Where statements [duplicate]

1 Answer 1

5 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Linked

Related