SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfAPIisValidMaritalStatus](

@MaritalStatusCode NVARCHAR(1)

)

RETURNS BIT

AS

BEGIN

DECLARE @tblMaritalStatus TABLE(MaritalStatusCode NVARCHAR(1))

DECLARE @isValid BIT

INSERT INTO @tblMaritalStatus(MaritalStatusCode)

VALUES ('N'),('W'),('S'),('D'),('M'),(NULL)

IF EXISTS(SELECT 1 FROM @tblMaritalStatus WHERE MaritalStatusCode = @MaritalStatusCode)

SET @isValid = 1

ELSE

SET @isValid = 0

RETURN(@isValid)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[CREATE FUNCTION]

(

@ProdID INT = 0,

@LocationId INT = 0,

@Month INT,

@Year INT,

@Mode INT --1:Product Base, 2:Officer Mode

)

RETURNS @Result TABLE(ProdId INT, Allocated FLOAT,Officer NVARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

DECLARE @Date DATE,

@DaysInMonth INT,

@EndDate DATE

SELECT @Date = CAST(CAST(@Year AS VARCHAR(4)) + '-' + CAST(@Month AS VARCHAR(2)) + '-' + '01' AS DATE)

SELECT @DaysInMonth = DATEDIFF(DAY,@Date,DATEADD(MONTH,1,@Date))

SELECT @EndDate = CAST(CONVERT(VARCHAR(4),@Year) + '-' + CONVERT(VARCHAR(2),@Month) + '-' + CONVERT(VARCHAR(2),@DaysInMonth) AS DATE)

IF @Mode = 1

BEGIN

;WITH Allocation AS

(

SELECT PL.ProdID,

CASE

WHEN MONTH(DATEADD(D,-1,PL.ExpiryDate)) = @Month AND YEAR(DATEADD(D,-1,PL.ExpiryDate)) = @Year AND (DAY(PL.ExpiryDate)) > 1

THEN CASE WHEN DATEDIFF(D,CASE WHEN PR.PayDate < @Date THEN @Date ELSE PR.PayDate END,PL.ExpiryDate) = 0 THEN 1 ELSE DATEDIFF(D,CASE WHEN PR.PayDate < @Date THEN @Date 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) = @Month AND YEAR(CASE WHEN PR.PayDate < PL.EffectiveDate THEN PL.EffectiveDate ELSE PR.PayDate END) = @Year

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 < @Date AND PL.ExpiryDate > @EndDate AND PR.PayDate < @Date

THEN @DaysInMonth * (SUM(PR.Amount)/CASE WHEN (DATEDIFF(DAY,CASE WHEN PR.PayDate < PL.EffectiveDate THEN PL.EffectiveDate ELSE PR.PayDate END,DATEADD(D,-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

FROM tblPremium PR

INNER JOIN tblPolicy PL ON PR.PolicyID = PL.PolicyID

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

WHERE PR.ValidityTo IS NULL

AND PL.ValidityTo IS NULL

AND PL.ProdID = @ProdId

AND PL.PolicyStatus <> 1

AND PR.PayDate <= PL.ExpiryDate

AND (D.Region = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

GROUP BY PL.ProdID, PL.ExpiryDate, PR.PayDate,PL.EffectiveDate

)

INSERT INTO @Result(ProdId,Allocated)

SELECT ProdId, ISNULL(SUM(Allocated), 0)Allocated

FROM Allocation

GROUP BY ProdId

END

ELSE IF @Mode = 2

BEGIN

;WITH Allocation AS

(

SELECT PL.ProdID,

CASE

WHEN MONTH(DATEADD(D,-1,PL.ExpiryDate)) = @Month AND YEAR(DATEADD(D,-1,PL.ExpiryDate)) = @Year AND (DAY(PL.ExpiryDate)) > 1

THEN CASE WHEN DATEDIFF(D,CASE WHEN PR.PayDate < @Date THEN @Date ELSE PR.PayDate END,PL.ExpiryDate) = 0 THEN 1 ELSE DATEDIFF(D,CASE WHEN PR.PayDate < @Date THEN @Date 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) = @Month AND YEAR(CASE WHEN PR.PayDate < PL.EffectiveDate THEN PL.EffectiveDate ELSE PR.PayDate END) = @Year

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 < @Date AND PL.ExpiryDate > @EndDate AND PR.PayDate < @Date

THEN @DaysInMonth * (SUM(PR.Amount)/CASE WHEN (DATEDIFF(DAY,CASE WHEN PR.PayDate < PL.EffectiveDate THEN PL.EffectiveDate ELSE PR.PayDate END,DATEADD(D,-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,

O.Code, O.LastName, O.OtherNames

FROM tblPremium PR INNER JOIN tblPolicy PL ON PR.PolicyID = PL.PolicyID

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblOfficer O ON PL.OfficerId = O.OfficerID

WHERE PR.ValidityTo IS NULL

AND PL.ValidityTo IS NULL

AND O.ValidityTo IS NULL

AND (D.Region = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND PL.PolicyStatus <> 1

AND PR.PayDate <= PL.ExpiryDate

GROUP BY PL.ProdID, PL.ExpiryDate, PR.PayDate,PL.EffectiveDate, O.Code, O.LastName, O.OtherNames

)

INSERT INTO @Result(ProdId,Allocated,Officer,LastName,OtherNames)

SELECT ProdId, ISNULL(SUM(Allocated), 0)Allocated, Code, LastName, OtherNames

FROM Allocation

GROUP BY ProdId, Code, LastName, OtherNames

END

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfCollectedContribution](

@DateFrom DATE,

@DateTo DATE,

@OfficerId INT

)

RETURNS DECIMAL(18,2)

AS

BEGIN

DECLARE @LegacyOfficer INT

DECLARE @tblOfficerSub TABLE(OldOfficer INT, NewOfficer INT)

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @OfficerID

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @OfficerID)

WHILE @LegacyOfficer IS NOT NULL

BEGIN

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @LegacyOfficer

IF EXISTS(SELECT 1 FROM @tblOfficerSub GROUP BY NewOfficer HAVING COUNT(1) > 1)

BREAK;

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @LegacyOfficer)

END;

RETURN(

SELECT SUM(Amount) FROM tblPremium PR

INNER JOIN tblPolicy PL ON PL.PolicyID=PR.PolicyID

INNER JOIN @tblOfficerSub O ON O.NewOfficer = PL.OfficerID

WHERE

PL.ValidityTo IS NULL

AND PR.ValidityTo IS NULL

AND PayDate >= @DateFrom

AND PayDate <=@DateTo

)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfExpiredPolicies]

(

@ProdID INT = 0,

@LocationId INT = 0,

@Month INT,

@Year INT,

@Mode INT --1:Product base, 2: Officer Base

)

RETURNS @Resul TABLE(ProdId INT, ExpiredPolicies INT, Officer NVARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Resul(ProdId,ExpiredPolicies)

SELECT PL.ProdID, COUNT(PL.PolicyID) ExpiredPolicies

FROM tblPolicy PL

INNER JOIN tblFamilies F ON F.FamilyId = PL.FamilyId

INNER JOIN tblVillages V ON V.VillageId = F.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

WHERE PL.ValidityTo IS NULL

AND F.ValidityTo IS NULL

AND PL.PolicyStatus >1 --Uncommented By Rogers for PrimaryIndicator1 Report

AND MONTH(PL.ExpiryDate) = @Month AND YEAR(PL.ExpiryDate) = @Year

AND (PL.ProdID = @ProdID OR @ProdID = 0)

AND (D.Region = @LocationId OR D.DistrictId= @LocationId OR @LocationId = 0)

GROUP BY PL.ProdID

ELSE IF @Mode = 2

INSERT INTO @Resul(ProdId,ExpiredPolicies,Officer,LastName,OtherNames)

SELECT PL.ProdID, COUNT(PL.PolicyID) ExpiredPolicies,O.Code,O.LastName,O.OtherNames

FROM tblPolicy PL

INNER JOIN tblFamilies F ON F.FamilyId = PL.FamilyId

INNER JOIN tblVillages V ON V.VillageId = F.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblOfficer O ON PL.OfficerID = O.OfficerID

WHERE PL.ValidityTo IS NULL

AND F.ValidityTo IS NULL

AND PL.PolicyStatus >1 --Uncommented By Rogers for PrimaryIndicator1 Report

AND MONTH(PL.ExpiryDate) = @Month AND YEAR(PL.ExpiryDate) = @Year

AND (PL.ProdID = @ProdID OR @ProdID = 0)

AND (D.Region = @LocationId OR D.DistrictId= @LocationId OR @LocationId = 0)

GROUP BY PL.ProdID,O.Code,O.LastName,O.OtherNames

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfExpiredPoliciesPhoneStatistics](

@DateFrom DATE,

@DateTo DATE,

@OfficerId INT

)

RETURNS INT

AS

BEGIN

DECLARE @LegacyOfficer INT

DECLARE @tblOfficerSub TABLE(OldOfficer INT, NewOfficer INT)

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @OfficerID

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @OfficerID)

WHILE @LegacyOfficer IS NOT NULL

BEGIN

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @LegacyOfficer

IF EXISTS(SELECT 1 FROM @tblOfficerSub GROUP BY NewOfficer HAVING COUNT(1) > 1)

BREAK;

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @LegacyOfficer)

END;

RETURN(

SELECT COUNT(1) ExpiredPolicies

FROM tblPolicy PL

LEFT OUTER JOIN (SELECT PL.PolicyID, F.FamilyID, PR.ProdID

FROM tblPolicy PL

INNER JOIN tblFamilies F ON F.FamilyId = PL.FamilyId

INNER JOIN tblProduct PR ON PR.ProdID= PL.ProdID OR(PL.ProdID = PR.ConversionProdID )

WHERE

PL.ValidityTo IS NULL

AND F.ValidityTo IS NULL

AND PR.ValidityTo IS NULL

AND PL.PolicyStage='R'

) R ON PL.ProdID=R.ProdID AND PL.FamilyID=R.FamilyID

INNER JOIN @tblOfficerSub O ON O.NewOfficer = PL.OfficerID

WHERE

PL.ValidityTo IS NULL

AND PL.PolicyStatus = 8

AND R.PolicyID IS NULL

AND (PL.ExpiryDate >= @DateFrom AND PL.ExpiryDate < = @DateTo)

)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfGetSnapshotIndicators](

@Date DATE,

@OfficerId INT

) RETURNS @tblSnapshotIndicators TABLE(ACtive INT,Expired INT,Idle INT,Suspended INT)

AS

BEGIN

DECLARE @ACtive INT=0

DECLARE @Expired INT=0

DECLARE @Idle INT=0

DECLARE @Suspended INT=0

DECLARE @LegacyOfficer INT

DECLARE @tblOfficerSub TABLE(OldOfficer INT, NewOfficer INT)

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @OfficerID

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @OfficerID)

WHILE @LegacyOfficer IS NOT NULL

BEGIN

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @LegacyOfficer

IF EXISTS(SELECT 1 FROM @tblOfficerSub GROUP BY NewOfficer HAVING COUNT(1) > 1)

BREAK;

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @LegacyOfficer)

END;

SET @ACtive = (

SELECT COUNT(DISTINCT P.FamilyID) ActivePolicies FROM tblPolicy P

INNER JOIN @tblOfficerSub O ON P.OfficerID = O.NewOfficer

INNER JOIN tblFamilies F on F.FamilyID = P.FamilyID

WHERE P.ValidityTo IS NULL AND PolicyStatus = 2

AND F.ValidityTo IS NULL

AND ExpiryDate >=@Date

)

SET @Expired = (SELECT COUNT(1) ExpiredPolicies

FROM tblPolicy PL

LEFT OUTER JOIN (

SELECT PL.PolicyID, F.FamilyID, PR.ProdID

FROM tblPolicy PL

INNER JOIN tblFamilies F ON F.FamilyId = PL.FamilyId

INNER JOIN tblProduct PR ON PR.ProdID= PL.ProdID OR (PL.ProdID = PR.ConversionProdID)

WHERE

PL.ValidityTo IS NULL

AND F.ValidityTo IS NULL

AND PR.ValidityTo IS NULL

AND PL.PolicyStage='R'

AND PL.PolicyStatus = 2

) R ON PL.ProdID=R.ProdID AND PL.FamilyID=R.FamilyID

INNER JOIN @tblOfficerSub O ON PL.OfficerID = O.NewOfficer

WHERE

PL.ValidityTo IS NULL

AND PL.PolicyStatus = 8

AND R.PolicyID IS NULL

AND (PL.ExpiryDate =@Date)

)

SET @Idle = (

SELECT COUNT(DISTINCT PL.FamilyID) IddlePolicies FROM tblPolicy PL

INNER JOIN @tblOfficerSub O ON PL.OfficerID = O.NewOfficer

INNER JOIN tblProduct PR ON PR.ProdID = PL.ProdID

INNER JOIN tblFamilies F ON PL.FamilyID = F.FamilyID

LEFT OUTER JOIN (SELECT FamilyID, ProdID FROM tblPolicy WHERE ValidityTo IS NULL AND PolicyStatus =2 AND ExpiryDate >=@Date) ActivePolicies ON ActivePolicies.FamilyID = PL.FamilyID AND (ActivePolicies.ProdID = PL.ProdID OR ActivePolicies.ProdID = PR.ConversionProdID)

WHERE PL.ValidityTo IS NULL AND PL.PolicyStatus = 1

AND ExpiryDate >=@Date

AND ActivePolicies.ProdID IS NULL

AND F.ValidityTo IS NULL

)

SET @Suspended = (

SELECT COUNT(DISTINCT PL.FamilyID) SuspendedPolicies FROM tblPolicy PL

INNER JOIN @tblOfficerSub O ON PL.OfficerID = O.NewOfficer

INNER JOIN tblProduct PR ON PR.ProdID = PL.ProdID

INNER JOIN tblFamilies F ON PL.FamilyID = F.FamilyID

LEFT OUTER JOIN (SELECT FamilyID, ProdID FROM tblPolicy WHERE ValidityTo IS NULL AND PolicyStatus =2 AND ExpiryDate >=@Date) ActivePolicies ON ActivePolicies.FamilyID = PL.FamilyID AND (ActivePolicies.ProdID = PL.ProdID OR ActivePolicies.ProdID = PR.ConversionProdID)

WHERE PL.ValidityTo IS NULL AND PL.PolicyStatus = 4

AND ExpiryDate >=@Date

AND ActivePolicies.ProdID IS NULL

AND F.ValidityTo IS NULL

)

INSERT INTO @tblSnapshotIndicators(ACtive, Expired, Idle, Suspended) VALUES (@ACtive, @Expired, @Idle, @Suspended)

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfNewlyPremiumCollected]

(

@ProdID INT = 0,

@LocationId INT = 0,

@Month INT,

@Year INT,

@Mode INT --1:Product Base, 2:Officer Base

)

RETURNS @Result TABLE(ProdId INT, PremiumCollection FLOAT,Officer NVARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,PremiumCollection)

SELECT PL.ProdID,SUM(PR.Amount)PremiumCollection

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

LEFT OUTER JOIN tblPremium PR ON PL.PolicyID = PR.PolicyID

WHERE PR.ValidityTo IS NULL

AND (PL.ProdID = @ProdID OR @ProdID = 0)

AND (D.Region = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND MONTH(PR.PayDate) = @Month AND YEAR(PR.PayDate) = @Year

GROUP BY PL.ProdID

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,PremiumCollection,Officer,LastName,OtherNames)

SELECT PL.ProdID,SUM(PR.Amount)PremiumCollection,O.Code,O.LastName,O.OtherNames

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblOfficer O ON PL.OfficerID = O.OfficerID

LEFT OUTER JOIN tblPremium PR ON PL.PolicyID = PR.PolicyID

WHERE PR.ValidityTo IS NULL

AND (D.Region = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND (PL.ProdID = @ProdID OR @ProdID = 0)

AND (D.Region = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND MONTH(PR.PayDate) = @Month AND YEAR(PR.PayDate) = @Year

GROUP BY PL.ProdID,O.Code,O.LastName,O.OtherNames

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfNewPolicies]

(

@ProdID INT,

@LocationId INT = 0,

@Month INT,

@Year INT,

@Mode INT --1: Product Base, 2: Enrollment Officer Base

)

RETURNS @Result TABLE(ProdId INT, Male INT,Female INT,Other INT, Officer VARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,Male,Female,Other)

SELECT ProdId, M Male, F Female, O Other

FROM

(SELECT PL.ProdId, I.Gender, I.InsureeId

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblRegions R ON R.RegionId = D.Region

INNER JOIN tblInsuree I ON I.InsureeId = Fam.InsureeID

WHERE PL.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND V.ValidityTo IS NULL

AND W.ValidityTo IS NULL

AND D.ValidityTo IS NULL

AND R.ValidityTo IS NULL

AND I.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.PolicyStage = N'N'

AND (PL.ProdId = @ProdID OR @ProdID = 0)

AND (R.RegionId = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND MONTH(PL.EnrollDate) = @Month

AND YEAR(PL.EnrollDate) = @Year

) NewPolicies

PIVOT

(

COUNT(InsureeId) FOR Gender IN (M, F, O)

)pvt

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,Male,Female,Other,Officer,LastName,OtherNames)

SELECT ProdId, M Male, F Female, O Other, Officer, LastName, OtherNames

FROM

(SELECT PL.ProdId, I.Gender, O.Code Officer, O.LastName, O.OtherNames, I.InsureeId

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblRegions R ON R.RegionId = D.Region

INNER JOIN tblInsuree I ON I.InsureeId = Fam.InsureeID

INNER JOIN tblOfficer O ON O.OfficerId = PL.OfficerID

WHERE PL.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND V.ValidityTo IS NULL

AND W.ValidityTo IS NULL

AND D.ValidityTo IS NULL

AND R.ValidityTo IS NULL

AND I.ValidityTo IS NULL

AND O.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.PolicyStage = N'N'

AND (PL.ProdId = @ProdID OR @ProdID = 0)

AND (R.RegionId = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND MONTH(PL.EnrollDate) = @Month

AND YEAR(PL.EnrollDate) = @Year

) NewPolicies

PIVOT

(

COUNT(InsureeId) FOR Gender IN (M, F, O)

)pvt

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfNewPoliciesPhoneStatistics](

@DateFrom DATE,

@DateTo DATE,

@OfficerId INT

)

RETURNS INT

AS

BEGIN

DECLARE @LegacyOfficer INT

DECLARE @tblOfficerSub TABLE(OldOfficer INT, NewOfficer INT)

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @OfficerID

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @OfficerID)

WHILE @LegacyOfficer IS NOT NULL

BEGIN

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @LegacyOfficer

IF EXISTS(SELECT 1 FROM @tblOfficerSub GROUP BY NewOfficer HAVING COUNT(1) > 1)

BREAK;

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @LegacyOfficer)

