Query containing while loop for dates taking increditbly long

  • daniness

    SSCrazy

    Points: 2890

    Hi All,

    I'm hoping to get some insight to an issue we're experiencing with a query in an SSIS package, which has been taking ages to complete. It takes about 3 hours in our Dev environment, and progressively longer as we've tried deploying to the upper environments. We thought as first, it might have to do with the date variables being passed to it, but I don't believe that's the case, as I've tried running the package with just a week spanning between the dates, and it's taking an hour plus. Granted that the date range is pretty long, i.e. about 10 years, but I don't think it should be taking this long....our last attempt to deploy to Prod took it over 10 hours before it had to be cancelled and rolled back. Any insight that could be provided would be greatly appreciated. I'm including the query below:

    DECLARE @StartDate DateTime = '2010-01-01'
    DECLARE @EndDate DateTime = '2019-05-01'

    DECLARE @StartDT DATETIME
    DECLARE @EndDT DATETIME
    DECLARE @LoopEndDT DATETIME
    DECLARE @PeriodStart DateTime
    DECLARE @PeriodEnd DateTime
    DECLARE @RunMonth INT

    -- get last day of month
    SET @StartDT = DATEADD(mm, DATEDIFF(mm, 0,@StartDate) +1, -1)
    PRINT @StartDT

    SET @LoopEndDT = @EndDate
    PRINT @LoopEndDT

    -- Loop through end of month days
    WHILE @StartDT < @LoopEndDT
    BEGIN
    SET @EndDT = DATEADD(DAY,1,@StartDT);
    --SET @EndDT = DATEADD(MONTH,1,@StartDT);
    SET @PeriodStart = DATEADD(MONTH, -1, @StartDt);
    SET @PeriodEnd = DATEADD(ms, -3, @StartDt);
    SET @RunMonth = convert(int, substring(CONVERT(char(10), @StartDT, 112),1,6));

    -- Create a list by last day of month for each Enrollment ID that has coverage that day, no record means no coverage
    -- Active coverage means the effective date is before the *end* of the last day of the month (23:59:59), and the end date is after the *beginning* of the last day of the month (00:00:00)
    -- Pull the MAX asset value for those cases where the dates overlap (to prevent pulling multiple records)

    IF OBJECT_ID('tempdb.dbo.##FSEDMEnrollDataZ') IS NOT NULL
    DROP TABLE dbo.##FSEDMEnrollDataZ;
    --GO;

    CREATE TABLE ##FSEDMEnrollDataZ (
    [EnrollmentID] [nvarchar] (max) NOT NULL,
    [CoverageID] [int] NOT NULL,
    [PSPolicyTransID] [nvarchar] (200) NOT NULL, --adding this to populate into Staging table

    [PolicyNumber] [nvarchar] (100) NULL,
    [PolicyCode] [nvarchar](50) NULL,
    [PCMPolicyID] [int] NOT NULL,

    [ProductName] [nvarchar] (200) NULL,
    [ProductCode] [nvarchar] (50) NULL,
    [CustomerAccountNumber] [nvarchar](max) NULL,

    [CoverageTypeName] [nvarchar] (200) NULL,
    [AssetClassCode] [nvarchar] (50) NULL,
    [AssetClassDescription] [nvarchar] (200) NULL,

    [ClientPCMPartyID] [int] NOT NULL,
    [ClientAddress1] [nvarchar] (max) NULL,
    [ClientAddress2] [nvarchar] (max) NULL,

    [ClientCity] [nvarchar] (max) NULL,
    [ClientState] [nvarchar] (max) NULL,
    [ClientPostalCode] [nvarchar] (max) NULL,

    [ClientCountry] [nvarchar] (max) NULL,
    [SourceCoverageStartDate] [datetime] NOT NULL,
    [SourceCoverageEndDate] [datetime] NULL,

    [SourceCoverageTerm] [int] NOT NULL,
    --[CancelDate] [datetime] NULL,
    [PeriodEndCalendar] [datetime] NULL,

    [AccountingPeriod] [int] NULL,
    [FullTermPremium] [money] NULL,
    [PCMCoverageID] [int] NOT NULL,

    [pcmcov] [int] NOT NULL,

    [OriginalWrittenPremiumMTD] [money] NULL,
    [CancelledWrittenPremiumMTD] [money] NULL,

    [ITDDirectWrittenPremium] [money] NULL,
    [MTDEarnedPremium] [money] NULL,
    [ITDDirectEarnedPremium] [money] NULL

    );

    TRUNCATE table ##FSEDMEnrollDataZ;

    IF OBJECT_ID('tempdb.dbo.#TempAssetClassDetails_CS2') IS NOT NULL
    DROP TABLE dbo.#TempAssetClassDetails_CS2;

    CREATE TABLE #TempAssetClassDetails_CS2(
    [EnrollmentID] [nvarchar] (max) NOT NULL
    ,[CoverageID] [int] NOT NULL
    ,[Code] [nvarchar] (50) NULL
    ,[AssetValue] [money] NULL
    ,[AssetDescription] [nvarchar] (200) NULL);

    with AssetClassCode_by_Enrollments as (
    SELECT pac.AssetClassCode
    ,vec.PCMAssetClassId
    ,vec.EnrollmentID
    ,vec.CoveragePeriod AS CoverageID
    ,sum(vec.AssetValue) AS AssetValue

    FROM ExtVESEnrollmentCoverage vec

    INNER JOIN ExtPCMAssetClass pac ON vec.PCMAssetClassId = pac.AssetClassId

    GROUP BY pac.AssetClassCode
    ,vec.PCMAssetClassId
    --,pac.AssetClassDescription
    ,vec.EnrollmentID
    ,vec.CoveragePeriod )
    ,unique_results AS (
    SELECT EnrollmentID, --AssetClassDescription,
    CoverageID,
    MAX(AssetClassCode) AS Code,
    AssetValue
    FROM AssetClassCode_by_Enrollments
    WHERE CONCAT(EnrollmentID, CoverageID, AssetValue)
    IN (SELECT CONCAT(EnrollmentID
    ,CoverageID
    ,MAX(AssetValue)) AssetValue
    FROM AssetClassCode_by_Enrollments
    GROUP BY EnrollmentID
    ,CoverageID)

    GROUP BY EnrollmentID,
    CoverageID,
    AssetValue)

    INSERT INTO #TempAssetClassDetails_CS2
    SELECT DISTINCT
    EnrollmentID
    ,CoverageID
    ,Code
    ,AssetValue
    ,(SELECT MAX(ISNULL(AssetClassDescription, 'not found in MasterAssetClass table'))
    FROM ExtPCMAssetClass pac
    WHERE pac.AssetClassCode = unique_results.code) AS AssetDescription
    from unique_results
    order by EnrollmentID;

    with ppr AS (
    SELECT
    [SourceId]
    ,[SourceCoveragePeriod]
    ,sum([EarnablePremium]) as EarnablePremium
    ,sum([EarnedPremium]) as EarnedPremium
    ,sum([MonthlyEarnedPremium]) as MonthlyEarnedPremium
    ,[LastMonthEarningsId]
    ,[PcmProducerId]
    ,[ReportDateYearMonth]
    ,sum([WrittenPremium]) as WrittenPremium
    ,sum([CanceledPremium]) as CanceledPremium
    ,[RiskEntityId]
    ,[PcmCoverageId]
    ,[PcmCoverageId] as pcmcov --duplicated so i could use in query above as a group by w/o won't work if use PcmCoverageID i/o copying?
    ,[PcmPolicyId]
    ,[CoverageStatus]
    FROM [dbo].[EXTFSEPercentPremiumReported]
    --WHERE SourceId = '1'
    group by
    [SourceId]
    ,[SourceCoveragePeriod]
    ,[LastMonthEarningsId]
    ,[PcmProducerId]
    ,[ReportDateYearMonth]
    ,[RiskEntityId]
    ,[PcmCoverageId]
    ,[PcmCoverageId]
    ,[PcmPolicyId]
    ,[CoverageStatus])

    INSERT INTO ##FSEDMEnrollDataZ
    /************************** Potential CODE Change **************************************************/
    SELECT

    EnrollmentID
    ,CoverageID
    ,PSPolicyTransID

    ,PolicyNumber
    ,PolicyCode
    ,PCMPolicyID

    ,ProductName
    ,ProductCode
    ,CustomerAccountNumber

    ,CoverageTypeName
    ,AssetClassCode
    ,AssetClassDescription

    ,ClientPcmPartyId
    ,ClientAddress1
    ,ClientAddress2

    ,ClientCity
    ,ClientState
    ,ClientPostalCode

    ,ClientCountry
    ,SourceCoverageStartDate
    ,SourceCoverageEndDate

    ,SourceCoverageTerm
    --,CancelDate
    ,PeriodEndCalendar

    ,reportDateYearMonth --AS AccountingPeriod
    ,(FullTermPremium) AS FullTermPremium
    ,PcmCoverageId

    ,Pcmcov
    --,CoverageStatus

    ,COALESCE(SUM(OriginalWrittenPremium),0) AS OriginalWrittenPremiumMTD
    ,COALESCE(SUM(CancelledWrittenPremium),0) AS CancelledWrittenPremiumMTD

    ,(ITDDirectWrittenPremium) AS ITDDirectWrittenPremium
    ,sum(MTDEarnedPremium) AS MTDEarnedPremium
    ,(ITDDirectEarnedPremium) AS ITDDirectEarnedPremium
    FROM
    (
    SELECT DISTINCT

    ppr.SourceId AS EnrollmentID
    ,ppr.SourceCoveragePeriod AS CoverageID
    ,convert (nvarchar,ept.PSPolicyTransID) AS PSPolicyTransID
    --,ept.PSPolicyTransID AS PSPolicyTransID
    ,ip.PolicyNumber
    ,ire.SourceTransactionId
    ,ip.PolicyCode
    ,ip.PCMPolicyID
    ,ip.ProductName
    ,pp.ProductCode
    ,ire.ContractNumber as CustomerAccountNumber
    ,UPPER(psc.PolicySchemaCoverageName) AS CoverageTypeName
    ,UPPER(TACD.Code) AS AssetClassCode
    ,UPPER(TACD.AssetDescription) AS AssetClassDescription
    ,ip.ClientPcmPartyId
    ,ip.ClientAddress1
    ,ip.ClientAddress2
    ,ip.ClientCity
    ,ip.ClientState
    ,ip.ClientPostalCode
    ,ip.ClientCountry
    ,ire.CoverageStartDate AS SourceCoverageStartDate
    ,ire.CoverageEndDate AS SourceCoverageEndDate
    ,ire.CoverageTerm AS SourceCoverageTerm
    ,ire.CancelDate

    ,EOMonth (DATEADD(MONTH, -1, (CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)))) AS PeriodEndCalendar
    ,ppr.reportDateYearMonth
    ,ppr.PcmCoverageId
    ,ppr.Pcmcov
    ,ppr.CoverageStatus
    ,(SELECT SUM(ppr4.earnablePremium)
    FROM ExtFSEPercentPremiumReported AS ppr4
    WHERE ppr.Sourceid = ppr4.sourceID
    AND ppr.SourceCoveragePeriod = ppr4.SourcecoveragePeriod
    AND ppr4.ReportDateYearMonth = ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr4.PCMCoverageid
    --AND ppr.YearMonth = ppr4.YearMonth
    GROUP BY ppr4.Sourceid, ppr4.SourceCoveragePeriod, ppr4.PcmCoverageId) AS FullTermPremium
    -- ,CASE
    -- WHEN ppr.coverageStatus = 'Active' and ire.CancelDate is null THEN SUM(ppr.WrittenPremium)
    ----Added to see for Rental premium and a possible scenerio (one) that shows enrolled and cancelled and Matured in one month.
    -- WHEN ppr.coverageStatus = 'Matured' and ire.CancelDate is null THEN SUM(ppr.WrittenPremium) --I have one deal that has ALL 3 status', others have matured and still show up in data extract?
    ,(SELECT SUM(ppr.WrittenPremium) --Updated source (3/26/2019)
    FROM ExtFSEPercentPremiumReported AS ppr5
    WHERE ppr.Sourceid = ppr5.sourceID
    AND ppr.SourceCoveragePeriod = ppr5.SourcecoveragePeriod
    AND ppr5.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr5.PCMCoverageid
    AND ppr.CoverageStatus in ('Active', 'Matured')
    AND ire.CancelDate IS NULL
    GROUP BY ppr5.Sourceid, ppr5.SourceCoveragePeriod, ppr5.PcmCoverageId) AS OriginalWrittenPremium

    ,(SELECT SUM(ppr.CanceledPremium)
    FROM ExtFSEPercentPremiumReported AS ppr6
    WHERE ppr.Sourceid = ppr6.sourceID
    AND ppr.SourceCoveragePeriod = ppr6.SourcecoveragePeriod
    AND ppr6.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr6.PCMCoverageid
    AND ppr.CoverageStatus in ('Active','Cancelled')
    GROUP BY ppr6.Sourceid, ppr6.SourceCoveragePeriod, ppr6.PcmCoverageId) AS CancelledWrittenPremium

    ,(SELECT SUM(ppr2.writtenPremium + ppr2.canceledPremium)
    FROM ExtFSEPercentPremiumReported AS ppr2
    WHERE ppr.Sourceid = ppr2.sourceID
    AND ppr.SourceCoveragePeriod = ppr2.SourcecoveragePeriod
    AND ppr2.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr2.PCMCoverageid
    GROUP BY ppr2.Sourceid, ppr2.SourceCoveragePeriod, ppr2.PcmCoverageId) AS ITDDirectWrittenPremium --also GrossWrittenPremium b/c there's no AssumedPremium; this is also NetWrittenPremium, as there's no Ceded

    ,(SELECT SUM(ppr.MonthlyEarnedPremium)
    FROM ExtFSEPercentPremiumReported AS ppr7
    WHERE ppr.Sourceid = ppr7.sourceID
    AND ppr.SourceCoveragePeriod = ppr7.SourcecoveragePeriod
    AND ppr7.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr7.PCMCoverageid
    GROUP BY ppr7.Sourceid, ppr7.SourceCoveragePeriod, ppr7.PcmCoverageId) AS MTDEarnedPremium

    ,(SELECT SUM(ppr3.MonthlyEarnedPremium)
    FROM ExtFSEPercentPremiumReported AS ppr3
    WHERE ppr.Sourceid = ppr3.sourceID
    AND ppr.SourceCoveragePeriod = ppr3.SourcecoveragePeriod
    AND ppr3.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr3.PCMCoverageid
    GROUP BY ppr3.Sourceid, ppr3.SourceCoveragePeriod, ppr3.PcmCoverageId) AS ITDDirectEarnedPremium

    FROM ExtFSEInvoicePolicy ip
    INNER JOIN ExtFSEInvoiceRiskEntity ire ON ip.PCMPolicyID = ire.PcmPolicyId
    INNER JOIN ppr ON ire.SourceId = ppr.SourceId
    AND ire.SourceCoveragePeriod = ppr.SourceCoveragePeriod
    AND ire.riskentityid = ppr.RiskEntityId
    INNER JOIN ExtFSELastMonthEarnings lme ON ppr.PcmPolicyId = lme.PcmPolicyId
    AND ppr.ReportDateYearMonth = lme.CurrentAccountingYearMonth
    AND lme.approvaldate IS NOT NULL
    INNER JOIN ExtPCMPolicySchemaCoverage psc ON ppr.pcmcoverageid = psc.PolicySchemaCoverageId
    INNER JOIN ExtPCMProduct pp ON ip.ProductName = pp.ProductName

    INNER JOIN #TempAssetClassDetails_CS2 AS TACD ON ire.sourceid = TACD.EnrollmentID AND ire.SourceCoveragePeriod = TACD.CoverageID
    --INNER JOIN RiskItem ri ON ire.RiskEntityId = ri.RiskEntityId
    --INNER JOIN RiskEntity re ON ri.RiskEntityId = re.Id --AND ri.SourceID = re.SourceID
    INNER JOIN ExtVESEnrollmentPolicyTrans ept ON Convert(int, ire.SourceId) = ept.EnrollmentID
    AND Convert(int, ire.SourceCoveragePeriod) = ept.CoverageID --To include PolicyTransID field

    WHERE ppr.WrittenPremium + ppr.CanceledPremium + ppr.MonthlyEarnedPremium <> 0
    GROUP BY ppr.SourceId
    ,ppr.SourceCoveragePeriod
    ,ept.PSPolicyTransID
    ,ip.PolicyNumber
    ,ire.SourceTransactionId
    ,ip.PolicyCode
    ,ip.PCMPolicyID
    ,ip.ProductName
    ,pp.ProductCode
    ,ire.ContractNumber
    ,UPPER(psc.PolicySchemaCoverageName)
    ,UPPER(TACD.Code)
    ,UPPER(TACD.AssetDescription)
    ,ip.ClientPcmPartyId
    ,ip.ClientAddress1
    ,ip.ClientAddress2
    ,ip.ClientCity
    ,ip.ClientState
    ,ip.ClientPostalCode
    ,ip.ClientCountry
    ,ire.CoverageStartDate
    ,ire.CoverageEndDate
    ,ire.CoverageTerm
    ,ire.CancelDate

    ,EOMonth (DATEADD(MONTH, -1, (CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112))))

    ,ppr.reportDateYearMonth
    ,ppr.PcmCoverageId
    ,ppr.Pcmcov
    ,ppr.CoverageStatus
    ) Sub
    /************************** Potential CODE Change **************************************************/

    GROUP BY
    EnrollmentID
    ,CoverageID
    ,PSPolicyTransID
    ,PolicyNumber
    ,PolicyCode
    ,PCMPolicyID
    ,ProductName
    ,ProductCode
    ,CustomerAccountNumber
    ,CoverageTypeName
    ,AssetClassCode
    ,AssetClassDescription

    ,ClientPcmPartyId
    ,ClientAddress1
    ,ClientAddress2
    ,ClientCity
    ,ClientState
    ,ClientPostalCode
    ,ClientCountry
    ,SourceCoverageStartDate
    ,SourceCoverageEndDate
    ,SourceCoverageTerm
    --,CancelDate
    ,PeriodEndCalendar
    ,reportDateYearMonth
    ,PcmCoverageId
    ,Pcmcov
    ,(FullTermPremium)
    ,(ITDDirectWrittenPremium)
    --,MTDEarnedPremium
    ,(ITDDirectEarnedPremium)
    --,CancelledWrittenPremium
    ORDER BY CustomerAccountNumber
    ,EnrollmentID
    ,CoverageID
    ,ReportDateYearMonth

    --Increment last day of month
    SET @StartDT = DATEADD(mm, DATEDIFF(mm, 0, @EndDT) +1, -1);

    PRINT @StartDT


    END

    • This topic was modified 1 year, 1 month ago by  daniness.
    • This topic was modified 1 year, 1 month ago by  daniness.
  • ZZartin

    SSC-Dedicated

    Points: 30414

    Well how long does your process take to run for a single month?  It's going to take ~113 times longer to run for 9 and a half years.

     

    Other than that it looks like one of those inserts has a bunch of sub queries in the select, maybe try working on that.

    • This reply was modified 1 year, 1 month ago by  ZZartin.
    • This reply was modified 1 year, 1 month ago by  ZZartin.
    • This reply was modified 1 year, 1 month ago by  ZZartin.
  • drew.allen

    SSC Guru

    Points: 76735

    • You have non-SARGable formulas in your WHERE clause.
    • You call the same table multiple times when you probably only need to call it once.
    • You haven't posted the execution plan.

    Also, you have to remember that this is a volunteer website.  The longer your script is, the less likely people are to work on it.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Michael L John

    One Orange Chip

    Points: 25928

    Unless I am completely missing something, this is inside a loop that sets the values of a series of date variables that are never used in the query. For every execution in the loop, you are going to get the same results over and over.  If you are trying to restrict the results to a date range, then you do not need a loop.  You can simply add to the WHERE clause, although I am not sure what part of this I would add it to!

    You are using DISTINCT in the same query as a GROUP BY.  This is redundant.  Remove the DISTINCT.

    You are using MAX datatypes in your temp tables.  Unless the source columns are MAX types, then these should probably be changed.  If the source columns ARE max datatypes, then THEY should probably be changed.

    You are using the syntax WHERE something IN (SELECT something FROM Table GROUP BY).  You do not need the group by when you are using IN

    The CTE "unique_results" is using a group by, which makes it unique, and then your select on this is using DISTINCT. Again, this is redundant and is overhead that you do not need.

    The multiple in-line queries of the table ExtFSEPercentPremiumReported are not needed.  Join to this table once, and use case statements to get the values you want.

    Can you save the ACTUAL execution plan, and provide some additional details of what you need?  The table structures, sample data, and desired output would help significantly.

     

     

    Michael L John
    If you assassinate a DBA, would you pull a trigger?
    To properly post on a forum:
    http://www.sqlservercentral.com/articles/61537/

  • Thom A

    SSC Guru

    Points: 98644

    I very briefly read that script (as Drew said, the longer the script the less likely people are to look at it), but if you are trying to create a set of dates a WHILE loop is literally the worst way you can do it. I would strongly suggest building a Calendar Table (a Google or searching on this site will explain what those are), or using a Tally Table (which you could also use to build said Calendar Table).

    As a quick example, here's 2 queries that produce every date between 01 January 1900 and the current date; notice which one is faster:

    DECLARE @Dates table (CalendarDate date);
    DECLARE @StartDate date = '19000101';

    DECLARE @StartTime datetime2(7) = SYSDATETIME();

    WHILE @StartDate < GETDATE() BEGIN

    INSERT INTO @Dates (CalendarDate)
    VALUES(@StartDate);

    SET @StartDate = DATEADD(DAY, 1,@StartDate);

    END;
    SELECT *
    FROM @Dates;

    PRINT CONCAT(N'While Loop Time Taken = ', DATEDIFF(ms, @StartTime, SYSDATETIME()), N'ms');
    GO

    DECLARE @StartDate date = '19000101';

    DECLARE @StartTime datetime2(7) = SYSDATETIME();

    WITH N AS(
    SELECT N
    FROM (VALUES(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL),(NULL))N(N)),
    Tally AS(
    SELECT ROW_NUMBER () OVER (ORDER BY (SELECT NULL)) -1 AS I
    FROM N N1, N N2, N N3, N N4, N N5), --100000 rows
    Dates AS(
    SELECT DATEADD(DAY, T.I, @StartDate) AS CalendarDate
    FROM Tally T)
    SELECT CalendarDate
    FROM Dates D
    WHERE D.CalendarDate < GETDATE()

    PRINT CONCAT(N'Tally Table Time Taken = ', DATEDIFF(ms, @StartTime, SYSDATETIME()), N'ms');

    Output messages:

    (43608 rows affected)
    While Loop Time Taken = 3203ms

    (43608 rows affected)
    Tally Table Time Taken = 125ms

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • frederico_fonseca

    SSChampion

    Points: 14685

    and I'm not even sure the code is doing what they expect

    just a small example.

    the following inline query is summing the value of the outside table and has a group by within it - I would expect that the value being summed would be PPR7.MonthlyEarnedPremium

    not only that but on this case that inner "group by" is not required

    but the whole query should be rewritten

    ,(SELECT SUM(ppr.MonthlyEarnedPremium)
    FROM ExtFSEPercentPremiumReported AS ppr7
    WHERE ppr.Sourceid = ppr7.sourceID
    AND ppr.SourceCoveragePeriod = ppr7.SourcecoveragePeriod
    AND ppr7.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr7.PCMCoverageid
    GROUP BY ppr7.Sourceid, ppr7.SourceCoveragePeriod, ppr7.PcmCoverageId) AS MTDEarnedPremium


    FROM ExtFSEInvoicePolicy ip
    INNER JOIN ExtFSEInvoiceRiskEntity ire ON ip.PCMPolicyID = ire.PcmPolicyId
    INNER JOIN ppr ON ire.SourceId = ppr.SourceId
  • daniness

    SSCrazy

    Points: 2890

    Wow...thank you, everyone for all of your valuable feedback! It's very much appreciated! I meant to apologize for the rather lengthy query, but I was desperate to get some insight. In the interim, we were able to get the query to optimally perform and it has gone down from at least 6 hours to mere minutes! All set to release to production, knock on wood! I'll include what it is now below. Thanks again, everyone for all of your helpful suggestions!

    TRUNCATE table ##FSEDMEnrollDataZ;

    --START: AssetClassCode_by_Enrollments
    with AssetClassCode_by_Enrollments as (
    SELECT pac.AssetClassCode
    ,vec.PCMAssetClassId
    ,vec.EnrollmentID
    ,vec.CoveragePeriod AS CoverageID
    ,sum(vec.AssetValue) AS AssetValue

    FROM ExtVESEnrollmentCoverage vec

    INNER JOIN ExtPCMAssetClass pac ON vec.PCMAssetClassId = pac.AssetClassId

    GROUP BY pac.AssetClassCode
    ,vec.PCMAssetClassId
    --,pac.AssetClassDescription
    ,vec.EnrollmentID
    ,vec.CoveragePeriod)
    --END: AssetClassCode_by_Enrollments

    --START: unique_results
    ,unique_results AS (
    SELECT EnrollmentID, --AssetClassDescription,
    CoverageID,
    MAX(AssetClassCode) AS Code,
    AssetValue
    FROM AssetClassCode_by_Enrollments
    WHERE CONCAT(EnrollmentID, CoverageID, AssetValue) IN (SELECT CONCAT(EnrollmentID
    ,CoverageID
    ,MAX(AssetValue)) AssetValue
    FROM AssetClassCode_by_Enrollments
    GROUP BY EnrollmentID
    ,CoverageID)

    GROUP BY EnrollmentID,
    CoverageID,
    AssetValue)
    --END: unique_results

    --START: TempAssetClassDetails_CS2
    ,TempAssetClassDetails_CS2 AS (
    SELECT DISTINCT
    EnrollmentID
    ,CoverageID
    ,Code
    ,AssetValue
    ,(SELECT MAX(ISNULL(AssetClassDescription, 'not found in MasterAssetClass table'))
    FROM ExtPCMAssetClass pac
    WHERE pac.AssetClassCode = unique_results.code) AS AssetDescription
    from unique_results)
    --END: TempAssetClassDetails_CS2


    INSERT INTO ##FSEDMEnrollDataZ
    SELECT
    EnrollmentID
    ,CoverageID
    ,PSPolicyTransID

    ,PolicyNumber
    ,PolicyCode
    ,PCMPolicyID

    ,ProductName
    ,ProductCode
    ,CustomerAccountNumber

    ,CoverageTypeName
    ,AssetClassCode
    ,AssetClassDescription

    ,ClientPcmPartyId
    ,ClientAddress1
    ,ClientAddress2

    ,ClientCity
    ,ClientState
    ,ClientPostalCode

    ,ClientCountry
    ,SourceCoverageStartDate
    ,SourceCoverageEndDate

    ,SourceCoverageTerm
    ,PeriodEndCalendar

    ,reportDateYearMonth AS AccountingPeriod
    ,(FullTermPremium) AS FullTermPremium
    ,PcmCoverageId

    ,Pcmcov
    ,COALESCE(SUM(OriginalWrittenPremium),0) AS OriginalWrittenPremiumMTD
    ,COALESCE(SUM(CancelledWrittenPremium),0) AS CancelledWrittenPremiumMTD

    ,(ITDDirectWrittenPremium) AS ITDDirectWrittenPremium
    ,sum(MTDEarnedPremium) AS MTDEarnedPremium
    ,(ITDDirectEarnedPremium) AS ITDDirectEarnedPremium
    FROM
    (
    SELECT DISTINCT

    ppr.SourceId AS EnrollmentID
    ,ppr.SourceCoveragePeriod AS CoverageID
    ,convert (nvarchar,ept.PSPolicyTransID) AS PSPolicyTransID
    --,ept.PSPolicyTransID AS PSPolicyTransID
    ,ip.PolicyNumber
    ,ire.SourceTransactionId
    ,ip.PolicyCode
    ,ip.PCMPolicyID
    ,ip.ProductName
    ,pp.ProductCode
    ,ire.ContractNumber as CustomerAccountNumber
    ,UPPER(psc.PolicySchemaCoverageName) AS CoverageTypeName
    ,UPPER(TACD.Code) AS AssetClassCode
    ,UPPER(TACD.AssetDescription) AS AssetClassDescription
    ,ip.ClientPcmPartyId
    ,ip.ClientAddress1
    ,ip.ClientAddress2
    ,ip.ClientCity
    ,ip.ClientState
    ,ip.ClientPostalCode
    ,ip.ClientCountry
    ,ire.CoverageStartDate AS SourceCoverageStartDate
    ,ire.CoverageEndDate AS SourceCoverageEndDate
    ,ire.CoverageTerm AS SourceCoverageTerm
    ,ire.CancelDate

    ,EOMonth (DATEADD(MONTH, -1, (CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112)))) AS PeriodEndCalendar
    ,ppr.reportDateYearMonth
    ,ppr.PcmCoverageId
    ,ppr.Pcmcov
    ,ppr.CoverageStatus
    ,(SELECT SUM(ppr4.earnablePremium)
    FROM ExtFSEPercentPremiumReported AS ppr4
    WHERE ppr.Sourceid = ppr4.sourceID
    AND ppr.SourceCoveragePeriod = ppr4.SourcecoveragePeriod
    AND ppr4.ReportDateYearMonth = ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr4.PCMCoverageid
    --AND ppr.YearMonth = ppr4.YearMonth
    GROUP BY ppr4.Sourceid, ppr4.SourceCoveragePeriod, ppr4.PcmCoverageId) AS FullTermPremium

    ,(SELECT SUM(ppr.WrittenPremium) --Updated source (3/26/2019)
    FROM ExtFSEPercentPremiumReported AS ppr5
    WHERE ppr.Sourceid = ppr5.sourceID
    AND ppr.SourceCoveragePeriod = ppr5.SourcecoveragePeriod
    AND ppr5.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr5.PCMCoverageid
    AND ppr.CoverageStatus in ('Active', 'Matured')
    AND ire.CancelDate IS NULL
    GROUP BY ppr5.Sourceid, ppr5.SourceCoveragePeriod, ppr5.PcmCoverageId) AS OriginalWrittenPremium

    ,(SELECT SUM(ppr.CanceledPremium)
    FROM ExtFSEPercentPremiumReported AS ppr6
    WHERE ppr.Sourceid = ppr6.sourceID
    AND ppr.SourceCoveragePeriod = ppr6.SourcecoveragePeriod
    AND ppr6.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr6.PCMCoverageid
    AND ppr.CoverageStatus in ('Active','Cancelled')
    --AND ire.CancelDate IS NOT NULL --Removed for cancel adjustments from FSE
    GROUP BY ppr6.Sourceid, ppr6.SourceCoveragePeriod, ppr6.PcmCoverageId) AS CancelledWrittenPremium

    ,(SELECT SUM(ppr2.writtenPremium + ppr2.canceledPremium)
    FROM ExtFSEPercentPremiumReported AS ppr2
    WHERE ppr.Sourceid = ppr2.sourceID
    AND ppr.SourceCoveragePeriod = ppr2.SourcecoveragePeriod
    AND ppr2.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr2.PCMCoverageid
    GROUP BY ppr2.Sourceid, ppr2.SourceCoveragePeriod, ppr2.PcmCoverageId) AS ITDDirectWrittenPremium --also GrossWrittenPremium b/c there's no AssumedPremium; this is also NetWrittenPremium, as there's no Ceded

    --,sum(ppr.MonthlyEarnedPremium) as MTDEarnedPremium
    ,(SELECT SUM(ppr.MonthlyEarnedPremium)
    FROM ExtFSEPercentPremiumReported AS ppr7
    WHERE ppr.Sourceid = ppr7.sourceID
    AND ppr.SourceCoveragePeriod = ppr7.SourcecoveragePeriod
    AND ppr7.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr7.PCMCoverageid
    GROUP BY ppr7.Sourceid, ppr7.SourceCoveragePeriod, ppr7.PcmCoverageId) AS MTDEarnedPremium

    ,(SELECT SUM(ppr3.MonthlyEarnedPremium)
    FROM ExtFSEPercentPremiumReported AS ppr3
    WHERE ppr.Sourceid = ppr3.sourceID
    AND ppr.SourceCoveragePeriod = ppr3.SourcecoveragePeriod
    AND ppr3.ReportDateYearMonth <= ppr.reportDateYearMonth
    AND PPR.PCMCoverageid = ppr3.PCMCoverageid
    GROUP BY ppr3.Sourceid, ppr3.SourceCoveragePeriod, ppr3.PcmCoverageId) AS ITDDirectEarnedPremium

    FROM ExtFSEInvoicePolicy ip
    INNER JOIN ExtFSEInvoiceRiskEntity ire ON ip.PCMPolicyID = ire.PcmPolicyId
    INNER JOIN
    (
    SELECT
    [SourceId]
    ,[SourceCoveragePeriod]
    ,sum([EarnablePremium]) as EarnablePremium
    ,sum([EarnedPremium]) as EarnedPremium
    ,sum([MonthlyEarnedPremium]) as MonthlyEarnedPremium
    ,[LastMonthEarningsId]
    ,[PcmProducerId]
    ,[ReportDateYearMonth]
    ,sum([WrittenPremium]) as WrittenPremium
    ,sum([CanceledPremium]) as CanceledPremium
    ,[RiskEntityId]
    ,[PcmCoverageId]
    ,[PcmCoverageId] as pcmcov --duplicated so i could use in query above as a group by w/o won't work if use PcmCoverageID i/o copying?
    ,[PcmPolicyId]
    ,[CoverageStatus]
    FROM [dbo].[EXTFSEPercentPremiumReported]
    group by
    [SourceId]
    ,[SourceCoveragePeriod]
    ,[LastMonthEarningsId]
    ,[PcmProducerId]
    ,[ReportDateYearMonth]
    ,[RiskEntityId]
    ,[PcmCoverageId]
    ,[PcmCoverageId]
    ,[PcmPolicyId]
    ,[CoverageStatus]
    ) ppr ON ire.SourceId = ppr.SourceId
    AND ire.SourceCoveragePeriod = ppr.SourceCoveragePeriod
    AND ire.riskentityid = ppr.RiskEntityId
    INNER JOIN ExtFSELastMonthEarnings lme ON ppr.PcmPolicyId = lme.PcmPolicyId
    AND ppr.ReportDateYearMonth = lme.CurrentAccountingYearMonth
    AND lme.approvaldate IS NOT NULL
    INNER JOIN ExtPCMPolicySchemaCoverage psc ON ppr.pcmcoverageid = psc.PolicySchemaCoverageId
    INNER JOIN ExtPCMProduct pp ON ip.ProductName = pp.ProductName

    INNER JOIN TempAssetClassDetails_CS2 AS TACD ON ire.sourceid = TACD.EnrollmentID AND ire.SourceCoveragePeriod = TACD.CoverageID
    INNER JOIN ExtVESEnrollmentPolicyTrans ept ON Convert(int, ire.SourceId) = ept.EnrollmentID
    AND Convert(int, ire.SourceCoveragePeriod) = ept.CoverageID --To include PolicyTransID field

    WHERE ppr.WrittenPremium + ppr.CanceledPremium + ppr.MonthlyEarnedPremium <> 0
    GROUP BY ppr.SourceId
    ,ppr.SourceCoveragePeriod
    ,ept.PSPolicyTransID
    ,ip.PolicyNumber
    ,ire.SourceTransactionId
    ,ip.PolicyCode
    ,ip.PCMPolicyID
    ,ip.ProductName
    ,pp.ProductCode
    ,ire.ContractNumber
    ,UPPER(psc.PolicySchemaCoverageName)
    ,UPPER(TACD.Code)
    ,UPPER(TACD.AssetDescription)
    ,ip.ClientPcmPartyId
    ,ip.ClientAddress1
    ,ip.ClientAddress2
    ,ip.ClientCity
    ,ip.ClientState
    ,ip.ClientPostalCode
    ,ip.ClientCountry
    ,ire.CoverageStartDate
    ,ire.CoverageEndDate
    ,ire.CoverageTerm
    ,ire.CancelDate
    ,EOMonth (DATEADD(MONTH, -1, (CONVERT(date, CAST(ppr.ReportDateYearMonth AS char(6))+'01', 112))))
    ,ppr.reportDateYearMonth
    ,ppr.PcmCoverageId
    ,ppr.Pcmcov
    ,ppr.CoverageStatus
    ) Sub

    GROUP BY
    EnrollmentID
    ,CoverageID
    ,PSPolicyTransID
    ,PolicyNumber
    ,PolicyCode
    ,PCMPolicyID
    ,ProductName
    ,ProductCode
    ,CustomerAccountNumber
    ,CoverageTypeName
    ,AssetClassCode
    ,AssetClassDescription
    ,ClientPcmPartyId
    ,ClientAddress1
    ,ClientAddress2
    ,ClientCity
    ,ClientState
    ,ClientPostalCode
    ,ClientCountry
    ,SourceCoverageStartDate
    ,SourceCoverageEndDate
    ,SourceCoverageTerm
    --,CancelDate
    ,PeriodEndCalendar
    ,reportDateYearMonth
    ,PcmCoverageId
    ,Pcmcov
    ,(FullTermPremium)
    ,(ITDDirectWrittenPremium)
    ,(ITDDirectEarnedPremium)
    --END: ##FSEDMEnrollDataZ

     

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply