This request use a CROSS APPLY to sum the active policy 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