END;

RETURN(

SELECT COUNT(1)

FROM

tblPolicy PL

INNER JOIN @tblOfficerSub O ON O.NewOfficer = PL.OfficerID

WHERE PL.ValidityTo IS NULL AND PolicyStage ='N' AND EnrollDate >= @DateFrom AND EnrollDate <=@DateTo

)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfNewPolicyInsuree]

(

@ProdID INT = 0,

@LocationId INT = 0,

@Month INT,

@Year INT,

@Mode INT --1:Product base 2: Officer Base

)

RETURNS @Result TABLE(ProdId INT, Male INT, Female INT,Other INT, Officer NVARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,Male,Female,Other)

SELECT ProdId, M Male, F Female, O Other

FROM

(SELECT PL.ProdId, I.Gender, I.InsureeId

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblRegions R ON R.RegionId = D.Region

INNER JOIN tblInsuree I ON I.FamilyID = Fam.FamilyID

WHERE PL.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND V.ValidityTo IS NULL

AND W.ValidityTo IS NULL

AND D.ValidityTo IS NULL

AND R.ValidityTo IS NULL

AND I.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.PolicyStage = N'N'

AND (PL.ProdId = @ProdID OR @ProdID = 0)

AND (R.RegionId = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND MONTH(PL.EnrollDate) = @Month

AND YEAR(PL.EnrollDate) = @Year

) NewPolicies

PIVOT

(

COUNT(InsureeId) FOR Gender IN (M, F, O)

)pvt

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,Male,Female,Other,Officer,LastName,OtherNames)

