May 23, 2019 at 4:35 pm
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
May 23, 2019 at 4:55 pm
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.
May 23, 2019 at 6:54 pm
WHERE clause.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
May 23, 2019 at 7:43 pm
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/
May 24, 2019 at 8:08 am
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.
Larnu.uk
May 24, 2019 at 8:17 am
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
May 28, 2019 at 8:07 pm
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 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply