Policy - Primary Indicators Module

OMT-333: Report: Policies - Primary Operational IndicatorsIn Review

The Policy/New Policy and Insuree/New Insuree figures are always identical and refer to insurees, not policies and insurees as one would expect.

This report is based on the uspSSRSPrimaryIndicators1 stored procedure. It will separately runs queries for each month in the given range from sub-procedures that are all very similar. They are run in two modes that only differ by adding the Officer to grouping. The modular version is using the same queries, just adding the grouping where applicable.

Total policies:
    AND PL."PolicyStatus" > 1
    AND PL.EffectiveDate <= %(LastDay)s
    AND PL.ExpiryDate > %(LastDay)s

join with new policies:
    Pol-Fam-Vil-War-Dist-Reg-Ins
    where policy status > 1 (IDLE)
    and policy_Stage = 'N'  (so not renewals)
    and enrollment date is @year-@month   (so enrolled during that month while the total above is all policies active during that month)
    mode 2 adds Officer, LastName, OtherNames of course

join with suspended policies:
    where policy ValidityFrom is @year-@month
    and policy status = 4 (SUSPENDED)

join with expired policies:
    where policy status > 1 (IDLE)
    and policy Expiry Date is @year-@month

join with udfPolicyRenewal
  	PolicyStatus > 1
	PL.PolicyStage = 'R'
	enrollment date is @year-@month

join with udfPolicyInsuree same as Total Policies ?
    Pol-Prod-Fam-Ins-Loc
    PolicyStatus > 1
    EffectiveDate <= LastDay
    ExpiryDate > LastDay

join with udfNewPolicyInsuree same as new policies ?
    PolicyStatus > 1 (IDLE)
    Policy_Stage = 'N'  (so not renewals)
    EnrollmentDate is @year-@month   (so enrolled during that month while the total above is all policies active during that month)

join with udfNewlyPremiumCollected
    left join Premium
    sum(prem.amount)
    where paydate is @year-@month

join with udfAvailablePremium
    inner join Premium ?
    PolicyStatus <> 1
    PR."PayDate" <= PL."ExpiryDate"
    group by PL."ProdID", PL."ExpiryDate", PR."PayDate",PL."EffectiveDate"
    gros case => group by ProdId

Then the data is joined with a complex condition to dispatch the premiums depending on the date within the month. The original:

CASE
WHEN MONTH(DATEADD_DAY(-1,PL."ExpiryDate")) = theMonth AND YEAR(DATEADD_DAY(-1,PL."ExpiryDate")) = theYear AND (DAY(PL."ExpiryDate")) > 1
    THEN CASE WHEN DATEDIFF_DAY(CASE WHEN PR."PayDate" < theDate THEN theDate ELSE PR."PayDate" END,PL."ExpiryDate") = 0 THEN 1 ELSE DATEDIFF_DAY(CASE WHEN PR."PayDate" < theDate THEN theDate ELSE PR."PayDate" END,PL."ExpiryDate") END  * ((SUM(PR."Amount"))/(CASE WHEN (DATEDIFF_DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END,PL."ExpiryDate")) <= 0 THEN 1 ELSE DATEDIFF_DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END,PL."ExpiryDate") END))
WHEN MONTH(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END) = theMonth AND YEAR(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END) = theYear
    THEN ((DaysInMonth + 1 - DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END)) * ((SUM(PR."Amount"))/CASE WHEN DATEDIFF_DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END,PL."ExpiryDate") <= 0 THEN 1 ELSE DATEDIFF_DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END,PL."ExpiryDate") END))
WHEN PL."EffectiveDate" < theDate AND PL."ExpiryDate" > EndDate AND PR."PayDate" < theDate
    THEN DaysInMonth * (SUM(PR."Amount")/CASE WHEN (DATEDIFF_DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END,DATEADD_DAY(-1,PL."ExpiryDate"))) <= 0 THEN 1 ELSE DATEDIFF_DAY(CASE WHEN PR."PayDate" < PL."EffectiveDate" THEN PL."EffectiveDate" ELSE PR."PayDate" END,PL."ExpiryDate") END)
END Allocated

Was rewritten as:

CASE
WHEN MONTH(DATEADD_DAY(-1,PL."ExpiryDate")) = theMonth AND YEAR(DATEADD_DAY(-1,PL."ExpiryDate")) = theYear AND (DAY(PL."ExpiryDate")) > 1
    THEN GREATEST(1, DATEDIFF_DAY(GREATEST(PR."PayDate", theDate),PL."ExpiryDate"))
      * ((SUM(PR."Amount"))
         /GREATEST(1, DATEDIFF_DAY(GREATEST(PR."PayDate", PL."EffectiveDate"),PL."ExpiryDate")))
WHEN MONTH(GREATEST(PR."PayDate", PL."EffectiveDate")) = theMonth AND YEAR(GREATEST(PR."PayDate", PL."EffectiveDate")) = theYear
    THEN ((DaysInMonth + 1 - DAY(GREATEST(PR."PayDate", PL."EffectiveDate")))
              * (SUM(PR."Amount")
                     /GREATEST(1, DATEDIFF_DAY(GREATEST(PR."PayDate", PL."EffectiveDate"),PL."ExpiryDate"))))
WHEN PL."EffectiveDate" < theDate AND PL."ExpiryDate" > EndDate AND PR."PayDate" < theDate
    THEN DaysInMonth
             * (SUM(PR."Amount")
                    /GREATEST(1, DATEDIFF_DAY(GREATEST(PR."PayDate", PL."EffectiveDate"),DATEADD_DAY(-1,PL."ExpiryDate"))))
END Allocated

openIMIS

Policy - Primary Indicators Module

Did you encounter a problem or do you have a suggestion?

Please contact our Service Desk