DECLARE
@Date VARCHAR(8) = '20240101',
@Group VARCHAR(MAX) = '00100A,00110H' --'0012GB,001360,00192P'--001AES,001281,001ECL,001VHS,001BPE'
--@ShowCaptive INT = 1

SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
DROP TABLE IF EXISTS
#PRODUCTS,
#GRP,
#GrpBillExt,
#BillAdjst,
#TPREM_BillAdjst,
#EEBILLF,
#Adj,
#ELG,
#UNPVT_ELG,
#RATES,
#UNPVT_RATES,
#Fees,
#HPMiscFees,
#Data,
#EMP,
#Final

DECLARE @AsOfDate VARCHAR(8) = CONVERT(VARCHAR(8),CONVERT(DATE,CONCAT(CONVERT(VARCHAR(2),MONTH(CONVERT(DATE,@Date))),'/1/',CONVERT(VARCHAR(4),YEAR(CONVERT(DATE,@Date))))),112)
--DECLARE @IsPast BIT = IIF(@AsOfDate < CONVERT(VARCHAR(8),GETDATE(),112),1,0)

CREATE TABLE #PRODUCTS
(
[IsIProduct] [INT] NULL,
[PRODUCT] [VARCHAR](2) NULL,
[NAME] [VARCHAR](9) NULL,
[DESC] [VARCHAR](30) NULL,
[SortBy] [INT] NULL
)

CREATE TABLE #GRP
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[NAME] [VARCHAR](30) NULL,
[MaxBillDate] [VARCHAR](8) NULL,
[SSNCERT] [VARCHAR](4) NULL,
[IsPast] [BIT] NULL,
[IsExtS] [BIT] NULL
)

CREATE TABLE #GrpBillExt
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[LAST] [VARCHAR](15) NULL,
[FIRST] [VARCHAR](15) NULL,
[MINIT] [VARCHAR](1) NULL,
[STATUS] [VARCHAR](1) NULL,
[PRODUCT] [VARCHAR](2) NULL,
[BENPLAN] [VARCHAR](10) NULL,
[DEPSTAT] [VARCHAR](1) NULL,
[PLAN] [VARCHAR](9) NULL,
[ADJSW] [VARCHAR](1) NULL,
[COVGDATE] [VARCHAR](8) NULL,
[COVGTHRU] [VARCHAR](8) NULL,
[FEEBKR] [VARCHAR](12) NULL,
[MiscFeeDesc] [VARCHAR](30) NULL,
[PREM] [VARCHAR](12) NULL
)

CREATE TABLE #BillAdjst
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[EmpName] [VARCHAR](70) NOT NULL,
[ADJTYPE] [VARCHAR](1) NULL,
[STATUS] [VARCHAR](1) NULL,
[COVGTier] [VARCHAR](7) NULL,
[COVGDATE] [VARCHAR](8) NULL,
[COVGTHRU] [VARCHAR](8) NULL,
[PRODUCT] [VARCHAR](2) NULL,
[PLAN] [VARCHAR](9) NULL,
[IsCAP][INT] NULL,
[FEEBKR] [VARCHAR] (20) NULL,
)

CREATE TABLE #TPREM_BillAdjst
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[TPREMIUM] [MONEY] NULL,
[TPREMIUM_CAP] [MONEY] NULL
)

CREATE TABLE #EEBILLF
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[TFEES] [MONEY] NULL,
[TFEES_CAP] [MONEY] NULL
)

CREATE TABLE #Adj
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[EmpName] [VARCHAR](70) NOT NULL,
[STATUS] [VARCHAR](1) NULL,
[COVGTier] [VARCHAR](7) NULL,
[COVGDATE] [VARCHAR](8) NULL,
[COVGTHRU] [VARCHAR](8) NULL,
[PRODUCT] [VARCHAR](2) NULL,
[BENPLAN] [VARCHAR](10) NULL,
[PLAN] [VARCHAR](9) NULL,
[ADJTYPE] [VARCHAR](1) NULL,
[ADJUSTMENT] [MONEY] NULL,
[IsCAP] [INT] NULL,
[ADJUSTMENT_CAP] [MONEY] NULL,
[FEEBKR] [VARCHAR] (8) NULL,
)

CREATE TABLE #ELG
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[EmpName] [VARCHAR](70) NULL,
[STATUS] [VARCHAR](1) NULL,
[DEPMM] [VARCHAR](1) NULL,
[DEPDE] [VARCHAR](1) NULL,
[DEPRX] [VARCHAR](1) NULL,
[DEPVI] [VARCHAR](1) NULL,
[DEPWC] [VARCHAR](1) NULL,
[BENEFITMM] [VARCHAR](10) NULL,
[BENEFITDE] [VARCHAR](10) NULL,
[BENEFITRX] [VARCHAR](10) NULL,
[BENEFITVI] [VARCHAR](10) NULL,
[BENEFITWC] [VARCHAR](10) NULL,
--[BENEFITWI] [VARCHAR](10) NULL,
--[BENEFITLT] [VARCHAR](10) NULL,
[INSPRODI1] [VARCHAR](2) NULL,
[INSPRODI2] [VARCHAR](2) NULL,
[INSPRODI3] [VARCHAR](2) NULL,
[INSPRODI4] [VARCHAR](2) NULL,
[INSPRODI5] [VARCHAR](2) NULL,
[INSPRODI6] [VARCHAR](2) NULL,
[INSPRODI7] [VARCHAR](2) NULL,
[INSPRODI8] [VARCHAR](2) NULL,
[PLANMM] [VARCHAR](9) NULL,
[PLANDE] [VARCHAR](9) NULL,
[PLANRX] [VARCHAR](9) NULL,
[PLANVI] [VARCHAR](9) NULL,
[PLANWC] [VARCHAR](9) NULL,
--[PLANWI] [VARCHAR](9) NULL,
--[PLANLT] [VARCHAR](9) NULL,
[PLANI1] [VARCHAR](9) NULL,
[PLANI2] [VARCHAR](9) NULL,
[PLANI3] [VARCHAR](9) NULL,
[PLANI4] [VARCHAR](9) NULL,
[PLANI5] [VARCHAR](9) NULL,
[PLANI6] [VARCHAR](9) NULL,
[PLANI7] [VARCHAR](9) NULL,
[PLANI8] [VARCHAR](9) NULL,
[eRank] [INT] NULL
)

CREATE TABLE #UNPVT_ELG
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[EmpName] [VARCHAR](70) NULL,
[STATUS] [VARCHAR](1) NULL,
[PRODUCT] [VARCHAR](2) NULL,
[BENEFIT] [VARCHAR](10) NULL,
[DEP] [VARCHAR](1) NULL,
[BILL_PLAN] [VARCHAR](9) NULL,
[IsIProduct] [INT] NULL,
[ProductSortBy] [INT] NULL
)

CREATE TABLE #RATES
(
[PRODUCT] [VARCHAR](2) NULL,
[PLAN] [VARCHAR](9) NULL,
[EFFDATE] [VARCHAR](8) NULL,
[FBKR_1] [VARCHAR](3) NULL,
[FBKR_2] [VARCHAR](3) NULL,
[FBKR_3] [VARCHAR](3) NULL,
[FBKR_4] [VARCHAR](3) NULL,
[FBKR_5] [VARCHAR](3) NULL,
[FBKR_6] [VARCHAR](3) NULL,
[FBKR_7] [VARCHAR](3) NULL,
[FBKR_8] [VARCHAR](3) NULL,
[FBKR_9] [VARCHAR](3) NULL,
[FBKR_10] [VARCHAR](3) NULL,
[DFEMP_1] [VARCHAR](8) NULL,
[DFEMP_2] [VARCHAR](8) NULL,
[DFEMP_3] [VARCHAR](8) NULL,
[DFEMP_4] [VARCHAR](8) NULL,
[DFEMP_5] [VARCHAR](8) NULL,
[DFEMP_6] [VARCHAR](8) NULL,
[DFEMP_7] [VARCHAR](8) NULL,
[DFEMP_8] [VARCHAR](8) NULL,
[DFEMP_9] [VARCHAR](8) NULL,
[DFEMP_10] [VARCHAR](8) NULL,
[DFFAM_1] [VARCHAR](8) NULL,
[DFFAM_2] [VARCHAR](8) NULL,
[DFFAM_3] [VARCHAR](8) NULL,
[DFFAM_4] [VARCHAR](8) NULL,
[DFFAM_5] [VARCHAR](8) NULL,
[DFFAM_6] [VARCHAR](8) NULL,
[DFFAM_7] [VARCHAR](8) NULL,
[DFFAM_8] [VARCHAR](8) NULL,
[DFFAM_9] [VARCHAR](8) NULL,
[DFFAM_10] [VARCHAR](8) NULL,
[DFSPO_1] [VARCHAR](8) NULL,
[DFSPO_2] [VARCHAR](8) NULL,
[DFSPO_3] [VARCHAR](8) NULL,
[DFSPO_4] [VARCHAR](8) NULL,
[DFSPO_5] [VARCHAR](8) NULL,
[DFSPO_6] [VARCHAR](8) NULL,
[DFSPO_7] [VARCHAR](8) NULL,
[DFSPO_8] [VARCHAR](8) NULL,
[DFSPO_9] [VARCHAR](8) NULL,
[DFSPO_10] [VARCHAR](8) NULL,
[DFCHL_1] [VARCHAR](8) NULL,
[DFCHL_2] [VARCHAR](8) NULL,
[DFCHL_3] [VARCHAR](8) NULL,
[DFCHL_4] [VARCHAR](8) NULL,
[DFCHL_5] [VARCHAR](8) NULL,
[DFCHL_6] [VARCHAR](8) NULL,
[DFCHL_7] [VARCHAR](8) NULL,
[DFCHL_8] [VARCHAR](8) NULL,
[DFCHL_9] [VARCHAR](8) NULL,
[DFCHL_10] [VARCHAR](8) NULL,
[DFPREM_1] [VARCHAR](8) NULL,
[DFPREM_2] [VARCHAR](8) NULL,
[DFPREM_3] [VARCHAR](8) NULL,
[DFPREM_4] [VARCHAR](8) NULL,
[DFPREM_5] [VARCHAR](8) NULL,
[DFPREM_6] [VARCHAR](8) NULL,
[DFPREM_7] [VARCHAR](8) NULL,
[DFPREM_8] [VARCHAR](8) NULL,
[DFPREM_9] [VARCHAR](8) NULL,
[DFPREM_10] [VARCHAR](8) NULL,
[DEMP_1] [VARCHAR](9) NULL,
[DEMP_2] [VARCHAR](9) NULL,
[DEMP_3] [VARCHAR](9) NULL,
[DEMP_4] [VARCHAR](9) NULL,
[DEMP_5] [VARCHAR](9) NULL,
[DEMP_6] [VARCHAR](9) NULL,
[DEMP_7] [VARCHAR](9) NULL,
[DEMP_8] [VARCHAR](9) NULL,
[DEMP_9] [VARCHAR](9) NULL,
[DEMP_10] [VARCHAR](9) NULL,
[DFAM_1] [VARCHAR](9) NULL,
[DFAM_2] [VARCHAR](9) NULL,
[DFAM_3] [VARCHAR](9) NULL,
[DFAM_4] [VARCHAR](9) NULL,
[DFAM_5] [VARCHAR](9) NULL,
[DFAM_6] [VARCHAR](9) NULL,
[DFAM_7] [VARCHAR](9) NULL,
[DFAM_8] [VARCHAR](9) NULL,
[DFAM_9] [VARCHAR](9) NULL,
[DFAM_10] [VARCHAR](9) NULL,
[DSPO_1] [VARCHAR](9) NULL,
[DSPO_2] [VARCHAR](9) NULL,
[DSPO_3] [VARCHAR](9) NULL,
[DSPO_4] [VARCHAR](9) NULL,
[DSPO_5] [VARCHAR](9) NULL,
[DSPO_6] [VARCHAR](9) NULL,
[DSPO_7] [VARCHAR](9) NULL,
[DSPO_8] [VARCHAR](9) NULL,
[DSPO_9] [VARCHAR](9) NULL,
[DSPO_10] [VARCHAR](9) NULL,
[DCHL_1] [VARCHAR](9) NULL,
[DCHL_2] [VARCHAR](9) NULL,
[DCHL_3] [VARCHAR](9) NULL,
[DCHL_4] [VARCHAR](9) NULL,
[DCHL_5] [VARCHAR](9) NULL,
[DCHL_6] [VARCHAR](9) NULL,
[DCHL_7] [VARCHAR](9) NULL,
[DCHL_8] [VARCHAR](9) NULL,
[DCHL_9] [VARCHAR](9) NULL,
[DCHL_10] [VARCHAR](9) NULL,
[DPREM_1] [VARCHAR](9) NULL,
[DPREM_2] [VARCHAR](9) NULL,
[DPREM_3] [VARCHAR](9) NULL,
[DPREM_4] [VARCHAR](9) NULL,
[DPREM_5] [VARCHAR](9) NULL,
[DPREM_6] [VARCHAR](9) NULL,
[DPREM_7] [VARCHAR](9) NULL,
[DPREM_8] [VARCHAR](9) NULL,
[DPREM_9] [VARCHAR](9) NULL,
[DPREM_10] [VARCHAR](9) NULL
)