SELECT ProdId, M Male, F Female, O Other, Officer, LastName, OtherNames

FROM

(SELECT PL.ProdId, I.Gender, O.Code Officer, O.LastName, O.OtherNames, I.InsureeId

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblRegions R ON R.RegionId = D.Region

INNER JOIN tblInsuree I ON I.FamilyID = Fam.FamilyID

INNER JOIN tblOfficer O ON O.OfficerId = PL.OfficerID

WHERE PL.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND V.ValidityTo IS NULL

AND W.ValidityTo IS NULL

AND D.ValidityTo IS NULL

AND R.ValidityTo IS NULL

AND I.ValidityTo IS NULL

AND O.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.PolicyStage = N'N'

AND (PL.ProdId = @ProdID OR @ProdID = 0)

AND (R.RegionId = @LocationId OR D.DistrictId = @LocationId OR @LocationId = 0)

AND MONTH(PL.EnrollDate) = @Month

AND YEAR(PL.EnrollDate) = @Year

) NewPolicies

PIVOT

(

COUNT(InsureeId) FOR Gender IN (M, F, O)

)pvt

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfPolicyInsuree]

(

@ProdID INT = 0,

@LocationId INT = 0,

@LastDay DATE,

@Mode INT --1: Product Base 2: Officer Base

)

