3
\$\begingroup\$

The first part of the query grabs Policy's Premiums, Effective and Expiration dates. The second part creates Calendar, and third part (final SELECT statement) returns Earnings broken down by Month and Year.

Everything works fine and takes just 3 seconds to display the result. But then I need to filter what PolicyNumbers to work with, basically I need to get rid of PolicyNumber's that dont have @ClassCode. So for that in the first part of the query I placed WHERE clause:

WHERE  State IN ('CA','NV','AZ')        
        AND PolicyNumber IN (
                            SELECT  PolicyNumber
                            FROM    tblClassCodesPlazaCommercial 
                            GROUP BY PolicyNumber
                            HAVING COUNT (CASE WHEN ClassCode NOT IN (@ClassCode)                                                                       
 THEN 1 END)=0
                            )

The CASE statement will generate 1 for the ClassCode that is present in the list, else NULL will be generated. Now the count aggregate will count of 1 for each PolicyNumber. By setting = 0 we can make sure the PolicyNumber does not have any ClassCode present in the given list.

After that query spinning forever, becase @ClassCode can have more that 200 ClassCodes in SSRS report.

Interesting things is that both those statements works fine separately. But when I use them together (which is placed that WHERE clause in cte policy_data then execution takes forever.

Is any way to tell engine to do the first part of the query, and then calculate and break down Earnings for only those policies that have been filtered?

; WITH Earned_to_date AS (
   SELECT Cast(EOMONTH (GETDATE(), -1) AS DATE) AS Earned_to_date
), policy_data AS (
    SELECT
        PolicyNumber
,       Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
,       Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
,       WrittenPremium
,       State
        FROM PlazaInsuranceWPDataSet
        WHERE  State IN ('CA','NV','AZ') 
    /* -------This statement gives me trouble ----------------------*/
        AND PolicyNumber IN (
                            SELECT  PolicyNumber
                            FROM    tblClassCodesPlazaCommercial 
                            GROUP BY PolicyNumber
                            HAVING COUNT (CASE WHEN ClassCode NOT IN (5151)                                                                     
 THEN 1 END)=0
                            )
)

My entire code:

; WITH Earned_to_date AS (
   SELECT Cast(EOMONTH (GETDATE(), -1) AS DATE) AS Earned_to_date
), policy_data AS (
    SELECT
        PolicyNumber
,       Cast(PolicyEffectiveDate AS DATE) AS PolicyEffectiveDate
,       Cast(PolicyExpirationDate AS DATE) AS PolicyExpirationDate
,       WrittenPremium
,       State
        FROM PlazaInsuranceWPDataSet
        WHERE  State IN ('CA','NV','AZ') 
    /* -------This statement gives me trouble ----------------------*/
        AND PolicyNumber IN (
                            SELECT  PolicyNumber
                            FROM    tblClassCodesPlazaCommercial 
                            GROUP BY PolicyNumber
                            HAVING COUNT (CASE WHEN ClassCode NOT IN (@ClassCode)                                                                       
 THEN 1 END)=0
                            )
)
, digits AS (
SELECT digit
   FROM (VALUES (0), (1), (2), (3), (4)
,      (5), (6), (7), (8), (9)) AS z2 (digit)
), numbers AS (
SELECT 1000 * d4.digit + 100 * d3.digit + 10 * d2.digit + d1.digit AS number
    FROM digits AS d1
    CROSS JOIN digits AS d2
    CROSS JOIN digits AS d3
    CROSS JOIN digits AS d4
), calendar AS (
SELECT
    DateAdd(month, number, '1753-01-01') AS month_of
,   DateAdd(month, number, '1753-02-01') AS month_after
    FROM numbers
), policy_dates AS (
SELECT
   PolicyNumber
,   CASE
        WHEN month_of < PolicyEffectiveDate THEN PolicyEffectiveDate
        ELSE month_of
    END AS StartRiskMonth
,   CASE
       WHEN PolicyExpirationDate < month_after THEN PolicyExpirationDate
       WHEN Earned_to_date.Earned_to_date < month_after THEN Earned_to_date
       ELSE month_after
    END AS EndRiskMonth
,   DateDiff(day, PolicyEffectiveDate, PolicyExpirationDate) AS policy_days
,   WrittenPremium
    FROM policy_data
    JOIN calendar
        ON (policy_data.PolicyEffectiveDate < calendar.month_after
        AND calendar.month_of < policy_data.PolicyExpirationDate)
    CROSS JOIN Earned_to_date
    WHERE  month_of < Earned_to_date
)
SELECT      
            Year(StartRiskMonth) as YearStartRisk, 
            Month(StartRiskMonth) as MonthStartRisk,
            c.YearNum,c.MonthNum,
            convert(varchar(7), StartRiskMonth, 120) as RiskMonth,
            sum(WrittenPremium * DateDiff(day, StartRiskMonth, EndRiskMonth) / policy_days) as EarnedPremium
FROM        tblCalendar  c
            LEFT  JOIN policy_dates l ON c.YearNum=Year(l.StartRiskMonth) AND c.MonthNum = Month(l.StartRiskMonth) 
            AND l.StartRiskMonth BETWEEN '01-01-2015' AND  '12-31-2016'
WHERE       c.YearNum Not IN (2017) 
GROUP BY    convert(varchar(7), StartRiskMonth, 120),
            Year(StartRiskMonth) , Month(StartRiskMonth),
            c.YearNum,c.MonthNum
ORDER BY    c.YearNum,c.MonthNum

What would be the best way to improve the performance? I created non-clustered index on PolicyNumber on both tables. But still nothing.

Like I said, seems to me that if SQL Engine would process the first part(PolicyNumber filtering) that takes 3 seconds, and then do the second part (calculation for those PolicyNumber's) that takes another 3 seconds - that would be awesome.

Execution plan:

enter image description here

Final result:

enter image description here

\$\endgroup\$
-1
\$\begingroup\$

I think this does what you are looking for (more efficiently)

 SELECT disticnt PolicyNumber
 FROM tblClassCodesPlazaCommercial 
 where not exist ( select 1 
                   from tblClassCodesPlazaCommercial
                   where ClassCode NOT IN (@ClassCode) ) 

Original code

SELECT  PolicyNumber
FROM    tblClassCodesPlazaCommercial 
GROUP BY PolicyNumber
HAVING COUNT (CASE WHEN ClassCode NOT IN (@ClassCode) THEN 1 END) = 0
\$\endgroup\$
  • \$\begingroup\$ Down vote may I ask your basis for the down vote? \$\endgroup\$ – paparazzo Feb 21 '17 at 20:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.