CREATE TABLE #UNPVT_RATES
(
[PRODUCT] [VARCHAR](2) NULL,
[PLAN] [VARCHAR](9) NULL,
[EFFDATE] [VARCHAR](8) NULL,
[Iteration] [INT] NULL,
[FBKR] [VARCHAR](3) NULL,
[DFEMP] [VARCHAR](8) NULL,
[DFFAM] [VARCHAR](8) NULL,
[DFSPO] [VARCHAR](8) NULL,
[DFCHL] [VARCHAR](8) NULL,
[DFPREM] [VARCHAR](8) NULL,
[DEMP] [VARCHAR](9) NULL,
[DFAM] [VARCHAR](9) NULL,
[DSPO] [VARCHAR](9) NULL,
[DCHL] [VARCHAR](9) NULL,
[DPREM] [VARCHAR](9) NULL
)

CREATE TABLE #Fees
(
[PUNBR] [varchar](3) NULL,
[GRNBR] [varchar](3) NULL,
[DESC_1] [varchar](30) NULL,
[DESC_2] [varchar](30) NULL,
[DESC_3] [varchar](30) NULL,
[DESC_4] [varchar](30) NULL,
[DESC_5] [varchar](30) NULL,
[DESC_6] [varchar](30) NULL,
[DESC_7] [varchar](30) NULL,
[DESC_8] [varchar](30) NULL,
[DESC_9] [varchar](30) NULL,
[DESC_10] [varchar](30) NULL,
[DESC_11] [varchar](30) NULL,
[DESC_12] [varchar](30) NULL,
[DESC_13] [varchar](30) NULL,
[DESC_14] [varchar](30) NULL,
[TYPE_1] [varchar](1) NULL,
[TYPE_2] [varchar](1) NULL,
[TYPE_3] [varchar](1) NULL,
[TYPE_4] [varchar](1) NULL,
[TYPE_5] [varchar](1) NULL,
[TYPE_6] [varchar](1) NULL,
[TYPE_7] [varchar](1) NULL,
[TYPE_8] [varchar](1) NULL,
[TYPE_9] [varchar](1) NULL,
[TYPE_10] [varchar](1) NULL,
[TYPE_11] [varchar](1) NULL,
[TYPE_12] [varchar](1) NULL,
[TYPE_13] [varchar](1) NULL,
[TYPE_14] [varchar](1) NULL,
[DAMOUNT_1] [varchar](12) NULL,
[DAMOUNT_2] [varchar](12) NULL,
[DAMOUNT_3] [varchar](12) NULL,
[DAMOUNT_4] [varchar](12) NULL,
[DAMOUNT_5] [varchar](12) NULL,
[DAMOUNT_6] [varchar](12) NULL,
[DAMOUNT_7] [varchar](12) NULL,
[DAMOUNT_8] [varchar](12) NULL,
[DAMOUNT_9] [varchar](12) NULL,
[DAMOUNT_10] [varchar](12) NULL,
[DAMOUNT_11] [varchar](12) NULL,
[DAMOUNT_12] [varchar](12) NULL,
[DAMOUNT_13] [varchar](12) NULL,
[DAMOUNT_14] [varchar](12) NULL,
[BKR_1] [varchar](3) NULL,
[BKR_2] [varchar](3) NULL,
[BKR_3] [varchar](3) NULL,
[BKR_4] [varchar](3) NULL,
[BKR_5] [varchar](3) NULL,
[BKR_6] [varchar](3) NULL,
[BKR_7] [varchar](3) NULL,
[BKR_8] [varchar](3) NULL,
[BKR_9] [varchar](3) NULL,
[BKR_10] [varchar](3) NULL,
[BKR_11] [varchar](3) NULL,
[BKR_12] [varchar](3) NULL,
[BKR_13] [varchar](3) NULL,
[BKR_14] [varchar](3) NULL
)

CREATE TABLE #HPMiscFees
(
[Underwriter] [varchar](3) NULL,
[Group] [varchar](3) NULL,
[Misc Fee Description] [varchar](30) NULL,
[Misc Fee Type] [varchar](1) NULL,
[Misc Fee Amount] [varchar](12) NULL,
[Misc Fee Broker Code] [varchar](3) NULL,
[SortBy] [int] NULL
)

CREATE TABLE #Data
(
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[EmpName] [VARCHAR](70) NULL,
[STATUS] [VARCHAR](1) NULL,
[PRODUCT] [VARCHAR](2) NULL,
[BENEFIT] [VARCHAR](10) NULL,
[DEP] [VARCHAR](7) NULL,
[BILL_PLAN] [VARCHAR](9) NULL,
[PREM] [MONEY] NULL,
[IsCAP] [INT] NULL,
[PREM_CAP] [MONEY] NULL,
[SortBy] [INT] NULL,
[IsIProduct] [INT] NULL,
[ProductSortBy] [INT] NULL,
[AdjSortBy] [INT] NULL,
[FEEBKR] [VARCHAR](20) NULL
)

--CREATE TABLE #EMP
--(
-- [PUNBR] [VARCHAR](3) NULL,
-- [GRNBR] [VARCHAR](3) NULL,
-- [ESSN] [VARCHAR](9) NULL,
-- [CERT] [VARCHAR](12) NULL,
-- [EmpName] [VARCHAR](70) NULL,
-- [STATUS] [VARCHAR](1) NULL
--)

CREATE TABLE #Final
(
[Level] [INT] NULL,
[PUNBR] [VARCHAR](3) NULL,
[GRNBR] [VARCHAR](3) NULL,
[GroupName] [VARCHAR](30) NULL,
[IsPast] [BIT] NULL,
[PRODUCT] [VARCHAR](2) NULL,
[IsIProduct] [INT] NULL,
[ProductName] [VARCHAR](9) NULL,
[ProductDescription] [VARCHAR](30) NULL,
[ProductSortBy] [INT] NULL,
[DataSortBy] [INT] NULL,
[AdjSortBy] [INT] NULL,
[ESSN] [VARCHAR](9) NULL,
[CERT] [VARCHAR](12) NULL,
[EmpName] [VARCHAR](70) NULL,
[STATUS] [VARCHAR](1) NULL,
[BENEFIT] [VARCHAR](10) NULL,
[DEP] [VARCHAR](7) NULL,
[BILL_PLAN] [VARCHAR](9) NULL,
[PREM] [MONEY] NULL,
[IsCAP] [INT] NULL,
[PREM_CAP] [MONEY] NULL,
[feebkr] [VARCHAR](20) NULL,
)