RETURNS @Result TABLE(ProdId INT, Male INT, Female INT, Other INT,Officer NVARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,Male,Female, Other)

SELECT ProdId, [M], [F], [O]

FROM

(

SELECT Prod.ProdID, Ins.Gender, Ins.InsureeID

FROM tblPolicy PL

INNER JOIN tblProduct Prod ON Prod.ProdId = PL.ProdID

INNER JOIN tblFamilies Fam ON Fam.FamilyId = PL.FamilyID

INNER JOIN tblInsuree Ins ON Ins.FamilyId = Fam.FamilyId

INNER JOIN uvwLocations L ON L.VillageId = Fam.LocationId

WHERE PL.ValidityTo IS NULL

AND Prod.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND Ins.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.EffectiveDate <= @LastDay

AND PL.ExpiryDate > @LastDay

AND (Prod.ProdId = @ProdId OR @ProdId = 0)

AND (L.RegionId = @LocationId OR L.DistrictId = @LocationId OR ISNULL(@LocationId, 0) = 0)

)Base

PIVOT

(

COUNT(InsureeId) FOR Gender IN ([M], [F], [O])

)TotalPolicyInsurees

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,Male,Female, Other,Officer,LastName,OtherNames)

SELECT ProdId, [M], [F], [O], Officer, LastName, OtherNames

