USE [iCare]
GO
/****** Object:  StoredProcedure [dbo].[prn_GetCostPerMember]    Script Date: 11/23/2017 11:45:58 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[prn_GetCostPerMember]    
    
    
 @strCorporate varchar(100),    
 @strClaimType varchar(10),    
 @dtSD datetime,    
 @dtED datetime


AS    
BEGIN    
    
SET NOCOUNT ON

CREATE TABLE #tblTemp
(
	Amt decimal(18,2),
	Year int,
	Month decimal(18,0),
	Headcount decimal(18,0)
)

CREATE TABLE #tblCorp
	(
		ItemID int,
		CorpID varchar(20)
	)

	INSERT INTO #tblCorp (ItemID, CorpID)
	EXECUTE pra_SplitText @strCorporate, ','

CREATE TABLE #tblCost
(
	ClaimType varchar(10),
	CPMPA decimal(18,2)
)

	INSERT INTO #tblTemp VALUES (0,0,0,0)

IF (@strClaimType = 'GP' OR @strClaimType = 'All')
BEGIN

	UPDATE #tblTemp
	SET #tblTemp.Amt = tbl.Amt
	FROM
	(
		SELECT SUM(DD_SHELL.AmtInsured) as Amt
		FROM DD_SHELL 
		WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
		AND DD_SHELL.DTDISABILITY >= @dtSD
		AND DD_SHELL.DTDISABILITY <= @dtED
		AND DD_SHELL.ClaimType = 'GP'
	)tbl

	UPDATE #tblTemp
	SET #tblTemp.Month = tbl.Mth
	FROM
	(
		SELECT COUNT(Mth) as Mth
		FROM
		(
			SELECT DISTINCT YEAR(DD_SHELL.DTDISABILITY) as Yr, MONTH(DD_SHELL.DTDISABILITY) as Mth
			FROM DD_SHELL
			WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
			AND DD_SHELL.DTDISABILITY >= @dtSD
			AND DD_SHELL.DTDISABILITY <= @dtED
			AND DD_SHELL.ClaimType = 'GP'
		)tblTemp
	)tbl

	UPDATE #tblTemp
	SET #tblTemp.Year = YEAR(@dtSD)
	--FROM
	--(
	--	SELECT YEAR(DD_SHELL.DTDISABILITY) as Yr
	--	FROM DD_SHELL
	--	WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
	--	AND DD_SHELL.DTDISABILITY = @dtSD
	--	AND DD_SHELL.ClaimType = 'GP'
	--)tbl

	UPDATE #tblTemp
	SET #tblTemp.Headcount = tbl.Headcount
	FROM
	(
		SELECT COUNT(DD_Headcount.EmpID) as Headcount
		FROM DD_Headcount
		WHERE YEAR(DD_Headcount.Year) IN (SELECT Year FROM #tblTemp)
		AND DD_Headcount.copar_id IN (SELECT CorpID FROM #tblCorp)
		AND DD_Headcount.PolicyType = 'OP'
	)tbl

	INSERT INTO #tblCost
	SELECT 'GP', CONVERT(decimal(18,2),((#tblTemp.Amt/#tblTemp.Month)*12)/#tblTemp.Headcount)
	FROM #tblTemp	


END


IF (@strClaimType = 'SP' OR @strClaimType = 'All')
BEGIN

	UPDATE #tblTemp
	SET #tblTemp.Amt = tbl.Amt
	FROM
	(
		SELECT SUM(DD_SHELL.AmtInsured) as Amt
		FROM DD_SHELL 
		WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
		AND DD_SHELL.DTDISABILITY >= @dtSD
		AND DD_SHELL.DTDISABILITY <= @dtED
		AND DD_SHELL.ClaimType = 'SP'
	)tbl

	UPDATE #tblTemp
	SET #tblTemp.Month = tbl.Mth
	FROM
	(
		SELECT COUNT(Mth) as Mth
		FROM
		(
			SELECT DISTINCT YEAR(DD_SHELL.DTDISABILITY) as Yr, MONTH(DD_SHELL.DTDISABILITY) as Mth
			FROM DD_SHELL
			WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
			AND DD_SHELL.DTDISABILITY >= @dtSD
			AND DD_SHELL.DTDISABILITY <= @dtED
			AND DD_SHELL.ClaimType = 'SP'
		)tblTemp
	)tbl

	UPDATE #tblTemp
	SET #tblTemp.Year = YEAR(@dtSD)
	--FROM
	--(
	--	SELECT YEAR(DD_SHELL.DTDISABILITY) as Yr
	--	FROM DD_SHELL
	--	WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
	--	AND DD_SHELL.DTDISABILITY = @dtSD
	--	AND DD_SHELL.ClaimType = 'SP'
	--)tbl

	UPDATE #tblTemp
	SET #tblTemp.Headcount = tbl.Headcount
	FROM
	(
		SELECT COUNT(DD_Headcount.EmpID) as Headcount
		FROM DD_Headcount
		WHERE YEAR(DD_Headcount.Year) IN (SELECT Year FROM #tblTemp)
		AND DD_Headcount.copar_id IN (SELECT CorpID FROM #tblCorp)
		AND DD_Headcount.PolicyType = 'OP'
	)tbl

	INSERT INTO #tblCost
	SELECT 'SP', CONVERT(decimal(18,2),((#tblTemp.Amt/#tblTemp.Month)*12)/#tblTemp.Headcount)
	FROM #tblTemp	

END 


IF (@strClaimType = 'IP' OR @strClaimType = 'All')
BEGIN

	UPDATE #tblTemp
	SET #tblTemp.Amt = tbl.Amt
	FROM
	(
		SELECT SUM(DD_SHELL.AmtInsured) as Amt
		FROM DD_SHELL 
		WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
		AND DD_SHELL.DTDISABILITY >= @dtSD
		AND DD_SHELL.DTDISABILITY <= @dtED
		AND DD_SHELL.ClaimType = 'IP'
	)tbl

	UPDATE #tblTemp
	SET #tblTemp.Month = tbl.Mth
	FROM
	(
		SELECT COUNT(Mth) as Mth
		FROM
		(
			SELECT DISTINCT YEAR(DD_SHELL.DTDISABILITY) as Yr, MONTH(DD_SHELL.DTDISABILITY) as Mth
			FROM DD_SHELL
			WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
			AND DD_SHELL.DTDISABILITY >= @dtSD
			AND DD_SHELL.DTDISABILITY <= @dtED
			AND DD_SHELL.ClaimType = 'IP'
		)tblTemp
	)tbl

	UPDATE #tblTemp
	SET #tblTemp.Year = YEAR(@dtSD)
	--FROM
	--(
	--	SELECT YEAR(DD_SHELL.DTDISABILITY) as Yr
	--	FROM DD_SHELL
	--	WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
	--	AND DD_SHELL.DTDISABILITY = @dtSD
	--	AND DD_SHELL.ClaimType = 'IP'
	--)tbl

	UPDATE #tblTemp
	SET #tblTemp.Headcount = tbl.Headcount
	FROM
	(
		SELECT COUNT(DD_Headcount.EmpID) as Headcount
		FROM DD_Headcount
		WHERE YEAR(DD_Headcount.Year) IN (SELECT Year FROM #tblTemp)
		AND DD_Headcount.copar_id IN (SELECT CorpID FROM #tblCorp)
		AND DD_Headcount.PolicyType = 'IP'
	)tbl

	INSERT INTO #tblCost
	SELECT 'IP', CONVERT(decimal(18,2),((#tblTemp.Amt/#tblTemp.Month)*12)/#tblTemp.Headcount)
	FROM #tblTemp	

END


--IF (@strClaimType = 'OTH' OR @strClaimType = 'All')
--BEGIN

--	UPDATE #tblTemp
--	SET #tblTemp.Amt = tbl.Amt
--	FROM
--	(
--		SELECT SUM(DD_SHELL.AmtInsured) as Amt
--		FROM DD_SHELL 
--		WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
--		AND DD_SHELL.DTDISABILITY >= @dtSD
--		AND DD_SHELL.DTDISABILITY <= @dtED
--		AND DD_SHELL.ClaimType = 'OTH'
--	)tbl

--	UPDATE #tblTemp
--	SET #tblTemp.Month = tbl.Mth
--	FROM
--	(
--		SELECT COUNT(Mth) as Mth
--		FROM
--		(
--			SELECT DISTINCT YEAR(DD_SHELL.DTDISABILITY) as Yr, MONTH(DD_SHELL.DTDISABILITY) as Mth
--			FROM DD_SHELL
--			WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
--			AND DD_SHELL.DTDISABILITY >= @dtSD
--			AND DD_SHELL.DTDISABILITY <= @dtED
--			AND DD_SHELL.ClaimType = 'OTH'
--		)tblTemp
--	)tbl

--	UPDATE #tblTemp
--	SET #tblTemp.Year = YEAR(@dtSD)
--	--FROM
--	--(
--	--	SELECT YEAR(DD_SHELL.DTDISABILITY) as Yr
--	--	FROM DD_SHELL
--	--	WHERE DD_SHELL.CorpID IN (SELECT CorpID FROM #tblCorp)
--	--	AND DD_SHELL.DTDISABILITY = @dtSD
--	--	AND DD_SHELL.ClaimType = 'OTH'
--	--)tbl

--	UPDATE #tblTemp
--	SET #tblTemp.Headcount = tbl.Headcount
--	FROM
--	(
--		SELECT COUNT(DD_Headcount.EmpID) as Headcount
--		FROM DD_Headcount
--		WHERE YEAR(DD_Headcount.Year) IN (SELECT Year FROM #tblTemp)
--		AND DD_Headcount.copar_id IN (SELECT CorpID FROM #tblCorp)
--		AND DD_Headcount.PolicyType = 'OP'
--	)tbl

--	INSERT INTO #tblCost
--	SELECT 'OTH', CONVERT(decimal(18,2),((#tblTemp.Amt/#tblTemp.Month)*12)/#tblTemp.Headcount)
--	FROM #tblTemp	

--END


SELECT SUM(#tblCost.CPMPA)
FROM #tblCost

DROP TABLE #tblTemp
DROP TABLE #tblCorp
DROP TABLE #tblCost

END