INSERT INTO #PRODUCTS
SELECT 0 AS 'IsIProduct',
CONVERT(VARCHAR(2),[value]) AS 'PRODUCT',
CONVERT(VARCHAR(10),CASE [value]
WHEN 'MM' THEN 'MEDICAL'
WHEN 'DE' THEN 'DENTAL'
WHEN 'RX' THEN 'ANCILLARY'
WHEN 'VI' THEN 'VISION'
WHEN 'WC' THEN 'WORK COMP' /?/
WHEN 'WI' THEN 'WI' /?/
WHEN 'LT' THEN 'LT' /?/ END) AS 'NAME',
CONVERT(VARCHAR(30),CASE [value]
WHEN 'MM' THEN 'MEDICAL'
WHEN 'DE' THEN 'DENTAL'
WHEN 'RX' THEN 'ANCILLARY'
WHEN 'VI' THEN 'VISION'
WHEN 'WC' THEN 'WORK COMP' /?/
WHEN 'WI' THEN 'WI' /?/
WHEN 'LT' THEN 'LT' /?/ END) AS 'DESC',
CASE [value]
WHEN 'MM' THEN 1
WHEN 'DE' THEN 2
WHEN 'RX' THEN 3
WHEN 'VI' THEN 4
WHEN 'WC' THEN 5 /?/
WHEN 'WI' THEN 6 /?/
WHEN 'LT' THEN 7 /?/ END 'SortBy'
FROM STRING_SPLIT('MM,DE,RX,VI,WC'/,WI,LT'/,',') s
UNION ALL
SELECT 1,
UPPER(PRODUCT),
UPPER(LTRIM(RTRIM([NAME]))),
UPPER([DESC]),
ROW_NUMBER() OVER (ORDER BY PRODUCT)
FROM [HlthPC].dbo.INS
UNION ALL
SELECT 2,
'MF',
'MISC FEE',
'MISC FEE',
0

INSERT INTO #GRP
SELECT g.PUNBR,
g.GRNBR,
g.[NAME],
b.MaxBillDate,
IIF(ISNULL(e.BILLFMT,'') = 'b9gb2871','ESSN','CERT') AS 'SSNCERT',
IIF(@AsOfDate > b.MaxBillDate,0,1) AS 'IsPast',
IIF(ISNULL(r.PUNBR,'') = '',0,1) AS 'IsExtS'

FROM [HlthPC].dbo.GRP g
JOIN STRING_SPLIT(@Group,',') s
ON CONCAT(g.PUNBR,g.GRNBR) = s.[value]
LEFT JOIN (SELECT PUNBR,
GRNBR,
MAX(BILLDATE) AS 'MaxBillDate'

FROM [HlthPC].dbo.BILLHIST
WHERE INFOTYPE = 'S'
GROUP BY PUNBR,
GRNBR) b
ON g.PUNBR = b.PUNBR
AND g.GRNBR = b.GRNBR
LEFT JOIN (SELECT PUNBR,
GRNBR,
MAX(BILLDATE) AS 'MaxBillDate'
FROM [HlthPC].dbo.GrpBillExtS
WHERE BILLDATE <= @AsOfDate
GROUP BY PUNBR,
GRNBR) m
ON g.PUNBR = m.PUNBR
AND g.GRNBR = m.GRNBR
LEFT JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
BILLDATE,
BILLFMT
FROM [HlthPC].dbo.GrpBillExtS) e
ON g.PUNBR = e.PUNBR
AND g.GRNBR = e.GRNBR
AND m.MaxBillDate = e.BILLDATE
LEFT JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
BILLDATE
FROM [HlthPC].dbo.GrpBillExtS) r
ON g.PUNBR = r.PUNBR
AND g.GRNBR = r.GRNBR
AND @AsOfDate = r.BILLDATE
--WHERE @AsOfDate >= g.EFFDATE
-- AND @AsOfDate < ISNULL(NULLIF(g.TRMDATE,''),'99999999') --Commented to allow parameter filter to fully drive Group selection