FROM

(

SELECT Prod.ProdID, Ins.Gender, Ins.InsureeID, O.Code Officer, O.LastName, O.OtherNames

FROM tblPolicy PL

INNER JOIN tblProduct Prod ON Prod.ProdId = PL.ProdID

INNER JOIN tblOfficer O ON O.OfficerId = PL.OfficerID

INNER JOIN tblFamilies Fam ON Fam.FamilyId = PL.FamilyID

INNER JOIN tblInsuree Ins ON Ins.FamilyId = Fam.FamilyId

INNER JOIN uvwLocations L ON L.VillageId = Fam.LocationId

WHERE PL.ValidityTo IS NULL

AND Prod.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND Ins.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.EffectiveDate <= @LastDay

AND PL.ExpiryDate > @LastDay

AND (Prod.ProdId = @ProdId OR @ProdId = 0)

AND (L.RegionId = @LocationId OR L.DistrictId = @LocationId OR ISNULL(@LocationId, 0) = 0)

)Base

PIVOT

(

COUNT(InsureeId) FOR Gender IN ([M], [F], [O])

)TotalPolicyInsurees

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfPolicyRenewal]

(

@ProdID INT = 0,

@LocationId INT = 0,

@Month INT,

@Year INT,

@Mode INT --1: Product Base, 2:Officer Base

)

