OMT-333 - Getting issue details... STATUS
Warning: 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