IF (SELECT COUNT(1) FROM #GRP WHERE IsPast = 1) > 0
BEGIN

INSERT INTO #GrpBillExt
SELECT g.PUNBR, ---past invoices already had feebroker
g.GRNBR,
f.ESSN,
f.[CERT],
f.[LAST],
f.[FIRST],
f.MINIT,
f.[STATUS],
f.PRODUCT,
f.BENPLAN,
f.DEPSTAT,
f.[PLAN],
f.ADJSW,
f.COVGDATE,
f.COVGTHRU,
f.FEEBKR,
CONVERT(VARCHAR(30),'') AS 'MiscFeeDesc',
f.DFEE AS 'PREM'
FROM #GRP g
JOIN [HlthPC].dbo.GrpBillExtCF f
ON g.PUNBR = f.PUNBR
AND g.GRNBR = f.GRNBR
AND @AsOfDate = f.BILLDATE
WHERE g.IsPast = 1
AND g.IsExtS = 1
UNION ALL
SELECT g.PUNBR,
g.GRNBR,
r.ESSN,
r.[CERT],
r.[LAST],
r.[FIRST],
r.MINIT,
r.[STATUS],
r.PRODUCT,
r.BENPLAN,
r.DEPSTAT,
r.[PLAN],
r.ADJSW,
r.COVGDATE,
r.COVGTHRU,
r.CARRIER,
'',
r.DPREMIUM
FROM #GRP g
JOIN [HlthPC].dbo.GrpBillExtCR r
ON g.PUNBR = r.PUNBR
AND g.GRNBR = r.GRNBR
AND @AsOfDate = r.BILLDATE
WHERE g.IsPast = 1
AND g.IsExtS = 1
UNION ALL
SELECT g.PUNBR,
g.GRNBR,
'',
'',
'',
'',
'',
'P',
'MF',
'',
'',
'',
'',
'',
'',
m.GFEEBKR,
m.GFEEDESC,
m.GDFEE
FROM #GRP g
JOIN [HlthPC].dbo.GrpBillExtG m
ON g.PUNBR = m.PUNBR
AND g.GRNBR = m.GRNBR
AND @AsOfDate = m.BILLDATE
WHERE g.IsPast = 1
AND g.IsExtS = 1

UPDATE g
SET g.BENPLAN = x.BENEFIT
FROM #GrpBillExt g
JOIN (SELECT d.PUNBR,
d.GRNBR,
d.ESSN,
d.PRODUCT,
d.COVGDATE,
d.COVGTHRU,
e.BENEFIT
FROM (SELECT DISTINCT
PUNBR,
GRNBR,
ESSN,
PRODUCT,
COVGDATE,
COVGTHRU
FROM #GrpBillExt
WHERE ADJSW = 'Y') d
JOIN (SELECT PUNBR,
GRNBR,
ESSN,
EFFDATE,
TRMDATE,
TRY_CONVERT(VARCHAR(2),RIGHT(u.ColName,2)) as 'PRODUCT',
ColData as 'BENEFIT'
FROM [HlthPC].dbo.ELG l
UNPIVOT (ColData FOR ColName IN (BENEFITMM,BENEFITDE,BENEFITRX,BENEFITVI,BENEFITWC/,BENEFITWI,BENEFITLT/)) u
WHERE ColData <> '') e
ON d.PUNBR = e.PUNBR
AND d.GRNBR = e.GRNBR
AND d.ESSN = e.ESSN
AND d.PRODUCT = e.PRODUCT
AND NOT ((d.COVGDATE > e.EFFDATE AND d.COVGDATE >= e.TRMDATE)
OR (d.COVGTHRU <= e.EFFDATE AND d.COVGTHRU < e.TRMDATE))) x
ON g.PUNBR = x.PUNBR
AND g.GRNBR = x.GRNBR
AND g.ESSN = x.ESSN
AND g.PRODUCT = x.PRODUCT
AND g.COVGDATE = x.COVGDATE
AND g.COVGTHRU = x.COVGTHRU
WHERE g.ADJSW = 'Y'

INSERT INTO #Data
SELECT g.PUNBR,
g.GRNBR,
b.ESSN,
b.[CERT],
CONCAT(LTRIM(RTRIM(b.[LAST])),', ' + LTRIM(RTRIM(b.[FIRST])),' ' + NULLIF(RTRIM(b.MINIT),'') + '.',IIF(ISNULL(b.ADJSW,'') = 'Y',' | ' +
FORMAT(CONVERT(DATE,b.COVGDATE),'MM/dd/yyyy') + ' - ' + FORMAT(CONVERT(DATE,b.COVGTHRU),'MM/dd/yyyy'),'')),
b.[STATUS],
p.PRODUCT,
ISNULL(NULLIF(b.BENPLAN,''),b.[PLAN]),
IIF(p.IsIProduct = 1,CASE WHEN b.[PLAN] LIKE '%EE%' THEN 'EE only'
WHEN b.[PLAN] LIKE '%EF%' THEN 'Family'
WHEN b.[PLAN] LIKE '%ES%' THEN 'EE + Sp'
WHEN b.[PLAN] LIKE '%EC%' THEN 'EE + Ch' END,
CASE b.DEPSTAT
WHEN 'N' THEN 'EE only'
WHEN 'F' THEN 'Family'
WHEN 'S' THEN 'EE + Sp'
WHEN 'C' THEN 'EE + Ch' END),
b.[PLAN],
ISNULL(SUM(TRY_CONVERT(MONEY,PREM)),0),
MAX(IIF(b.FEEBKR = 'CAP' ,1,0)),
ISNULL(SUM(IIF(b.FEEBKR = 'CAP',TRY_CONVERT(MONEY,PREM),0)),0),
IIF(ISNULL(b.ADJSW,'') = 'Y',0,-1),
IIF(ISNULL(b.ADJSW,'') = 'Y',0,p.IsIProduct),
IIF(ISNULL(b.ADJSW,'') = 'Y',0,p.SortBy),
-1,
b.FEEBKR
FROM #GRP g
CROSS JOIN #PRODUCTS p
LEFT JOIN #GrpBillExt b
ON g.PUNBR = b.PUNBR
AND g.GRNBR = b.GRNBR
AND p.PRODUCT = b.PRODUCT
WHERE g.IsPast = 1
AND p.PRODUCT <> 'MF'
GROUP BY g.PUNBR,
g.GRNBR,
b.ESSN,
b.[CERT],
CONCAT(LTRIM(RTRIM(b.[LAST])),', ' + LTRIM(RTRIM(b.[FIRST])),' ' + NULLIF(RTRIM(b.MINIT),'') + '.',IIF(ISNULL(b.ADJSW,'') = 'Y',' | ' +
FORMAT(CONVERT(DATE,b.COVGDATE),'MM/dd/yyyy') + ' - ' + FORMAT(CONVERT(DATE,b.COVGTHRU),'MM/dd/yyyy'),'')),
b.[STATUS],
p.PRODUCT,
b.BENPLAN,
b.DEPSTAT,
b.[PLAN],
b.ADJSW,
p.IsIProduct,
p.SortBy,
B.FEEBKR
HAVING ISNULL(SUM(TRY_CONVERT(MONEY,PREM)),0) <> 0
UNION ALL
SELECT g.PUNBR,
g.GRNBR,
IIF(ISNULL(b.MiscFeeDesc,'') LIKE '% GHOST %','',ISNULL(b.FEEBKR,'')),
IIF(ISNULL(b.MiscFeeDesc,'') LIKE '% GHOST %','',ISNULL(b.FEEBKR,'')),
ISNULL(b.MiscFeeDesc,''),
ISNULL(b.[STATUS],''),
ISNULL(b.PRODUCT,''),
'',
'',
'',
ISNULL(SUM(TRY_CONVERT(MONEY,b.PREM)),0),
0,
0,
1,
0,
0,
-1,
b.FEEBKR
FROM #GRP g
CROSS JOIN #PRODUCTS p
JOIN #GrpBillExt b
ON g.PUNBR = b.PUNBR
AND g.GRNBR = b.GRNBR
AND p.PRODUCT = b.PRODUCT
WHERE g.IsPast = 1
AND p.PRODUCT = 'MF'
GROUP BY g.PUNBR,
g.GRNBR,
IIF(ISNULL(b.MiscFeeDesc,'') LIKE '% GHOST %','',ISNULL(b.FEEBKR,'')),
IIF(ISNULL(b.MiscFeeDesc,'') LIKE '% GHOST %','',ISNULL(b.FEEBKR,'')),
ISNULL(b.MiscFeeDesc,''),
ISNULL(b.[STATUS],''),
ISNULL(b.PRODUCT,''), b.FEEBKR
HAVING ISNULL(SUM(TRY_CONVERT(MONEY,PREM)),0) <> 0

END

-- Continuation of the #PRODUCTS insert statement
UNION ALL
SELECT 1 AS 'IsIProduct',
CONVERT(VARCHAR(2),[value]) AS 'PRODUCT',
CONVERT(VARCHAR(10),CASE [value]
WHEN 'I1' THEN 'INSURANCE1'
WHEN 'I2' THEN 'INSURANCE2'
WHEN 'I3' THEN 'INSURANCE3'
WHEN 'I4' THEN 'INSURANCE4'
WHEN 'I5' THEN 'INSURANCE5'
WHEN 'I6' THEN 'INSURANCE6'
WHEN 'I7' THEN 'INSURANCE7'
WHEN 'I8' THEN 'INSURANCE8' END) AS 'NAME',
CONVERT(VARCHAR(30),CASE [value]
WHEN 'I1' THEN 'INSURANCE1'
WHEN 'I2' THEN 'INSURANCE2'
WHEN 'I3' THEN 'INSURANCE3'
WHEN 'I4' THEN 'INSURANCE4'
WHEN 'I5' THEN 'INSURANCE5'
WHEN 'I6' THEN 'INSURANCE6'
WHEN 'I7' THEN 'INSURANCE7'
WHEN 'I8' THEN 'INSURANCE8' END) AS 'DESC',
CASE [value]
WHEN 'I1' THEN 8
WHEN 'I2' THEN 9
WHEN 'I3' THEN 10
WHEN 'I4' THEN 11
WHEN 'I5' THEN 12
WHEN 'I6' THEN 13
WHEN 'I7' THEN 14
WHEN 'I8' THEN 15 END 'SortBy'
FROM STRING_SPLIT('I1,I2,I3,I4,I5,I6,I7,I8',',') s;

-- Assuming similar insertions for other tables and data manipulations

-- Further queries and operations...

IF (SELECT COUNT(1) FROM #GRP WHERE IsPast = 0) > 0
BEGIN

INSERT INTO #BillAdjst
SELECT b.PUNBR, --adjustment for future
b.GRNBR,
b.ESSN,
e.[CERT],
CONCAT(LTRIM(RTRIM(e.LNAME)),', ' + LTRIM(RTRIM(e.FTNAME)),' ' + NULLIF(RTRIM(e.MINIT),'') + '.',' | ' +
FORMAT(CONVERT(DATE,b.COVGDATE),'MM/dd/yyyy'),' - ' + FORMAT(CONVERT(DATE,b.COVGTHRU),'MM/dd/yyyy')),
--b.BILLDATE,
b.ADJTYPE,
b.[STATUS],
IIF(p.IsIProduct = 1,CASE WHEN b.[PLAN] LIKE '%EE%' THEN 'EE only'
WHEN b.[PLAN] LIKE '%EF%' THEN 'Family'
WHEN b.[PLAN] LIKE '%ES%' THEN 'EE + Sp'
WHEN b.[PLAN] LIKE '%EC%' THEN 'EE + Ch' END,
CASE b.DEPSTAT
WHEN 'N' THEN 'EE only'
WHEN 'F' THEN 'Family'
WHEN 'S' THEN 'EE + Sp'
WHEN 'C' THEN 'EE + Ch' END),
b.COVGDATE,
b.COVGTHRU,
b.PRODUCT,
b.[PLAN],
MAX(IIF(b.CARRIER = 'CAP',1,0)) ,
b.carrier a--add fbkr here

FROM [HlthPC].dbo.BillAdjst b
JOIN #PRODUCTS p
ON b.PRODUCT = p.PRODUCT
JOIN #GRP g
ON b.PUNBR = g.PUNBR
AND b.GRNBR = g.GRNBR
AND b.BILLDATE >= g.MaxBillDate
AND g.IsPast = 0
LEFT JOIN [HlthPC].dbo.EMP e
ON b.PUNBR = e.PUNBR
AND b.GRNBR = e.GRNBR
AND b.ESSN = e.ESSN
--WHERE b.ADJTYPE = 'N'
GROUP BY b.PUNBR,
b.GRNBR,
b.ESSN,
e.[CERT],
CONCAT(LTRIM(RTRIM(e.LNAME)),', ' + LTRIM(RTRIM(e.FTNAME)),' ' + NULLIF(RTRIM(e.MINIT),'') + '.',' | ' +
FORMAT(CONVERT(DATE,b.COVGDATE),'MM/dd/yyyy'),' - ' + FORMAT(CONVERT(DATE,b.COVGTHRU),'MM/dd/yyyy')),
--b.BILLDATE,
b.ADJTYPE,
b.[STATUS],
IIF(p.IsIProduct = 1,CASE WHEN b.[PLAN] LIKE '%EE%' THEN 'EE only'
WHEN b.[PLAN] LIKE '%EF%' THEN 'Family'
WHEN b.[PLAN] LIKE '%ES%' THEN 'EE + Sp'
WHEN b.[PLAN] LIKE '%EC%' THEN 'EE + Ch' END,
CASE b.DEPSTAT
WHEN 'N' THEN 'EE only'
WHEN 'F' THEN 'Family'
WHEN 'S' THEN 'EE + Sp'
WHEN 'C' THEN 'EE + Ch' END),
b.COVGDATE,
b.COVGTHRU,
b.PRODUCT,
b.[PLAN],
b.carrier

INSERT INTO #TPREM_BillAdjst
SELECT a.PUNBR,
a.GRNBR,
a.ESSN,
ISNULL(SUM(TRY_CONVERT(MONEY,NULLIF(REPLACE(a.PREMIUM,'.00-',''),' '))),0) 'TPREMIUM',
ISNULL(SUM(IIF(a.CARRIER = 'CAP', TRY_CONVERT(MONEY,NULLIF(REPLACE(a.PREMIUM,'.00-',''),' ')),0)),0) 'TPREMIUM_CAP'
FROM [HlthPC].dbo.BillAdjst a
JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
ESSN
FROM #BillAdjst) b
ON a.PUNBR = b.PUNBR
AND a.GRNBR = b.GRNBR
AND a.ESSN = b.ESSN
GROUP BY a.PUNBR,
a.GRNBR,
a.ESSN

INSERT INTO #EEBILLF
SELECT e.PUNBR,
e.GRNBR,
e.ESSN,
ISNULL(SUM(TRY_CONVERT(MONEY,e.FEE)),0) AS 'TFEES',
ISNULL(SUM(IIF(e.CARRIER ='CAP',TRY_CONVERT(MONEY,e.FEE),0)),0) AS 'TFEES_CAP'
FROM [HlthPC].dbo.EEBILLF e
JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
ESSN
FROM #BillAdjst) b
ON e.PUNBR = b.PUNBR
AND e.GRNBR = b.GRNBR
AND e.ESSN = b.ESSN
WHERE e.ADJSW = 'Y'
GROUP BY e.PUNBR,
e.GRNBR,
e.ESSN

INSERT INTO #Adj
SELECT b.PUNBR,
b.GRNBR,
b.ESSN,
b.[CERT],
b.EmpName,
b.[STATUS],
b.COVGTier,
b.COVGDATE,
b.COVGTHRU,
b.PRODUCT,
'',
b.[PLAN],
b.ADJTYPE,
SUM(ISNULL(t.TPREMIUM,0) + ISNULL(f.TFEES,0)) AS 'ADJUSTMENT',
b.[IsCAP],
SUM(ISNULL(t.TPREMIUM_CAP,0) + ISNULL(f.TFEES_CAP,0)) AS 'ADJUSTMENT_CAP',
FEEBKR

FROM #BillAdjst b
LEFT JOIN #TPREM_BillAdjst t
ON b.PUNBR = t.PUNBR
AND b.GRNBR = t.GRNBR
AND b.ESSN = t.ESSN
LEFT JOIN #EEBILLF f
ON b.PUNBR = f.PUNBR
AND b.GRNBR = f.GRNBR
AND b.ESSN = f.ESSN
GROUP BY b.PUNBR,
b.GRNBR,
b.ESSN,
b.[CERT],
b.EmpName,
b.[STATUS],
b.COVGTier,
b.COVGDATE,
b.COVGTHRU,
b.PRODUCT,
b.[PLAN],
b.ADJTYPE,
b.[IsCAP],
B.FEEBKR
HAVING SUM(ISNULL(t.TPREMIUM,0) + ISNULL(f.TFEES,0)) <> 0

UPDATE a
SET a.BENPLAN = x.BENEFIT
FROM #Adj a
JOIN (SELECT d.PUNBR,
d.GRNBR,
d.ESSN,
d.PRODUCT,
d.COVGDATE,
d.COVGTHRU,
e.BENEFIT
FROM (SELECT DISTINCT
PUNBR,
GRNBR,
ESSN,
PRODUCT,
COVGDATE,
COVGTHRU
FROM #Adj) d
JOIN (SELECT PUNBR,
GRNBR,
ESSN,
EFFDATE,
TRMDATE,
TRY_CONVERT(VARCHAR(2),RIGHT(u.ColName,2)) as 'PRODUCT',
ColData as 'BENEFIT'
FROM [HlthPC].dbo.ELG l
UNPIVOT (ColData FOR ColName IN (BENEFITMM,BENEFITDE,BENEFITRX,BENEFITVI,BENEFITWC/,BENEFITWI,BENEFITLT/)) u
WHERE ColData <> '') e
ON d.PUNBR = e.PUNBR
AND d.GRNBR = e.GRNBR
AND d.ESSN = e.ESSN
AND d.PRODUCT = e.PRODUCT
AND NOT ((d.COVGDATE > e.EFFDATE AND d.COVGDATE >= e.TRMDATE)
OR (d.COVGTHRU <= e.EFFDATE AND d.COVGTHRU < e.TRMDATE))) x
ON a.PUNBR = x.PUNBR
AND a.GRNBR = x.GRNBR
AND a.ESSN = x.ESSN
AND a.PRODUCT = x.PRODUCT
AND a.COVGDATE = x.COVGDATE
AND a.COVGTHRU = x.COVGTHRU

INSERT INTO #ELG
SELECT g.PUNBR,
g.GRNBR,
e.ESSN,
e.[CERT],
CONCAT(LTRIM(RTRIM(e.LNAME)),', ' + LTRIM(RTRIM(e.FTNAME)),' ' + NULLIF(RTRIM(e.MINIT),'') + '.') AS 'EmpName',
l.[STATUS],
l.DEPMM,
l.DEPDE,
l.DEPRX,
l.DEPVI,
l.DEPWC,
l.BENEFITMM,
l.BENEFITDE,
l.BENEFITRX,
l.BENEFITVI,
l.BENEFITWC,
--l.BENEFITWI,
--l.BENEFITLT,
l.INSPRODI1,
l.INSPRODI2,
l.INSPRODI3,
l.INSPRODI4,
l.INSPRODI5,
l.INSPRODI6,
l.INSPRODI7,
l.INSPRODI8,
l.PLANMM,
l.PLANDE,
l.PLANRX,
l.PLANVI,
l.PLANWC,
--l.PLANWI,
--l.PLANLT,
l.PLANI1,
l.PLANI2,
l.PLANI3,
l.PLANI4,
l.PLANI5,
l.PLANI6,
l.PLANI7,
l.PLANI8,
ROW_NUMBER() OVER (PARTITION BY g.PUNBR, g.GRNBR, l.ESSN ORDER BY l.[STATUS], l.TRMDATE DESC, l.EFFDATE DESC) AS 'eRank'
FROM #GRP g
JOIN [HlthPC].dbo.ELG l
ON g.PUNBR = l.PUNBR
AND g.GRNBR = l.GRNBR
AND l.[STATUS] = 'A'
AND @AsOfDate >= l.EFFDATE
AND @AsOfDate < ISNULL(NULLIF(l.TRMDATE,''),'99999999')
AND l.ESSN NOT IN ('100000001','000000001','111111111')
JOIN [HlthPC].dbo.EMP e
ON l.PUNBR = e.PUNBR
AND l.GRNBR = e.GRNBR
AND l.ESSN = e.ESSN
WHERE g.IsPast = 0

DELETE FROM #ELG WHERE eRank > 1

INSERT INTO #UNPVT_ELG
SELECT l.PUNBR,
l.GRNBR,
l.ESSN,
l.[CERT],
l.EmpName,
l.[STATUS],
p.PRODUCT,
ISNULL(b.BENEFIT,'') AS 'BENEFIT',
IIF(d.DEP IS NULL,CASE WHEN ibp.BILL_PLAN LIKE '%EE%' THEN 'N'
WHEN ibp.BILL_PLAN LIKE '%EF%' THEN 'F'
WHEN ibp.BILL_PLAN LIKE '%ES%' THEN 'S'
WHEN ibp.BILL_PLAN LIKE '%EC%' THEN 'C' END,d.DEP) AS 'DEP',
ISNULL(a.BILL_PLAN,ibp.BILL_PLAN) AS 'BILL_PLAN',
p.IsIProduct,
p.SortBy AS 'ProductSortBy'

FROM #ELG l
CROSS JOIN #PRODUCTS p
LEFT JOIN (SELECT PUNBR,
GRNBR,
ESSN,
TRY_CONVERT(VARCHAR(2),RIGHT(u.ColName,2)) as 'PRODUCT',
ColData as 'BENEFIT'
FROM #ELG l
UNPIVOT (ColData FOR ColName IN (BENEFITMM,BENEFITDE,BENEFITRX,BENEFITVI,BENEFITWC/,BENEFITWI,BENEFITLT/)) u
WHERE ColData <> '') b
ON l.PUNBR = b.PUNBR
AND l.GRNBR = b.GRNBR
AND l.ESSN = b.ESSN
AND p.PRODUCT = b.PRODUCT
LEFT JOIN (SELECT PUNBR,
GRNBR,
ESSN,
TRY_CONVERT(VARCHAR(2),RIGHT(u.ColName,2)) as 'PRODUCT',
ColData as 'BILL_PLAN'
FROM #ELG l
UNPIVOT (ColData FOR ColName IN (PLANMM,PLANDE,PLANRX,PLANVI,PLANWC/,PLANWI,PLANLT/)) u
WHERE ColData <> '') a
ON l.PUNBR = a.PUNBR
AND l.GRNBR = a.GRNBR
AND l.ESSN = a.ESSN
AND p.PRODUCT = a.PRODUCT
LEFT JOIN (SELECT PUNBR,
GRNBR,
ESSN,
TRY_CONVERT(VARCHAR(1),RIGHT(u.ColName,1)) as 'IPRODNUM',
ColData as 'PRODUCT'
FROM #ELG l
UNPIVOT (ColData FOR ColName IN (INSPRODI1,INSPRODI2,INSPRODI3,INSPRODI4,INSPRODI5,INSPRODI6,INSPRODI7,INSPRODI8)) u
WHERE ColData <> '') ipp
ON l.PUNBR = ipp.PUNBR
AND l.GRNBR = ipp.GRNBR
AND l.ESSN = ipp.ESSN
AND p.PRODUCT = ipp.PRODUCT
LEFT JOIN (SELECT PUNBR,
GRNBR,
ESSN,
TRY_CONVERT(VARCHAR(1),RIGHT(u.ColName,1)) as 'IPRODNUM',
ColData as 'BILL_PLAN'
FROM #ELG l
UNPIVOT (ColData FOR ColName IN (PLANI1,PLANI2,PLANI3,PLANI4,PLANI5,PLANI6,PLANI7,PLANI8)) u
WHERE ColData <> '') ibp
ON l.PUNBR = ibp.PUNBR
AND l.GRNBR = ibp.GRNBR
AND l.ESSN = ibp.ESSN
AND ipp.IPRODNUM = ibp.IPRODNUM
LEFT JOIN (SELECT PUNBR,
GRNBR,
ESSN,
TRY_CONVERT(VARCHAR(2),RIGHT(u.ColName,2)) as 'PRODUCT',
ColData as 'DEP'
FROM #ELG l
UNPIVOT (ColData FOR ColName IN (DEPMM,DEPDE,DEPRX,DEPVI,DEPWC)) u
WHERE ColData <> '') d
ON l.PUNBR = d.PUNBR
AND l.GRNBR = d.GRNBR
AND l.ESSN = d.ESSN
AND p.PRODUCT = d.PRODUCT
WHERE (b.BENEFIT IS NOT NULL
OR a.BILL_PLAN IS NOT NULL
OR d.DEP IS NOT NULL
OR ibp.BILL_PLAN IS NOT NULL)

INSERT INTO #RATES
SELECT DISTINCT
r.PRODUCT,
r.[PLAN],
EFFDATE,
FBKR_1,
FBKR_2,
FBKR_3,
FBKR_4,
FBKR_5,
FBKR_6,
FBKR_7,
FBKR_8,
FBKR_9,
FBKR_10,-- add upto FBKR_20-- later

DFEMP_1,
DFEMP_2,
DFEMP_3,
DFEMP_4,
DFEMP_5,
DFEMP_6,
DFEMP_7,
DFEMP_8,
DFEMP_9,
DFEMP_10,
DFFAM_1,
DFFAM_2,
DFFAM_3,
DFFAM_4,
DFFAM_5,
DFFAM_6,
DFFAM_7,
DFFAM_8,
DFFAM_9,
DFFAM_10,
DFSPO_1,
DFSPO_2,
DFSPO_3,
DFSPO_4,
DFSPO_5,
DFSPO_6,
DFSPO_7,
DFSPO_8,
DFSPO_9,
DFSPO_10,
DFCHL_1,
DFCHL_2,
DFCHL_3,
DFCHL_4,
DFCHL_5,
DFCHL_6,
DFCHL_7,
DFCHL_8,
DFCHL_9,
DFCHL_10,
DFPREM_1,
DFPREM_2,
DFPREM_3,
DFPREM_4,
DFPREM_5,
DFPREM_6,
DFPREM_7,
DFPREM_8,
DFPREM_9,
DFPREM_10,
DEMP_1,
DEMP_2,
DEMP_3,
DEMP_4,
DEMP_5,
DEMP_6,
DEMP_7,
DEMP_8,
DEMP_9,
DEMP_10,
DFAM_1,
DFAM_2,
DFAM_3,
DFAM_4,
DFAM_5,
DFAM_6,
DFAM_7,
DFAM_8,
DFAM_9,
DFAM_10,
DSPO_1,
DSPO_2,
DSPO_3,
DSPO_4,
DSPO_5,
DSPO_6,
DSPO_7,
DSPO_8,
DSPO_9,
DSPO_10,
DCHL_1,
DCHL_2,
DCHL_3,
DCHL_4,
DCHL_5,
DCHL_6,
DCHL_7,
DCHL_8,
DCHL_9,
DCHL_10,
DPREM_1,
DPREM_2,
DPREM_3,
DPREM_4,
DPREM_5,
DPREM_6,
DPREM_7,
DPREM_8,
DPREM_9,
DPREM_10
FROM [HlthPC].dbo.RATES r
JOIN (SELECT PRODUCT,
[PLAN],
MAX(EFFDATE) AS 'MaxEffDate'
FROM [HlthPC].dbo.RATES
WHERE ESTATUS = 'A'
AND EFFDATE <= @Date
GROUP BY PRODUCT,
[PLAN]) m
ON r.PRODUCT = m.PRODUCT
AND r.[PLAN] = m.[PLAN]
AND r.EFFDATE = m.MaxEffDate
JOIN (SELECT DISTINCT
PRODUCT,
BILL_PLAN
FROM #UNPVT_ELG) u
ON r.PRODUCT = u.PRODUCT
AND r.[PLAN] = u.BILL_PLAN

INSERT INTO #UNPVT_RATES
SELECT r.PRODUCT,
r.[PLAN],
r.EFFDATE,
i.Iteration,
b.FBKR,-- future invoices for broker code
n.DFEMP,
f.DFFAM,
s.DFSPO,
c.DFCHL,
p.DFPREM,
nr.DEMP,
fr.DFAM,
sr.DSPO,
cr.DCHL,
pr.DPREM
FROM #RATES r
CROSS JOIN (SELECT CONVERT(INT,[value]) AS 'Iteration' FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10',',')) i
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.FBKR_CLMN,2),'_','')) as 'Iteration',
u.FBKR
FROM #RATES
UNPIVOT (FBKR FOR FBKR_CLMN IN
(FBKR_1,FBKR_2,FBKR_3,FBKR_4,FBKR_5,FBKR_6,FBKR_7,FBKR_8,FBKR_9,FBKR_10)) u
) b
ON r.PRODUCT = b.PRODUCT
AND r.[PLAN] = b.[PLAN]
AND r.EFFDATE = b.EFFDATE
AND i.Iteration = b.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DFEMP_CLMN,2),'_','')) as 'Iteration',
u.DFEMP
FROM #RATES
UNPIVOT (DFEMP FOR DFEMP_CLMN IN (DFEMP_1,DFEMP_2,DFEMP_3,DFEMP_4,DFEMP_5,DFEMP_6,DFEMP_7,DFEMP_8,DFEMP_9,DFEMP_10)) u

) n
ON r.PRODUCT = n.PRODUCT
AND r.[PLAN] = n.[PLAN]
AND r.EFFDATE = n.EFFDATE
AND i.Iteration = n.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DFFAM_CLMN,2),'_','')) as 'Iteration',
u.DFFAM
FROM #RATES
UNPIVOT (DFFAM FOR DFFAM_CLMN IN (DFFAM_1,DFFAM_2,DFFAM_3,DFFAM_4,DFFAM_5,DFFAM_6,DFFAM_7,DFFAM_8,DFFAM_9,DFFAM_10)) u

) f
ON r.PRODUCT = f.PRODUCT
AND r.[PLAN] = f.[PLAN]
AND r.EFFDATE = f.EFFDATE
AND i.Iteration = f.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DFSPO_CLMN,2),'_','')) as 'Iteration',
u.DFSPO
FROM #RATES
UNPIVOT (DFSPO FOR DFSPO_CLMN IN (DFSPO_1,DFSPO_2,DFSPO_3,DFSPO_4,DFSPO_5,DFSPO_6,DFSPO_7,DFSPO_8,DFSPO_9,DFSPO_10)) u

) s
ON r.PRODUCT = s.PRODUCT
AND r.[PLAN] = s.[PLAN]
AND r.EFFDATE = s.EFFDATE
AND i.Iteration = s.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DFCHL_CLMN,2),'_','')) as 'Iteration',
u.DFCHL
FROM #RATES
UNPIVOT (DFCHL FOR DFCHL_CLMN IN (DFCHL_1,DFCHL_2,DFCHL_3,DFCHL_4,DFCHL_5,DFCHL_6,DFCHL_7,DFCHL_8,DFCHL_9,DFCHL_10)) u

) c
ON r.PRODUCT = c.PRODUCT
AND r.[PLAN] = c.[PLAN]
AND r.EFFDATE = c.EFFDATE
AND i.Iteration = c.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DFPREM_CLMN,2),'_','')) as 'Iteration',
u.DFPREM
FROM #RATES
UNPIVOT (DFPREM FOR DFPREM_CLMN IN (DFPREM_1,DFPREM_2,DFPREM_3,DFPREM_4,DFPREM_5,DFPREM_6,DFPREM_7,DFPREM_8,DFPREM_9,DFPREM_10)) u

) p
ON r.PRODUCT = p.PRODUCT
AND r.[PLAN] = p.[PLAN]
AND r.EFFDATE = p.EFFDATE
AND i.Iteration = p.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DEMP_CLMN,2),'_','')) as 'Iteration',
u.DEMP
FROM #RATES
UNPIVOT (DEMP FOR DEMP_CLMN IN (DEMP_1,DEMP_2,DEMP_3,DEMP_4,DEMP_5,DEMP_6,DEMP_7,DEMP_8,DEMP_9,DEMP_10)) u

) nr
ON r.PRODUCT = nr.PRODUCT
AND r.[PLAN] = nr.[PLAN]
AND r.EFFDATE = nr.EFFDATE
AND i.Iteration = nr.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DFAM_CLMN,2),'_','')) as 'Iteration',
u.DFAM
FROM #RATES
UNPIVOT (DFAM FOR DFAM_CLMN IN (DFAM_1,DFAM_2,DFAM_3,DFAM_4,DFAM_5,DFAM_6,DFAM_7,DFAM_8,DFAM_9,DFAM_10)) u

) fr
ON r.PRODUCT = fr.PRODUCT
AND r.[PLAN] = fr.[PLAN]
AND r.EFFDATE = fr.EFFDATE
AND i.Iteration = fr.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DSPO_CLMN,2),'_','')) as 'Iteration',
u.DSPO
FROM #RATES
UNPIVOT (DSPO FOR DSPO_CLMN IN (DSPO_1,DSPO_2,DSPO_3,DSPO_4,DSPO_5,DSPO_6,DSPO_7,DSPO_8,DSPO_9,DSPO_10)) u

) sr
ON r.PRODUCT = sr.PRODUCT
AND r.[PLAN] = sr.[PLAN]
AND r.EFFDATE = sr.EFFDATE
AND i.Iteration = sr.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DCHL_CLMN,2),'_','')) as 'Iteration',
u.DCHL
FROM #RATES
UNPIVOT (DCHL FOR DCHL_CLMN IN (DCHL_1,DCHL_2,DCHL_3,DCHL_4,DCHL_5,DCHL_6,DCHL_7,DCHL_8,DCHL_9,DCHL_10)) u

) cr
ON r.PRODUCT = cr.PRODUCT
AND r.[PLAN] = cr.[PLAN]
AND r.EFFDATE = cr.EFFDATE
AND i.Iteration = cr.Iteration
LEFT JOIN (

SELECT PRODUCT,
[PLAN],
EFFDATE,
TRY_CONVERT(INT,REPLACE(RIGHT(u.DPREM_CLMN,2),'_','')) as 'Iteration',
u.DPREM
FROM #RATES
UNPIVOT (DPREM FOR DPREM_CLMN IN (DPREM_1,DPREM_2,DPREM_3,DPREM_4,DPREM_5,DPREM_6,DPREM_7,DPREM_8,DPREM_9,DPREM_10)) u

) pr
ON r.PRODUCT = pr.PRODUCT
AND r.[PLAN] = pr.[PLAN]
AND r.EFFDATE = pr.EFFDATE
AND i.Iteration = pr.Iteration