RETURNS @Result TABLE(ProdId INT, Renewals INT, Officer NVARCHAR(50),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,Renewals)

SELECT PL.ProdId, COUNT(PL.PolicyId)Renewals

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

WHERE PL.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.PolicyStage = N'R'

AND (PL.ProdId = @ProdID OR @ProdID = 0)

AND (D.DistrictId = @LocationId OR D.Region = @LocationId OR @LocationId = 0)

AND MONTH(PL.EnrollDate) = @Month

AND YEAR(PL.EnrollDate) = @Year

GROUP BY PL.ProdID

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,Renewals,Officer,LastName,OtherNames)

SELECT PL.ProdId, COUNT(PL.PolicyId)Renewals, O.Code Officer, O.LastName, O.OtherNames

FROM tblPolicy PL

INNER JOIN tblFamilies Fam ON PL.FamilyID = Fam.FamilyId

INNER JOIN tblVillages V ON V.VillageId = Fam.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblOfficer O ON O.OfficerId = PL.OfficerId

WHERE PL.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.PolicyStage = N'R'

AND (PL.ProdId = @ProdID OR @ProdID = 0)

AND (D.DistrictId = @LocationId OR D.Region = @LocationId OR @LocationId = 0)

AND MONTH(PL.EnrollDate) = @Month

AND YEAR(PL.EnrollDate) = @Year

GROUP BY PL.ProdID, O.Code , O.LastName, O.OtherNames

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author: <Author,,Name>

-- Create date: <Create Date,,>

-- Description: <Description,,>

-- =============================================

CREATE FUNCTION [dbo].[udfPremiumComposition]

(

)

RETURNS @Resul TABLE(PolicyId INT, AssFee DECIMAL(18,2),RegFee DECIMAL(18,2),PremFee DECIMAL(18,2) )

AS

BEGIN

INSERT INTO @Resul(PolicyId,AssFee,RegFee,PremFee)

