Number of insuree with Active insurance by Product/location/age range/gender/yearMonth

This request use a CROSS APPLY to sum the active policies per yearmonth, this approach use lot of compute, it shouldn’t be executed on a live server during business hours

Parameters:

RegionCode (can be null)
DistrictCode (can be null)
WardCode (can be null)
VillageCode (can be null)
startDate: date of the first YearMonth to be retreived
stopDate: date of the last YearMonth to be retreived

Dimensions:

Product code and Name
Location (Region, District, Ward, Village) code and name
Age range of 5 years until 80, then 1 group for 80+
Gender
Policy status
year month in the format YYYYMM

;WITH  Locations as (
SELECT * FROM uvwLocations 
WHERE VillageCode IS NOT NULL
AND (@RegionCode  is null or @RegionCode='' or RegionCode  = @RegionCode )
AND (@DistrictCode is null or @DistrictCode='' or DistrictCode = @DistrictCode)
AND (@WardCode is null or @WardCode='' or WardCode = @WardCode)
AND (@VillageCode is null or @VillageCode='' or VillageCode = @VillageCode)
),insuree AS
(
SELECT 
tblLocations.LocationCode
  ,tblLocations.LocationName
  ,Ins.CHFID
  ,tblProduct.ProductCode
  ,tblProduct.ProductName
  ,tblPolicy.EnrollDate
  ,tblPolicy.StartDate
  ,tblPolicy.EffectiveDate
  ,tblPolicy.ExpiryDate
  ,tblPolicy.PolicyStatus
  ,tblPolicy.PolicyValue
  ,tblFamilies.FamilyType
  ,CASE 
	WHEN datediff(yy,Ins.DOB,tblPolicy.EnrollDate) > 80 THEN 16
	ELSE datediff(yy,Ins.DOB,tblPolicy.EnrollDate)/5
  END AS AgeGroup
  ,Ins.Gender
  ,head.CHFID AS HEADID
FROM tblInsureePolicy IP
  INNER JOIN tblInsuree Ins
    ON IP.InsureeID = Ins.InsureeID
  INNER JOIN  tblPolicy 
	ON IP.policyId = tblPolicy.policyId
  INNER JOIN tblFamilies
    ON tblPolicy.FamilyID= tblFamilies.FamilyID 
  INNER JOIN tblInsuree head
    ON tblFamilies.InsureeID = head.InsureeID 
  INNER JOIN tblProduct
    ON tblPolicy.ProdID = tblProduct.ProdID
  LEFT JOIN tblLocations
    ON tblFamilies.LocationId = tblLocations.LocationId

WHERE
   Ins.CHFID <> '999999999'
   and tblPolicy.ValidityTo IS NULL 
   AND (tblPolicy.StartDate <= @stopDate or tblPolicy.StartDate <= @stopDate)
   AND (tblPolicy.ExpiryDate > @startDate)
   AND (tblPolicy.EffectiveDate IS NOT NULL)
   AND (tblLocations.LocationCode in (SELECT VillageCode from Locations) )
), MinMax as (
	SELECT  MIN(EffectiveDate) as Min 
	,MAX(ExpiryDate) as Max
	,datediff(mm,@startDate,@stopDate) as nbWeek
	FROM insuree
)
SELECT CONCAT(	RIGHT(10000+DATEPART(yyyy, DATEADD(mm,  x.number, @startDate)),4),RIGHT(100+DATEPART(mm,DATEADD(mm,  x.number, @startDate)),2)) as YearMonth
, A.*
,p.ProductName
,l.VillageName
,l.VillageCode
,l.WardName
,l.WardCode
,l.DistrictName
,l.DistrictCode
,l.RegionName
,l.RegionCode
FROM master.dbo.spt_values x

CROSS APPLY
(
  SELECT COUNT(DISTINCT CHFID) as nbInsuree
    ,ProductCode
    ,LocationCode
    ,PolicyStatus
    ,AgeGroup
    ,Gender
  FROM insuree
  WHERE insuree.EffectiveDate <= DATEADD(mm,  x.number, @startDate) 
  And insuree.ExpiryDate >= DATEADD(mm,  x.number, @startDate)
  GROUP BY LocationCode
    ,ProductCode
    ,PolicyStatus
    ,AgeGroup
    ,Gender
) A
INNER JOIN tblProduct p
ON A.ProductCode = p.ProductCode
INNER JOIN Locations l
ON A.LocationCode = l.VillageCode
WHERE x.type = 'P' AND x.number <= DATEDIFF(mm, @startDate, @stopDate)
AND p.ValidityTo IS NULL

openIMIS

Number of insuree with Active insurance by Product/location/age range/gender/yearMonth

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

Please contact our Service Desk