INSERT INTO #Fees
SELECT x.PUNBR,
x.GRNBR,
DESC_1,
DESC_2,
DESC_3,
DESC_4,
DESC_5,
DESC_6,
DESC_7,
DESC_8,
DESC_9,
DESC_10,
DESC_11,
DESC_12,
DESC_13,
DESC_14,
TYPE_1,
TYPE_2,
TYPE_3,
TYPE_4,
TYPE_5,
TYPE_6,
TYPE_7,
TYPE_8,
TYPE_9,
TYPE_10,
TYPE_11,
TYPE_12,
TYPE_13,
TYPE_14,
DAMOUNT_1,
DAMOUNT_2,
DAMOUNT_3,
DAMOUNT_4,
DAMOUNT_5,
DAMOUNT_6,
DAMOUNT_7,
DAMOUNT_8,
DAMOUNT_9,
DAMOUNT_10,
DAMOUNT_11,
DAMOUNT_12,
DAMOUNT_13,
DAMOUNT_14,
BKR_1,
BKR_2,
BKR_3,
BKR_4,
BKR_5,
BKR_6,
BKR_7,
BKR_8,
BKR_9,
BKR_10,
BKR_11,
BKR_12,
BKR_13,
BKR_14
FROM [HlthPC].dbo.Fees x
JOIN #GRP g
ON x.PUNBR = g.PUNBR
AND x.GRNBR = g.GRNBR
AND g.IsPast = 0