SELECT tblPolicy.PolicyID, CASE WHEN ISNULL(tblProduct.GeneralAssemblyLumpSum,0) = 0 THEN (COUNT(tblInsureePolicy.InsureeId) * ISNULL(tblProduct.GeneralAssemblyFee,0)) ELSE tblProduct.GeneralAssemblyLumpSum END as AssFee, CASE WHEN tblPolicy.PolicyStage = 'N' THEN (CASE WHEN ISNULL(tblProduct.RegistrationLumpSum ,0) = 0 THEN COUNT(tblInsureePolicy.InsureeId) * isnull(tblProduct.RegistrationFee,0) ELSE tblProduct.RegistrationLumpSum END) ELSE 0 END as RegFee, CASE WHEN ISNULL(tblProduct.LumpSum,0) = 0 THEN ( SUM (CASE WHEN (DATEDIFF(YY ,tblInsuree.DOB,tblInsureePolicy.EffectiveDate) >= 18) THEN 1 ELSE 0 END) * tblProduct.PremiumAdult) + ( SUM (CASE WHEN (DATEDIFF(YY ,tblInsuree.DOB,tblInsureePolicy.EffectiveDate) < 18) THEN 1 ELSE 0 END) * tblProduct.PremiumChild ) ELSE tblproduct.LumpSum END as PremFee

FROM tblPolicy INNER JOIN

tblInsureePolicy ON tblPolicy.PolicyID = tblInsureePolicy.PolicyId INNER JOIN

tblInsuree ON tblInsureePolicy.InsureeId = tblInsuree.InsureeID INNER JOIN tblProduct ON tblProduct.ProdID = tblPolicy.ProdID

WHERE (tblInsureePolicy.ValidityTo IS NULL) AND (tblPolicy.ValidityTo IS NULL) AND (tblInsuree.ValidityTo IS NULL) AND tblInsureePolicy.EffectiveDate IS NOT NULL and tblProduct.ValidityTo is null

GROUP BY tblPolicy.PolicyID, tblProduct.GeneralAssemblyFee , tblProduct.GeneralAssemblyLumpSum , tblProduct .RegistrationFee, tblProduct .RegistrationLumpSum ,tblProduct .LumpSum , tblProduct .PremiumAdult ,tblProduct .PremiumChild ,tblPolicy.PolicyStage

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfRenewedPoliciesPhoneStatistics](

@DateFrom DATE,

@DateTo DATE,

@OfficerId INT

)

RETURNS INT

AS

BEGIN

DECLARE @LegacyOfficer INT

DECLARE @tblOfficerSub TABLE(OldOfficer INT, NewOfficer INT)

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @OfficerID

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @OfficerID)

WHILE @LegacyOfficer IS NOT NULL

BEGIN

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @LegacyOfficer

IF EXISTS(SELECT 1 FROM @tblOfficerSub GROUP BY NewOfficer HAVING COUNT(1) > 1)

BREAK;

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @LegacyOfficer)

END;

RETURN(

SELECT COUNT(1) FROM

tblPolicy PL

INNER JOIN @tblOfficerSub O ON O.NewOfficer = PL.OfficerID

WHERE

ValidityTo IS NULL AND PolicyStage ='R' AND EnrollDate >= @DateFrom AND EnrollDate <=@DateTo

)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfSuspendedPolicies]

(

@ProdID INT = 0,

@LocationId INT ,

@Month INT,

@Year INT,

@Mode INT --1:Product base 2: Officer Base

)

RETURNS @Result TABLE(ProdId INT,SuspendedPolicies INT,Officer NVARCHAR(50),LastName NVARCHAR(50), OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,SuspendedPolicies)

SELECT PL.ProdID,COUNT(PL.PolicyID)SuspendedPolicies

FROM tblPolicy PL

INNER JOIN tblFamilies F ON F.FamilyId = PL.FamilyId

INNER JOIN tblVillages V ON V.VillageId = F.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

WHERE PL.ValidityTo IS NULL

AND F.ValidityTo IS NULL

AND PL.PolicyStatus = 4

AND (PL.ProdID = @ProdID OR @ProdID = 0)

AND MONTH(PL.ValidityFrom) = @Month AND YEAR(PL.ValidityFrom) = @Year

AND (D.Region = @LocationId OR D.DistrictId= @LocationId OR @LocationId = 0)

GROUP BY PL.ProdID

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,SuspendedPolicies,Officer,LastName,OtherNames)

SELECT PL.ProdID,COUNT(PL.PolicyID)SuspendedPolicies,O.Code,O.LastName,O.OtherNames

FROM tblPolicy PL

INNER JOIN tblFamilies F ON F.FamilyId = PL.FamilyId

INNER JOIN tblVillages V ON V.VillageId = F.LocationId

INNER JOIN tblWards W ON W.WardId = V.WardId

INNER JOIN tblDistricts D ON D.DistrictId = W.DistrictId

