I have a query that uses a lot of joins, subqueries and temp tables (I inherited it and am leary about rewriting it). I tried to rewrite it using table variables at one point, but the run time just got ridiculous for any query spanning more than a few months.
It currently runs as-is in about 3 minutes or less in Management Studio; however, when I try to run it via SRS or the Visual Studios 2005 Designer it runs indefinitely. It also runs indefinitely if I try to refresh the fileds or run it from the data tab (basically any time it has to call the stored procedure).
Does anyone have an idea of how I can get this report to run or know why it runs differently in SRS than in Management Studio?
Here is the very long procedure:
CREATE PROCEDURE [dbo].[ISP_RPT_RENEWAL_RETENTION_TEST]
(
@.FROM DATETIME,
@.TO DATETIME,
@.ASOF DATETIME,
@.REPORT VARCHAR(10) = 'ADHOC')
AS
SET NOCOUNT ON
--DECLARE @.FROM DATETIME
--DECLARE @.TO DATETIME
--DECLARE @.ASOF DATETIME
--DECLARE @.REPORT VARCHAR(10)
--
--SET @.REPORT = 'YTD'
--SET @.FROM = '1/1/2006'
--SET @.TO = '10/31/2006'
--SET @.ASOF = '10/31/2006'
IF (@.REPORT = 'ADHOC' OR @.REPORT IS NULL OR @.REPORT = '')
BEGIN
--Check to see if user has input data
--First of the previous month
IF (@.FROM IS NULL OR @.FROM = '') SET @.FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1
IF (@.TO IS NULL OR @.TO = '') SET @.TO = dbo.PreviousMonthEndDate(GetDate())
IF (@.ASOF IS NULL OR @.ASOF = '') SET @.ASOF = dbo.PreviousMonthEndDate(GetDate())
END
ELSE IF @.REPORT = 'MTD'
BEGIN
--Disregard user input and run for MTD
--First of the previous month
SET @.FROM = dbo.PreviousMonthEndDate(dbo.PreviousMonthEndDate(GetDate()))+1
SET @.TO = dbo.PreviousMonthEndDate(GetDate())
SET @.ASOF = dbo.PreviousMonthEndDate(GetDate())
END
ELSE IF @.REPORT = 'YTD'
BEGIN
--Disregard user input and run for YTD
SET @.FROM = dbo.FirstOfTheYear(GetDate())
SET @.TO = dbo.PreviousMonthEndDate(GetDate())
SET @.ASOF = dbo.PreviousMonthEndDate(GetDate())
END
SET @.FROM = dbo.MidnightDate(@.FROM)
SET @.TO = dbo.MidnightDate(@.TO)
SET @.ASOF = dbo.MidnightDate(@.ASOF)
/*** if user runs report before month end is closed, then use data from premium table
if however user runs report after month end close, then pull from reinsurance table ***/
DECLARE @.CHECKDATE DATETIME
SELECT @.CHECKDATE = MAX(MONTHENDDATE) FROM MPATREINSURANCE
WHERE MONTHENDDATE = @.TO
/*********************************************** IDENTIFY ALL RENEWALS ***************************************************/
SELECT DISTINCT
RC.POLICYNO POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,
MCITCUST.FULLNAME ,MAX(ISNULL(RC1.POLICYNO,RC.PREVIOUSPOLICYNO)) PREVIOUSPOLICYNO ,RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS PMRSTATUS,V.COVERAGE
INTO #MPAV_RENEWALS
FROM MPATPMRC RC
INNER JOIN MCITCUST ON
RC.COMPANYID = MCITCUST.COMPANYID AND
RC.INSUREDNAMENO = MCITCUST.NAMENO
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V.POLICYID = RC.POLICYID AND
V.PMRSEQUENCE = RC.PMRSEQUENCE AND
V.SUBCOMPANYID = RC.SUBCOMPANYID
INNER JOIN MCITCUST M1 ON
MCITCUST.COMPANYID =M1.COMPANYID AND
MCITCUST.CURRENTNAMENO = M1.CURRENTNAMENO
INNER JOIN MPATPMRC RC1 ON
RC1.COMPANYID = M1.COMPANYID AND
RC1.INSUREDNAMENO = M1.NAMENO
LEFT OUTER JOIN MPATCGLUWVERSIONS V1 ON
V1.POLICYID = RC1.POLICYID AND
V1.PMRSEQUENCE = RC1.PMRSEQUENCE AND
V1.SUBCOMPANYID = RC1.SUBCOMPANYID
WHERE
RC.POLICYTYPE ='CGL' AND
RC.POLICYEFFDATE >= '1/1/1998' AND
--FIND THE VERY FIRST VALID TRANSACTION BY LOOKING AT WHICH ONE OF RN/NB/RW IS THE LATEST
RC.TXNISSUED <= @.ASOF AND
RC1.TXNISSUED <= @.ASOF AND
RC.PMRSEQUENCE = (SELECT MAX(PMRSEQUENCE) FROM MPATPMRC RC2
WHERE RC2.POLICYID = RC.POLICYID AND
RC2.SUBCOMPANYID = RC.SUBCOMPANYID AND
RC2.POLICYYEAR = RC.POLICYYEAR AND
RC2.TXNISSUED <= @.ASOF AND
RC2.TXNTYPE IN ('RN','NB','RW','RE') AND
( (RC2.POLICYNO = RC.POLICYNO AND RC.POLICYNO IS NOT NULL)
OR (RC.POLICYNO IS NULL)
) AND
RC2.PMRSTATUS IN ('HS', 'EX', 'IN','PC','PA')AND
RC2.PROCESSINGSTATUS IN ('*','P','A','I','Q')
)
AND ((RC1.TXNTYPE ='CN' AND RC1.CANCELMETHOD <> 'F') OR (RC1.CANCELMETHOD IS NULL OR RC1.TXNTYPE <> 'CN'))
AND RC1.PMRSEQUENCE = (
SELECT MAX(PMRSEQUENCE) FROM MPATPMRC
WHERE MPATPMRC.POLICYID = RC1.POLICYID AND
MPATPMRC.POLICYNO = RC1.POLICYNO AND
MPATPMRC.TXNISSUED <= @.ASOF AND
MPATPMRC.PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND
MPATPMRC.PROCESSINGSTATUS IN ('*','C')AND
MPATPMRC.TXNTYPE NOT IN ('EA','ER' )) AND
(
(RC1.TXNTYPE <>'CN' AND RC1.POLICYEXPDATE = RC.POLICYEFFDATE ) OR
(RC1.TXNTYPE ='CN' AND RC1.TXNEFFECTIVE = RC.POLICYEFFDATE )
)
AND (V.COVERAGE = 'B' AND V1.COVERAGE = 'P' OR V.COVERAGE = 'P' AND V1.COVERAGE = 'B' OR
V.COVERAGE = V1.COVERAGE)
GROUP BY RC.POLICYNO , MCITCUST.CURRENTNAMENO,RC.POLICYID, RC.PMRSEQUENCE, RC.SUBCOMPANYID, RC.POLICYEFFDATE,
MCITCUST.FULLNAME , RC.INSUREDNAMENO,RC.PROCESSINGSTATUS, RC.PMRSTATUS ,V.COVERAGE
/**************************************FIND LAST VERSION*********************************/
SELECT
RC.POLICYNO,
RC.POLICYYEAR,
RC.POLICYID,
RC.SUBCOMPANYID,
RC.PMRSEQUENCE,
RC.TXNTYPE,
POLICYEXPDATE = CASE WHEN RC.TXNTYPE = 'CN' THEN RC.TXNEFFECTIVE ELSE RC.POLICYEXPDATE END,
REPORTDATES =CASE
WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE <= RC.TXNISSUED THEN RC.TXNISSUED
WHEN RC.TXNTYPE ='CN' AND RC.TXNEFFECTIVE > RC.TXNISSUED THEN RC.TXNEFFECTIVE
ELSE RC.POLICYEXPDATE END,
CN_REASON = CASE WHEN RC.TXNTYPE = 'CN' THEN
(CASE WHEN RC.TXNSUBTYPE IN ('PF','NP') THEN 'Non-pay'
WHEN RC.TXNSUBTYPE IN ('IN') THEN 'Ins Request'
WHEN RC.TXNSUBTYPE IN ('UW') THEN 'UW Reasons' ELSE 'Other' END)
Else '' END,
CN_METHOD = CASE WHEN RC.TXNTYPE = 'CN' THEN CANCELMETHOD ELSE '' END,
INS.FULLNAME INSURED,
RC.INSUREDNAMENO,
INS.CURRENTNAMENO,
RC.STATE,
RC.ORIGINCEPTIONDATE
INTO #LAST_VERSION
FROM MPATPMRC RC
INNER JOIN --LOOKING UP THE LATEST VERSION OF POLICIES
(SELECT DISTINCT POLICYNO, POLICYID, SUBCOMPANYID, MAX( PMRSEQUENCE) LASTPMR
FROM MPATPMRC
WHERE
POLICYTYPE ='CGL' AND
PMRSTATUS IN ('HS', 'EX', 'IN','CN') AND
PROCESSINGSTATUS IN ('*','C') AND
TXNTYPE NOT IN ('EA','ER')
AND MPATPMRC.TXNISSUED <= @.ASOF
GROUP BY POLICYNO, POLICYID, SUBCOMPANYID) LV ON
RC.POLICYNO = LV.POLICYNO AND
RC.SUBCOMPANYID = LV.SUBCOMPANYID AND
RC.POLICYID = LV.POLICYID AND
RC.PMRSEQUENCE = LV.LASTPMR
LEFT OUTER JOIN MCITCUST INS ON
RC.INSUREDNAMENO = INS.NAMENO
/*=============================================IDENTIFY RENEWALS FOR THE PERIOD==============================*/
SELECT R.POLICYNO,
R.CURRENTNAMENO,
R.POLICYID,
R.PMRSEQUENCE,
R.SUBCOMPANYID,
R.POLICYEFFDATE,
R.FULLNAME,
R.PREVIOUSPOLICYNO,
R.INSUREDNAMENO,
R.PROCESSINGSTATUS,
R.PMRSTATUS,
R.COVERAGE
INTO #RENEWALS
FROM #MPAV_RENEWALS R
LEFT OUTER JOIN #LAST_VERSION LV ON
R.POLICYNO = LV.POLICYNO
--take off pendings
WHERE LV.CN_METHOD <> 'F' AND
POLICYEFFDATE BETWEEN @.FROM AND @.TO
/* LETS GET RENEWAL BROKER */
SELECT
R.POLICYNO,
P.NAMENO,
C.FULLNAME,
A.CITY
INTO #RN_BRK
FROM #RENEWALS R
INNER JOIN MPATPRODUCERS P ON
R.POLICYID = P.POLICYID AND
R.PMRSEQUENCE = P.PMRSEQUENCE AND
R.SUBCOMPANYID = P.SUBCOMPANYID AND
PAYOR = 1
INNER JOIN MCITCUST C ON
P.NAMENO = C.NAMENO
INNER JOIN MCITADDR A ON
C.MAILINGADDRESS = A.ADDRESSID
/*==============================================GET RENEWING PREMIUMS=========================================*/
--this gives us written premium as of report date
SELECT DISTINCT
R.POLICYNO,
R.POLICYID,
MAX(R.SUBCOMPANYID) SUB,
MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,
MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,
MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,
MAX(MPATPMRC.UNDERWRITER)UW,
RN_DIRECT = SUM(WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),
RN_CEDED_REIN = SUM(WRITTENPREM3),
CEDED_RETAINED = 0
INTO #RN_PREM
FROM #RENEWALS R
LEFT OUTER JOIN MPATPMRC MPATPMRC ON
MPATPMRC.POLICYNO = R.POLICYNO
LEFT OUTER JOIN MPATPREMIUMS MPATPREMIUMS ON
MPATPMRC.COMPANYID = MPATPREMIUMS.COMPANYID AND
MPATPMRC.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND
MPATPMRC.POLICYID = MPATPREMIUMS.POLICYID AND
MPATPMRC.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID
WHERE @.CHECKDATE IS NULL AND
MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND
((MPATPMRC.TXNISSUED >= MPATPMRC.TXNEFFECTIVE AND MPATPMRC.TXNISSUED BETWEEN @.FROM AND @.TO) OR
(MPATPMRC.TXNEFFECTIVE >= MPATPMRC.TXNISSUED AND MPATPMRC.TXNEFFECTIVE BETWEEN @.FROM AND @.TO))
GROUP BY R.POLICYNO , R .POLICYID
UNION ALL
--this gives us written premium as of report date
SELECT DISTINCT
R.POLICYNO,
R.POLICYID,
MAX(R.SUBCOMPANYID) SUB,
MAX(MPATPMRC.POLICYEXPDATE) EXPDATE,
MAX(R.PREVIOUSPOLICYNO) EXPIRED_POLICY,
MAX(MPATPMRC.POLICYYEAR) POLICYYEAR,
MAX(MPATPMRC.UNDERWRITER) UW,
RN_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,
RN_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,
CEDED_RETAINED= SUM(
(CASE WHEN CEDEDPERCENT1 <> 0 THEN
((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT2 <> 0 THEN
((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT3 <> 0 THEN
((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)
)
FROM #RENEWALS R
LEFT OUTER JOIN MPATPMRC MPATPMRC ON
MPATPMRC.POLICYNO = R.POLICYNO
LEFT OUTER JOIN MPATREINSURANCE MPATREINSURANCE ON
MPATPMRC.COMPANYID = MPATREINSURANCE.COMPANYID AND
MPATPMRC.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND
MPATPMRC.POLICYID = MPATREINSURANCE.POLICYID AND
MPATPMRC.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID AND
MPATPMRC.TREATYID = MPATREINSURANCE.TREATYID
WHERE @.CHECKDATE IS NOT NULL AND
MPATPMRC.PMRSTATUS NOT LIKE '%P%' AND
((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED BETWEEN @.FROM AND @.TO) OR
(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE BETWEEN @.FROM AND @.TO))
GROUP BY R.POLICYNO , R .POLICYID
/*======================= looks for all policies EXPIRED during report time==========================*/
SELECT
LV.POLICYYEAR,
LV.POLICYNO,
LV.POLICYID,
LV.SUBCOMPANYID,
LV.PMRSEQUENCE,
LV.TXNTYPE,
LV.POLICYEXPDATE,
REASONDROPPED =
CASE WHEN LV.TXNTYPE = 'CN' AND CN_REASON = 'Non-pay' AND R.DESCRIPTION IS NULL
THEN 'Cancelled for non pay'
ELSE
ISNULL(ISNULL(R.DESCRIPTION, CLOSEREASONDESC),'No reason entered') END,
CN_METHOD,
V.COVERAGE,
CURRENTNAMENO,
INSUREDNAMENO,
INSURED,
LV.STATE,
ORIGINCEPTIONDATE,
CLOSECATEGORYDESC,
CLOSEREASONDESC,
COMPETITORDESC,
MKTFOLLOWUPDESC
INTO #EXPIRED
FROM #LAST_VERSION LV
LEFT OUTER JOIN MCITINSURED ON
LV.INSUREDNAMENO = MCITINSURED.NAMENO
LEFT OUTER JOIN MSOTDROPREAS R ON
MCITINSURED.REASONDROPPED = R.REASONID
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V.POLICYID = LV.POLICYID AND
V.PMRSEQUENCE = LV.PMRSEQUENCE AND
V.SUBCOMPANYID = LV.SUBCOMPANYID
LEFT OUTER JOIN MSOTCLOSECATEGORIES CC ON
MCITINSURED.CLOSECATEGORYID = CC.CLOSECATEGORYID
LEFT OUTER JOIN MSOTCLOSEREASONS CR ON
MCITINSURED.CLOSEREASONID = CR.CLOSEREASONID
LEFT OUTER JOIN MSOTCOMPETITORS CO ON
MCITINSURED.COMPETITORID = CO.COMPETITORID
LEFT OUTER JOIN MSOTMKTFOLLOWUP MK ON
MCITINSURED.MKTFOLLOWUPID = MK.MKTFOLLOWUPID
WHERE REPORTDATES BETWEEN CAST(CAST(@.FROM AS VARCHAR(11)) AS DATETIME) --DF CHANGE
AND CAST(CAST(@.TO AS VARCHAR(11)) AS DATETIME)
/*==============================================GET EXPIRED POLICY INFO=========================================*/
--GET INFO FOR ALL TXNS SO U ALSO GET THE PREMIUMS
SELECT DISTINCT
MAX(MCITCUST.NAMENO) NAMENO,
R.RISKSEQUENCE,
MAX(MCITCUST.CURRENTNAMENO) CURRENTNAMENO,
MAX(MCITCUST.FULLNAME) FULLNAME,
RC.POLICYNO,
RC.POLICYID,
MAX(RC.SUBCOMPANYID) SUB,
MAX(RC.POLICYYEAR) POLICYYEAR,
EXPDATE = MAX(EX.POLICYEXPDATE),
SALES = SUM(CASE WHEN PR.COVERAGETYPE ='P' AND PR.PMRSEQUENCE = EX.PMRSEQUENCE THEN ISNULL(PR.SALES,0) ELSE 0 END),
MAX(RC.UNDERWRITER) UW,
P.HAZARDCLASS,
P.CLASSCODE PRODUCTCODE,
MAX(P.DESCRIPTION) [DESCRIPTION],
DISTRIBUTOR = (CASE WHEN P.DISTRIBUTOR = 1 THEN 'YES' ELSE 'NO' END),--R.PRODUCTCODE
MEMBER = MAX(CASE WHEN MEMBER = 1 THEN 'DEVICE' ELSE 'EXP' END),
SUM(WRITTENPREM1 + WRITTENPREM2) LAYER1_2,
EX_LIMIT = MAX(CASE WHEN V.COV_PROD_AGGREGATE = '1000000' OR V.COV_CGL_AGGREGATE = '1000000' THEN 1
WHEN V.COV_PROD_AGGREGATE = '2000000' OR V.COV_CGL_AGGREGATE = '2000000' THEN 2
WHEN V.COV_PROD_AGGREGATE = '3000000' OR V.COV_CGL_AGGREGATE = '3000000' THEN 3
WHEN V.COV_PROD_AGGREGATE = '4000000' OR V.COV_CGL_AGGREGATE = '4000000' THEN 4
WHEN V.COV_PROD_AGGREGATE = '5000000' OR V.COV_CGL_AGGREGATE = '5000000' THEN 5
WHEN V.COV_PROD_AGGREGATE = '6000000' OR V.COV_CGL_AGGREGATE = '6000000' THEN 6
WHEN V.COV_PROD_AGGREGATE = '7000000' OR V.COV_CGL_AGGREGATE = '7000000' THEN 7
WHEN V.COV_PROD_AGGREGATE = '8000000' OR V.COV_CGL_AGGREGATE = '8000000' THEN 8
WHEN V.COV_PROD_AGGREGATE = '9000000' OR V.COV_CGL_AGGREGATE = '9000000' THEN 9
WHEN V.COV_PROD_AGGREGATE = '10000000' OR V.COV_CGL_AGGREGATE = '10000000' THEN 10 END),
EX_BROKER_GROUP =
(SELECT [DESCRIPTION] FROM MSOTCOMMPLAN
WHERE
SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND
COMMISSIONPLAN =
(SELECT TOP 1 COMMISSIONPLAN FROM MAGTCOMMPLAN
WHERE NAMENO = MPATPRODUCERS.NAMENO AND SUBCOMPANYID =MPATPRODUCERS.SUBCOMPANYID AND
COMMRATEEFFDATE <= EX.POLICYEXPDATE
ORDER BY COMMRATEEFFDATE DESC )),
MAX(BROKER.FULLNAME) BRK_NAME,
MAX(MPATPRODUCERS.COMMISSIONRATE * 100) RATE,
MAX(AA.CITY) BRK_CITY
INTO #EX
FROM MPATPMRC RC
INNER JOIN MPATPREMIUMS PR ON
RC.POLICYID = PR.POLICYID AND
RC.SUBCOMPANYID = PR.SUBCOMPANYID AND
RC.PMRSEQUENCE = PR.PMRSEQUENCE
INNER JOIN #EXPIRED EX ON
EX.POLICYNO = RC.POLICYNO
INNER JOIN MCITCUST ON
RC.COMPANYID = MCITCUST.COMPANYID AND
RC.INSUREDNAMENO = MCITCUST.NAMENO
LEFT OUTER JOIN MPATCGLUWRISKS R ON
PR.RISKSEQUENCE = R.RISKSEQUENCE AND
EX.POLICYID = R.POLICYID AND
EX.PMRSEQUENCE = R.PMRSEQUENCE AND
EX.SUBCOMPANYID = R.SUBCOMPANYID
LEFT OUTER JOIN MSOTPRODUCTS P ON
R.PRODUCTCODE = P.PRODUCTCODE
LEFT OUTER JOIN MPATCGLUWVERSIONS V ON
V .POLICYID = EX.POLICYID AND
V .PMRSEQUENCE =EX.PMRSEQUENCE AND
V .SUBCOMPANYID = EX.SUBCOMPANYID
INNER JOIN MPATPRODUCERS ON
EX.SUBCOMPANYID = MPATPRODUCERS.SUBCOMPANYID AND
EX.POLICYID = MPATPRODUCERS.POLICYID AND
EX.PMRSEQUENCE = MPATPRODUCERS.PMRSEQUENCE AND
MPATPRODUCERS.PAYOR = 1
INNER JOIN MCITCUST BROKER ON
MPATPRODUCERS.COMPANYID = BROKER.COMPANYID AND
MPATPRODUCERS.NAMENO = BROKER.NAMENO
LEFT OUTER JOIN MCITADDR AA ON
BROKER.MAILINGADDRESS = AA.ADDRESSID
WHERE RC.PMRSTATUS IN ('IN','EX','HS','CN') AND
RC.PROCESSINGSTATUS IN ('*','C')
GROUP BY R.RISKSEQUENCE ,RC.POLICYNO, RC.POLICYID,P.HAZARDCLASS, P.CLASSCODE,
MPATPRODUCERS.SUBCOMPANYID, MPATPRODUCERS.NAMENO, EX.POLICYEXPDATE,P.DISTRIBUTOR
ORDER BY RC.POLICYNO
/*****************************************************************************************************************/
SELECT
R.POLICYNO,
EX_DIRECT = SUM( WRITTENPREM1 + WRITTENPREM2 + WRITTENPREM3),
EX_CEDED_REIN = SUM(WRITTENPREM3),
EX_CEDED_RETAINED= 0
INTO #EX_PREM
FROM MPATPMRC R
INNER JOIN #EXPIRED E ON
R.POLICYNO = E.POLICYNO
LEFT OUTER JOIN MPATPREMIUMS ON
R.PMRSEQUENCE = MPATPREMIUMS.PMRSEQUENCE AND
R.POLICYID = MPATPREMIUMS.POLICYID AND
R.SUBCOMPANYID = MPATPREMIUMS.SUBCOMPANYID
WHERE @.CHECKDATE IS NULL AND
((R.TXNISSUED >= R.TXNEFFECTIVE AND R.TXNISSUED <= @.TO) OR
(R.TXNEFFECTIVE >= R.TXNISSUED AND R.TXNEFFECTIVE <= @.TO))
GROUP BY R.POLICYNO
UNION ALL
SELECT
R.POLICYNO,
EX_DIRECT = SUM(MPATREINSURANCE.WRITTENPREM1 + MPATREINSURANCE.WRITTENPREM2 + MPATREINSURANCE.WRITTENPREM3) ,
EX_CEDED_REIN = SUM(MPATREINSURANCE.CEDEDAMOUNT1 + MPATREINSURANCE.CEDEDAMOUNT2 + MPATREINSURANCE.CEDEDAMOUNT3) ,
EX_CEDED_RETAINED=
SUM(
(CASE WHEN CEDEDPERCENT1 <> 0 THEN
((1-CEDEDPERCENT1) * WRITTENPREM1) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT2 <> 0 THEN
((1-CEDEDPERCENT2) * WRITTENPREM2) ELSE 0 END)
+
(CASE WHEN CEDEDPERCENT3 <> 0 THEN
((1-CEDEDPERCENT3) * WRITTENPREM3) ELSE 0 END)
)
FROM MPATPMRC R
INNER JOIN #EXPIRED E ON
R.POLICYNO = E.POLICYNO
LEFT OUTER JOIN MPATREINSURANCE ON
R.PMRSEQUENCE = MPATREINSURANCE.PMRSEQUENCE AND
R.POLICYID = MPATREINSURANCE.POLICYID AND
R.SUBCOMPANYID = MPATREINSURANCE.SUBCOMPANYID
WHERE @.CHECKDATE IS NOT NULL AND
((MPATREINSURANCE.TXNISSUED >= MPATREINSURANCE.TXNEFFECTIVE AND MPATREINSURANCE.TXNISSUED <= @.TO) OR
(MPATREINSURANCE.TXNEFFECTIVE >= MPATREINSURANCE.TXNISSUED AND MPATREINSURANCE.TXNEFFECTIVE <= @.TO))
GROUP BY R.POLICYNO
/******************************************************************************************************************/
EXECUTE ISP_RPT_MKTSEGMENT
SET NOCOUNT OFF
SELECT
E.POLICYNO EXP_POLICY,
E.POLICYEXPDATE,
A.UW,
E.INSURED INS_NAME,
STATE INS_STATE,
BROKER,
BROKER_GROUP = CASE WHEN BROKER_GRP LIKE '%Others%' THEN 'Others'
WHEN BROKER_GRP LIKE '%Preferred%' THEN 'Preferred'
WHEN BROKER_GRP LIKE '%Marsh%' THEN 'Marsh'
WHEN BROKER_GRP LIKE '%Champion%' THEN 'Champion'
ELSE 'Others' END, COMM_RATE,
DOMINANT_PROD DOM_PROD,
DOMINANT_PROD_DESC DESCR,
MK.DISTRIBUTOR,
DOMINANT_HAZ DOM_HAZD,
EX_LIMIT,
MEMBER,
A.SALES,
MKT_SEGMENT,
ISNULL(EX_DIRECT,0) TOTAL_WRTTN,
(ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0)) TOTAL_CEDED,
CEDED_REINSURE = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_REIN,0) END ,
CEDED_RETAINED = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_CEDED_RETAINED,0) END ,
NWP_2M = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ((ISNULL(EX_CEDED_REIN,0) + ISNULL(EX_CEDED_RETAINED,0))) END,
XOL_NWP = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(EX_DIRECT,0) - ISNULL(EX_CEDED_REIN,0) END,
REASON_LOST =
CASE WHEN R.POLICYNO IS NULL THEN
(SELECT 'LOST -'+REASONDROPPED FROM #EXPIRED WHERE POLICYNO = E.POLICYNO)
ELSE '' END,
ISNULL(R.POLICYNO, '') RN_POLICY,
ISNULL(RN_DIRECT,0) RN_TOTAL_WRTTN,
(ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0)) RN_TOTAL_CEDED,
RN_CEDED_REINSURE = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_CEDED_REIN,0) END ,
RN_CEDED_RETAINED = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(CEDED_RETAINED,0) END ,
RN_NWP_2M = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ((ISNULL(RN_CEDED_REIN,0) + ISNULL(CEDED_RETAINED,0))) END,
RN_XOL_NWP = CASE WHEN @.CHECKDATE IS NULL THEN 0 ELSE ISNULL(RN_DIRECT,0) - ISNULL(RN_CEDED_REIN,0) END,
[INSURED INFORCE TODAY?] = CASE WHEN (SELECT COUNT(*)
FROM MPATPMRC
WHERE PMRSTATUS ='IN' AND
INSUREDNAMENO IN
(SELECT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)
)>0 THEN 'YES' ELSE 'NO' END,
[FIRST_JOINED_DATE] =
ISNULL(
(SELECT MIN(DATEJOINED) FROM MCITINSURED
WHERE NAMENO IN
(SELECT DISTINCT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)), ORIGINCEPTIONDATE),
[# OF POLICY YEARS] = (
SELECT COUNT(DISTINCT POLICYYEAR)
FROM MPATPMRC
WHERE PMRSTATUS IN ('EX','HS','IN') AND TXNTYPE IN ('CV','NB','RN','RW') AND PROCESSINGSTATUS ='*'
AND POLICYNO IS NOT NULL AND
INSUREDNAMENO IN (SELECT DISTINCT NAMENO FROM
MCITCUST WHERE CURRENTNAMENO = E.CURRENTNAMENO)),
MONTHS = (CASE WHEN MONTH(E.POLICYEXPDATE) = 1 THEN 'January'
WHEN MONTH(E.POLICYEXPDATE) = 2 THEN 'February'
WHEN MONTH(E.POLICYEXPDATE) = 3 THEN 'March'
WHEN MONTH(E.POLICYEXPDATE) = 4 THEN 'April'
WHEN MONTH(E.POLICYEXPDATE) = 5 THEN 'May'
WHEN MONTH(E.POLICYEXPDATE) = 6 THEN 'June'
WHEN MONTH(E.POLICYEXPDATE) = 7 THEN 'July'
WHEN MONTH(E.POLICYEXPDATE) = 8 THEN 'August'
WHEN MONTH(E.POLICYEXPDATE) = 9 THEN 'September'
WHEN MONTH(E.POLICYEXPDATE) = 10 THEN 'October'
WHEN MONTH(E.POLICYEXPDATE) = 11 THEN 'November'
WHEN MONTH(E.POLICYEXPDATE) = 12 THEN 'December' END),
BRK_CITY,
RB.FULLNAME RN_BROKER,
RB.CITY RN_BRK_CITY,
ORIG_EXP =
(SELECT MIN(POLICYEXPDATE) POLICYEXPDATE
FROM
MPATPMRC
WHERE POLICYNO = E.POLICYNO AND TXNTYPE IN ('NB','RN','RW')) ,
DROP_CATEGORY = CASE WHEN R.POLICYNO IS NULL THEN CLOSECATEGORYDESC ELSE '' END ,
DROP_REASON = CASE WHEN R.POLICYNO IS NULL THEN CLOSEREASONDESC ELSE '' END ,
COMPETITOR = CASE WHEN R.POLICYNO IS NULL THEN COMPETITORDESC ELSE '' END ,
MKTFOLLOWUP = CASE WHEN R.POLICYNO IS NULL THEN MKTFOLLOWUPDESC ELSE '' END,
MKT_SEGMENTSORT = MS.SORTORDER
FROM #EXPIRED E
LEFT OUTER JOIN #RENEWALS R ON
E.POLICYNO = R.PREVIOUSPOLICYNO
LEFT OUTER JOIN
(SELECT POLICYNO, SUM(SALES)SALES, MAX(UW) UW, SUM(LAYER1_2) PREM_2MIL, MAX(EX_LIMIT) EX_LIMIT,
MAX(BRK_NAME) BROKER, MAX(EX_BROKER_GROUP) BROKER_GRP, MAX(RATE) COMM_RATE, MAX(BRK_CITY) BRK_CITY
FROM #EX
GROUP BY POLICYNO ) A ON
E.POLICYNO = A.POLICYNO
LEFT OUTER JOIN #EX_PREM EP ON
E.POLICYNO = EP.POLICYNO
LEFT OUTER JOIN #RN_PREM RP ON
R.POLICYNO = RP.POLICYNO
LEFT OUTER JOIN MPAT_MKTSEGMENTS MK ON
A.POLICYNO = MK.POLICYNO
LEFT OUTER JOIN MSOTMARKETSEGMENT MS ON
MK.MKT_SEGMENT = MS.MARKETSEGMENT
LEFT OUTER JOIN #RN_BRK RB ON
R.POLICYNO = RB.POLICYNO
WHERE ISNULL(E.CN_METHOD,'X') <> 'F'
ORDER BY R.POLICYNO
Looks like something I just worked on, coincidentally in insurance too.
There are a couple of workarounds other than optimizing the query. RS & VS don't like temp tables.
One option may be to create the temp table structure at the beginning of the stored proc and then use insert into rather than select into syntax. Not sure if this will solve the issue though.
Another would be to create a wrapper stored proc that would return an empty dataset with the structure you need. Then point to the real stored proc when running the report.
A final one would be to use a nested query (derived table) instead of a temp table.
cheers,
Andrew
|||Thanks for the response. I'll try it out. I wish there was a better answer to why RS and VS don't like temp tables though. Seems like that should be addressed by MS as temp tables are about as standard as they come.|||Another strange development...
When I try to run the stored procedure in my test environment via Management Studio (a restore from the previous night's production backup on another server) it also runs continuously. So, I have a stored proc in production that runs fine, the same stored proc in test that doesn't, and it also doesn't work correctly when I try to run it from the production stored proc via SRS.
|||You could try using SQL profiler to see what's going on.
Here is another reason to move to derived tables.
http://www.sql-server-performance.com/jg_derived_tables.asp
cheers,
Andrew
|||I have it fixed.
Here's a little more info for you. I use this stored proc in two reports (one for adhoc use and one for a monthly auto-run report). Last month I added two new summary tables to the adhoc report that both use the same dataset. Then the user called me this passed week and asked why these two new summary tables weren't in the monthly report. So, I added them and told him to get the data through the adhoc report for this month. That's when he told me the report wasn't working (same with the monthly report once the tables were added).
So, I have since removed the two tables from the adhoc report (it ran fine) and then added them back in manually (still works fine). I will be doing the same thing to the monthly report now as well.
However, now I have two outstanding oddities 1) why did adding the two tables cause it to run forever in the first place, but then removing and readding them makes it run fine & 2) why does the stored proc run forever in my test environment using enterprise manager. Now, I don't really need those two things answered because the issue is resolved, but it sure would help troubleshooting on future report issues.
Thanks for your posts even though I led you in the wrong direction.
No comments:
Post a Comment