INSERT INTO #HPMiscFees
SELECT f.PUNBR AS 'Underwriter',
f.GRNBR AS 'Group',
ISNULL(d.ColData,'') AS 'Misc Fee Description',
ISNULL(t.ColData,'O') AS 'Misc Fee Type',
ISNULL(REPLACE(CONVERT(VARCHAR(12),CONVERT(MONEY,a.ColData)),'.00',''),'') AS 'Misc Fee Amount',
ISNULL(b.ColData,'') AS 'Misc Fee Broker Code',
n.ColNum AS 'SortBy'
FROM (SELECT DISTINCT
PUNBR,
GRNBR
FROM #Fees) f
CROSS JOIN (SELECT TRY_CONVERT(INT,[value]) AS 'ColNum'
FROM STRING_SPLIT('1,2,3,4,5,6,7,8,9,10,11,12,13,14',',')) n
LEFT JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
TRY_CONVERT(INT,REPLACE(ColName,'DESC_','')) AS 'ColNum',
ColData
FROM #Fees f
UNPIVOT (ColData FOR ColName IN (DESC_1,DESC_2,DESC_3,DESC_4,DESC_5,DESC_6,DESC_7,
DESC_8,DESC_9,DESC_10,DESC_11,DESC_12,DESC_13,DESC_14)) u) d
ON f.PUNBR = d.PUNBR
AND f.GRNBR = d.GRNBR
AND n.ColNum = d.ColNum
LEFT JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
TRY_CONVERT(INT,REPLACE(ColName,'TYPE_','')) AS 'ColNum',
ColData
FROM #Fees f
UNPIVOT (ColData FOR ColName IN (TYPE_1,TYPE_2,TYPE_3,TYPE_4,TYPE_5,TYPE_6,TYPE_7,
TYPE_8,TYPE_9,TYPE_10,TYPE_11,TYPE_12,TYPE_13,TYPE_14)) u) t
ON f.PUNBR = t.PUNBR
AND f.GRNBR = t.GRNBR
AND n.ColNum = t.ColNum
LEFT JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
TRY_CONVERT(INT,REPLACE(ColName,'DAMOUNT_','')) AS 'ColNum',
ColData
FROM #Fees f
UNPIVOT (ColData FOR ColName IN (DAMOUNT_1,DAMOUNT_2,DAMOUNT_3,DAMOUNT_4,DAMOUNT_5,DAMOUNT_6,DAMOUNT_7,
DAMOUNT_8,DAMOUNT_9,DAMOUNT_10,DAMOUNT_11,DAMOUNT_12,DAMOUNT_13,DAMOUNT_14)) u) a
ON f.PUNBR = a.PUNBR
AND f.GRNBR = a.GRNBR
AND n.ColNum = a.ColNum
LEFT JOIN (SELECT DISTINCT
PUNBR,
GRNBR,
TRY_CONVERT(INT,REPLACE(ColName,'BKR_','')) as 'ColNum',
ColData
FROM #Fees f
UNPIVOT (ColData FOR ColName IN (BKR_1,BKR_2,BKR_3,BKR_4,BKR_5,BKR_6,BKR_7,
BKR_8,BKR_9,BKR_10,BKR_11,BKR_12,BKR_13,BKR_14)) u) b
ON f.PUNBR = b.PUNBR
AND f.GRNBR = b.GRNBR
AND n.ColNum = b.ColNum
WHERE ISNULL(LTRIM(RTRIM(b.ColData)),'') <> ''
AND ISNULL(t.ColData,'O') <> 'P'