INNER JOIN tblOfficer O ON PL.OfficerID = O.OfficerID

WHERE PL.ValidityTo IS NULL

AND F.ValidityTo IS NULL

AND PL.PolicyStatus = 4

AND (PL.ProdID = @ProdID OR @ProdID = 0)

AND MONTH(PL.ValidityFrom) = @Month AND YEAR(PL.ValidityFrom) = @Year

AND (D.Region = @LocationId OR D.DistrictId= @LocationId OR @LocationId = 0)

GROUP BY PL.ProdID,O.Code,O.LastName,O.OtherNames

RETURN

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfSuspendedPoliciesPhoneStatistics](

@DateFrom DATE,

@DateTo DATE,

@OfficerId INT

)

RETURNS INT

AS

BEGIN

DECLARE @LegacyOfficer INT

DECLARE @tblOfficerSub TABLE(OldOfficer INT, NewOfficer INT)

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @OfficerID

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @OfficerID)

WHILE @LegacyOfficer IS NOT NULL

BEGIN

INSERT INTO @tblOfficerSub(OldOfficer, NewOfficer)

SELECT DISTINCT @OfficerID, @LegacyOfficer

IF EXISTS(SELECT 1 FROM @tblOfficerSub GROUP BY NewOfficer HAVING COUNT(1) > 1)

BREAK;

SET @LegacyOfficer = (SELECT OfficerID FROM tblOfficer WHERE ValidityTo IS NULL AND OfficerIDSubst = @LegacyOfficer)

END;

RETURN(

SELECT COUNT(1) SuspendedPolicies

FROM tblPolicy PL

INNER JOIN @tblOfficerSub O ON O.NewOfficer = PL.OfficerID

WHERE PL.ValidityTo IS NULL

AND PL.PolicyStatus = 4

AND (ExpiryDate >= @DateFrom AND ExpiryDate < = @DateTo)

)

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE FUNCTION [dbo].[udfTotalPolicies]

(

@ProdID INT = 0,

@LocationId INT = 0,

@LastDay DATE,

@Mode INT --1: ON Product, 2: On Officer

)

RETURNS @Result TABLE(ProdId INT, Male INT,Female INT, Other INT, Officer NVARCHAR(8),LastName NVARCHAR(50),OtherNames NVARCHAR(50))

AS

BEGIN

IF @Mode = 1

INSERT INTO @Result(ProdId,Male,Female, Other)

SELECT ProdId, [M], [F], [O]

FROM

(

SELECT Prod.ProdID, Ins.Gender, Ins.InsureeID

FROM tblPolicy PL

INNER JOIN tblProduct Prod ON Prod.ProdId = PL.ProdID

INNER JOIN tblFamilies Fam ON Fam.FamilyId = PL.FamilyID

INNER JOIN tblInsuree Ins ON Ins.InsureeId = Fam.InsureeID

INNER JOIN uvwLocations L ON L.VillageId = Fam.LocationId

WHERE PL.ValidityTo IS NULL

AND Prod.ValidityTo IS NULL

AND Fam.ValidityTo IS NULL

AND Ins.ValidityTo IS NULL

AND PL.PolicyStatus > 1

AND PL.EffectiveDate <= @LastDay

AND PL.ExpiryDate > @LastDay

AND (Prod.ProdId = @ProdId OR @ProdId = 0)

AND (L.RegionId = @LocationId OR L.DistrictId = @LocationId OR ISNULL(@LocationId, 0) = 0 OR @LocationId = 0) --@LocationId = 0 Added to get Country data

)Base

PIVOT

(

COUNT(InsureeId) FOR Gender IN ([M], [F], [O])

)TotalPolicies

ELSE IF @Mode = 2

INSERT INTO @Result(ProdId,Male,Female, Other,Officer,LastName,OtherNames)

SELECT ProdId, [M], [F], [O], Officer, LastName, OtherNames

FROM

(

SELECT Prod.ProdID, Ins.Gender, Ins.InsureeID, O.Code Officer, O.LastName, O.OtherNames

FROM tblPolicy PL

INNER JOIN tblProduct Prod ON Prod.ProdId = PL.ProdID

INNER JOIN tblOfficer O ON O.OfficerId = PL.OfficerID

INNER JOIN tblFamilies Fam ON Fam.FamilyId = PL.FamilyID

INNER JOIN tblInsuree Ins ON Ins.InsureeId = Fam.InsureeID

INNER JOIN uvwLocations L ON L.VillageId = Fam.LocationId

WHERE PL.ValidityTo IS NULL

AND Prod.ValidityTo IS NULL