INSERT INTO #Data ---FBKR need to be added here for futre invoices
SELECT e.PUNBR,
e.GRNBR,
e.ESSN,
e.[CERT],
e.EmpName,
e.[STATUS],
e.PRODUCT,
e.BENEFIT,
CASE e.DEP
WHEN 'N' THEN 'EE only'
WHEN 'F' THEN 'Family'
WHEN 'S' THEN 'EE + Sp'
WHEN 'C' THEN 'EE + Ch' END AS 'DEP',
e.BILL_PLAN,
CASE WHEN e.IsIProduct = 0 AND e.DEP = 'N' THEN r.DFEMP
WHEN e.IsIProduct = 0 AND e.DEP = 'F' THEN r.DFFAM
WHEN e.IsIProduct = 0 AND e.DEP = 'S' THEN r.DFSPO
WHEN e.IsIProduct = 0 AND e.DEP = 'C' THEN r.DFCHL
WHEN e.IsIProduct = 1 THEN r.DFPREM
ELSE CONVERT(MONEY,0) END AS 'PREM',
IsCAP,
CASE WHEN e.IsIProduct = 0 AND e.DEP = 'N' THEN r.DFEMP_CAP
WHEN e.IsIProduct = 0 AND e.DEP = 'F' THEN r.DFFAM_CAP
WHEN e.IsIProduct = 0 AND e.DEP = 'S' THEN r.DFSPO_CAP
WHEN e.IsIProduct = 0 AND e.DEP = 'C' THEN r.DFCHL_CAP
WHEN e.IsIProduct = 1 THEN r.DFPREM_CAP
ELSE CONVERT(MONEY,0) END AS 'PREM_CAP',
-1 AS 'SortBy',
e.IsIProduct,
e.ProductSortBy,
-1,
r.FBKR
FROM #UNPVT_ELG e
LEFT JOIN (SELECT PRODUCT,
[PLAN],[FBKR],
MAX(IIF(FBKR = 'CAP', 1, 0)) AS 'IsCAP',
ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DEMP),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFEMP),0)),0) AS 'DFEMP_CAP',
ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFAM),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFFAM),0)),0) AS 'DFFAM_CAP',
ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DEMP),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DSPO),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFEMP),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFSPO),0)),0) AS 'DFSPO_CAP',
ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DEMP),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DCHL),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFEMP),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFCHL),0)),0) AS 'DFCHL_CAP',
ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DPREM),0)),0) + ISNULL(SUM(IIF(FBKR = 'CAP',TRY_CONVERT(MONEY,DFPREM),0)),0) AS 'DFPREM_CAP',

ISNULL(SUM(TRY_CONVERT(MONEY,DEMP)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFEMP)),0) AS 'DFEMP',
ISNULL(SUM(TRY_CONVERT(MONEY,DFAM)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFFAM)),0) AS 'DFFAM',
ISNULL(SUM(TRY_CONVERT(MONEY,DEMP)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DSPO)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFEMP)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFSPO)),0) AS 'DFSPO',
ISNULL(SUM(TRY_CONVERT(MONEY,DEMP)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DCHL)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFEMP)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFCHL)),0) AS 'DFCHL',
ISNULL(SUM(TRY_CONVERT(MONEY,DPREM)),0) + ISNULL(SUM(TRY_CONVERT(MONEY,DFPREM)),0) AS 'DFPREM'
FROM #UNPVT_RATES
GROUP BY PRODUCT,[FBKR],
[PLAN]) r
ON e.PRODUCT = r.PRODUCT
AND e.BILL_PLAN = r.[PLAN]
UNION ALL
SELECT PUNBR,
GRNBR,
ESSN,
[CERT],
EmpName,
[STATUS],
PRODUCT,
ISNULL(NULLIF(BENPLAN,''),[PLAN]),
COVGTier,
[PLAN],
ADJUSTMENT,
[IsCAP],
[ADJUSTMENT_CAP],
0,
0,
0,
IIF(ISNULL(ADJTYPE,'') = 'N',0,1),
FEEBKR
FROM #Adj
UNION ALL
SELECT [Underwriter],---add [Misc Fee Broker Code] for this section this is for futre invoces
[Group],
IIF([Misc Fee Description] LIKE '% GHOST %','',[Misc Fee Broker Code]),
IIF([Misc Fee Description] LIKE '% GHOST %','',[Misc Fee Broker Code]),
[Misc Fee Description],
[Misc Fee Type],
'MF',
'',
'',
'',
ISNULL(SUM(TRY_CONVERT(MONEY,[Misc Fee Amount])),0),
IIF(IIF([Misc Fee Description] LIKE '% GHOST %','',[Misc Fee Broker Code])= 'CAP',1,0),
IIF(IIF([Misc Fee Description] LIKE '% GHOST %','',[Misc Fee Broker Code])= 'CAP',ISNULL(SUM(TRY_CONVERT(MONEY,[Misc Fee Amount])),0),0),
ISNULL(MinSortBy,SortBy),
0,
0,
---1,
[Misc Fee Broker Code]
FROM #HPMiscFees h
LEFT JOIN (SELECT [Underwriter] AS 'PUNBR',
[Group] AS 'GRNBR',
MIN(SortBy) AS 'MinSortBy'
FROM #HPMiscFees
WHERE [Misc Fee Description] LIKE '% GHOST %'
GROUP BY [Underwriter],
[Group]) m
ON h.[Underwriter] = m.PUNBR
AND h.[Group] = m.GRNBR
GROUP BY [Underwriter],
[Group],
IIF([Misc Fee Description] LIKE '% GHOST %','',[Misc Fee Broker Code]),
IIF([Misc Fee Description] LIKE '% GHOST %','',[Misc Fee Broker Code]),
[Misc Fee Description],
[Misc Fee Type],
[Misc Fee Broker Code],
ISNULL(MinSortBy,SortBy)

END

--INSERT INTO #EMP
--SELECT DISTINCT
-- PUNBR,
-- GRNBR,
-- ESSN,
-- [CERT],
-- EmpName,
-- [STATUS]
-- FROM #Data
-- WHERE PRODUCT <> 'MF'
-- AND SortBy < 1

DELETE g
FROM #GRP g
LEFT JOIN (SELECT DISTINCT PUNBR, GRNBR FROM #Data) i
ON g.PUNBR = i.PUNBR
AND g.GRNBR = i.GRNBR
--LEFT JOIN (SELECT DISTINCT PUNBR, GRNBR FROM #Adj) a
-- ON g.PUNBR = a.PUNBR
-- AND g.GRNBR = a.GRNBR
--LEFT JOIN (SELECT DISTINCT [Underwriter] AS 'PUNBR', [Group] AS 'GRNBR' FROM #HPMiscFees) h
-- ON g.PUNBR = h.PUNBR
-- AND g.GRNBR = h.GRNBR
WHERE i.PUNBR IS NULL
--AND a.PUNBR IS NULL
--AND h.PUNBR IS NULL

DELETE p
FROM #PRODUCTS p
LEFT JOIN (SELECT DISTINCT PRODUCT FROM #Data) i
ON p.PRODUCT = i.PRODUCT
--LEFT JOIN (SELECT DISTINCT PRODUCT FROM #Adj) a
-- ON p.PRODUCT = a.PRODUCT
--LEFT JOIN (SELECT TOP 1 'MF' AS 'PRODUCT' FROM #HPMiscFees) h
-- ON p.PRODUCT = h.PRODUCT
WHERE i.PRODUCT IS NULL
--AND a.PRODUCT IS NULL
--AND h.PRODUCT IS NULL

INSERT INTO #Final
SELECT 3 AS 'Level',
g.PUNBR,
g.GRNBR,
g.[NAME] AS 'GroupName',
g.IsPast,
p.PRODUCT,
p.IsIProduct,
p.[NAME] AS 'ProductName',
p.[DESC] AS 'ProductDescription',
p.SortBy AS 'ProductSortBy',
ISNULL(d.SortBy,-1) AS 'DataSortBy',
ISNULL(d.AdjSortBy,-1) AS 'AdjSortBy',
ISNULL(d.ESSN,'') AS 'ESSN',
ISNULL(d.[CERT],'') AS 'CERT',
ISNULL(d.EmpName,'') AS 'EmpName',
ISNULL(d.[STATUS],'') AS 'STATUS',
ISNULL(d.BENEFIT,'') AS 'BENEFIT',
ISNULL(d.DEP,'') AS 'DEP',
ISNULL(d.BILL_PLAN,'') AS 'BILL_PLAN',
CONVERT(MONEY,ISNULL(d.PREM,0)) AS 'PREM',
ISNULL(IsCAP,0) AS 'IsCAP',
CONVERT(MONEY,ISNULL(d.PREM_CAP,0)) AS 'PREM_CAP',
d.feebkr

FROM #GRP g
CROSS JOIN #PRODUCTS p
LEFT JOIN #Data d
ON g.PUNBR = d.PUNBR
AND g.GRNBR = d.GRNBR
AND p.PRODUCT = d.PRODUCT
WHERE p.PRODUCT <> 'MF'
UNION ALL
SELECT 2,
g.PUNBR,
g.GRNBR,
g.[NAME],
g.IsPast,
p.PRODUCT,
p.IsIProduct,
p.[NAME],
p.[DESC],
p.SortBy,
ISNULL(d.SortBy,-1),
ISNULL(d.AdjSortBy,-1),
ISNULL(d.ESSN,''),
ISNULL(d.[CERT],''),
ISNULL(d.EmpName,''),
ISNULL(d.[STATUS],''),
ISNULL(d.BENEFIT,''),
ISNULL(d.DEP,''),
ISNULL(d.BILL_PLAN,''),
ISNULL(d.PREM,0),
ISNULL(IsCAP,0),
ISNULL(d.PREM_CAP,0),
d.FEEBKR
FROM #GRP g
CROSS JOIN #PRODUCTS p
LEFT JOIN #Data d
ON g.PUNBR = d.PUNBR
AND g.GRNBR = d.GRNBR
AND p.PRODUCT = d.PRODUCT
WHERE p.PRODUCT = 'MF'/*
UNION ALL
SELECT 1,
g.PUNBR,
g.GRNBR,
g.[NAME],
g.IsPast,
p.PRODUCT,
p.IsIProduct,
p.[NAME],
p.[DESC],
p.SortBy,
-1,
-1,
'',
'',
'',
'',
'',
'',
'',
ISNULL(SUM(d.PREM),0)
FROM #GRP g
CROSS JOIN #PRODUCTS p
LEFT JOIN #Data d
ON g.PUNBR = d.PUNBR
AND g.GRNBR = d.GRNBR
AND p.PRODUCT = d.PRODUCT
GROUP BY g.PUNBR,
g.GRNBR,
g.[NAME],
p.PRODUCT,
p.IsIProduct,
p.[NAME],
p.[DESC],
p.SortBy
UNION ALL
SELECT 0,
g.PUNBR,
g.GRNBR,
g.[NAME] AS 'GroupName',
g.IsPast,
'',
0,
'TOTAL',
'',
0,
-1,
-1,
'',
'',
'',
'',
'',
'',
'',
ISNULL(SUM(d.PREM),0) AS 'PREM'
FROM #GRP g
CROSS JOIN #PRODUCTS p
LEFT JOIN #Data d
ON g.PUNBR = d.PUNBR
AND g.GRNBR = d.GRNBR
AND p.PRODUCT = d.PRODUCT
GROUP BY g.PUNBR,
g.GRNBR,
g.[NAME]/

SELECT ---perct =(Sum([Prem])/100) PREM,
[Level],
[PUNBR],
[GRNBR],
[GroupName],
[IsPast],
[PRODUCT],
[IsIProduct],
[ProductName],
[ProductDescription],
[ProductSortBy],
[DataSortBy],
[AdjSortBy],
[ESSN],
[CERT],
[EmpName],
[STATUS],
[BENEFIT],
[DEP],
[BILL_PLAN],
[PREM],
[IsCAP],
[PREM_CAP],
[feebkr]
FROM #Final
---GROUP BY sem
ORDER BY [PUNBR],
[GRNBR],
[Level] DESC,
[DataSortBy],
[EmpName],
[IsIProduct],
[ProductSortBy],
[PRODUCT],
[AdjSortBy]

DROP TABLE IF EXISTS
#PRODUCTS,
#GRP,
#GrpBillExt,
#BillAdjst,
#TPREM_BillAdjst,
#EEBILLF,
#Adj,
#ELG,
#UNPVT_ELG,
#RATES,
#UNPVT_RATES,
#Fees,
#HPMiscFees,
#Data,
#EMP,
#Final

-----------------------------------------

;WITH RATE_PLANS AS
(
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_1 AS [BROKER],
DFEMP_1 AS EEFEE, DFFAM_1 AS FAMFEE,
DFSPO_1 AS SPOFEE, DFCHL_1 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_1 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_2 AS [BROKER],
DFEMP_2 AS EEFEE, DFFAM_2 AS FAMFEE,
DFSPO_2 AS SPOFEE, DFCHL_2 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_2 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_3 AS [BROKER],
DFEMP_3 AS EEFEE, DFFAM_3 AS FAMFEE,
DFSPO_3 AS SPOFEE, DFCHL_3 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_3 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_4 AS [BROKER],
DFEMP_4 AS EEFEE, DFFAM_4 AS FAMFEE,
DFSPO_4 AS SPOFEE, DFCHL_4 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_4 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_5 AS [BROKER],
DFEMP_5 AS EEFEE, DFFAM_5 AS FAMFEE,
DFSPO_5 AS SPOFEE, DFCHL_5 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_5 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_6 AS [BROKER],
DFEMP_6 AS EEFEE, DFFAM_6 AS FAMFEE,
DFSPO_6 AS SPOFEE, DFCHL_6 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_6 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_7 AS [BROKER],
DFEMP_7 AS EEFEE, DFFAM_7 AS FAMFEE,
DFSPO_7 AS SPOFEE, DFCHL_7 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_7 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_7 AS [BROKER],
DFEMP_8 AS EEFEE, DFFAM_8 AS FAMFEE,
DFSPO_8 AS SPOFEE, DFCHL_8 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_8 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_7 AS [BROKER],
DFEMP_9 AS EEFEE, DFFAM_9 AS FAMFEE,
DFSPO_9 AS SPOFEE, DFCHL_9 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_9 <> ''
UNION ALL
SELECT RAT.PRODUCT, [PLAN], TIER, EFFDATE, FBKR_10 AS [BROKER],
DFEMP_10 AS EEFEE, DFFAM_10 AS FAMFEE,
DFSPO_10 AS SPOFEE, DFCHL_10 AS CHLDFEE
FROM dbo.RATES RAT
WHERE FBKR_10 <> ''
)
SELECT * FROM RATE_PLANS
WHERE [PLAN] LIKE 'HA1%'
-

----------------------------------------------

WITH EEFEE_PIVOT AS (
SELECT
PRODUCT,
[PLAN],
TIER,
EFFDATE,
SBM AS SBM_EEFEE,
HUB AS HUB_EEFEE,
VHB AS VHB_EEFEE,
SR2 AS SR2_EEFEE,
CAP AS CAP_EEFEE
FROM
(SELECT PRODUCT, [PLAN], TIER, EFFDATE, BROKER, EEFEE
FROM Table_Name2323) AS SourceTable
PIVOT
(MAX(EEFEE) FOR BROKER IN ([SBM], [HUB], [VHB], [SR2], [CAP])) AS PivotTable
),
FAMFEE_PIVOT AS (
SELECT
PRODUCT,
[PLAN],
TIER,
EFFDATE,
SBM AS SBM_FAMFEE,
HUB AS HUB_FAMFEE,
VHB AS VHB_FAMFEE,
SR2 AS SR2_FAMFEE,
CAP AS CAP_FAMFEE
FROM
(SELECT PRODUCT, [PLAN], TIER, EFFDATE, BROKER, FAMFEE
FROM Table_Name2323) AS SourceTable
PIVOT
(MAX(FAMFEE) FOR BROKER IN ([SBM], [HUB], [VHB], [SR2], [CAP])) AS PivotTable
),
SPOFEE_PIVOT AS (
SELECT
PRODUCT,
[PLAN],
TIER,
EFFDATE,
SBM AS SBM_SPOFEE,
HUB AS HUB_SPOFEE,
VHB AS VHB_SPOFEE,
SR2 AS SR2_SPOFEE,
CAP AS CAP_SPOFEE
FROM
(SELECT PRODUCT, [PLAN], TIER, EFFDATE, BROKER, SPOFEE
FROM Table_Name2323) AS SourceTable
PIVOT
(MAX(SPOFEE) FOR BROKER IN ([SBM], [HUB], [VHB], [SR2], [CAP])) AS PivotTable
),
CHLDFEE_PIVOT AS (
SELECT
PRODUCT,
[PLAN],
TIER,
EFFDATE,
SBM AS SBM_CHLDFEE,
HUB AS HUB_CHLDFEE,
VHB AS VHB_CHLDFEE,
SR2 AS SR2_CHLDFEE,
CAP AS CAP_CHLDFEE
FROM
(SELECT PRODUCT, [PLAN], TIER, EFFDATE, BROKER, CHLDFEE
FROM Table_Name2323) AS SourceTable
PIVOT
(MAX(CHLDFEE) FOR BROKER IN ([SBM], [HUB], [VHB], [SR2], [CAP])) AS PivotTable
)

SELECT
e.PRODUCT, e.[PLAN], e.TIER, e.EFFDATE,
e.SBM_EEFEE, e.HUB_EEFEE, e.VHB_EEFEE, e.SR2_EEFEE, e.CAP_EEFEE,
f.SBM_FAMFEE, f.HUB_FAMFEE, f.VHB_FAMFEE, f.SR2_FAMFEE, f.CAP_FAMFEE,
s.SBM_SPOFEE, s.HUB_SPOFEE, s.VHB_SPOFEE, s.SR2_SPOFEE, s.CAP_SPOFEE,
c.SBM_CHLDFEE, c.HUB_CHLDFEE, c.VHB_CHLDFEE, c.SR2_CHLDFEE, c.CAP_CHLDFEE
FROM
EEFEE_PIVOT e
JOIN
FAMFEE_PIVOT f ON e.PRODUCT = f.PRODUCT AND e.[PLAN] = f.[PLAN] AND e.TIER = f.TIER AND e.EFFDATE = f.EFFDATE
JOIN
SPOFEE_PIVOT s ON e.PRODUCT = s.PRODUCT AND e.[PLAN] = s.[PLAN] AND e.TIER = s.TIER AND e.EFFDATE = s.EFFDATE
JOIN
CHLDFEE_PIVOT c ON e.PRODUCT = c.PRODUCT AND e.[PLAN] = c.[PLAN] AND e.TIER = c.TIER AND e.EFFDATE = c.EFFDATE
ORDER BY
e.PRODUCT, e.[PLAN], e.TIER, e.EFFDATE;

Say Yes to New Adventures

"Lorem Ipsum has been the industry's standard dummy text ever since the 1500s." — James Chapman

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book. Lorem ipsum dolor amet, consectetur adipiscing elit.

Lorem Ipsum is simply dummy text of the printing and typesetting industry. Lorem Ipsum has been the industry's standard dummy text ever since the 1500s, when an unknown printer took a galley of type and scrambled it to make a type specimen book.

Vijay Kashyap
A California-based travel writer, lover of food, oceans, and nature.

Say Yes to New Adventures

You may also be interested in