Query uses CTEs, Temp tables, subqueries, UNION,

--Because the age bands may be different depending on benefit codes, and because we need to insert zero values where appropriate,
--this query is largely CTEs to organize everything up front, crossjoins to get the zero rows, and a final UNIONed query to put it together

-- Create tables to store the benefit codes and their groupings

CREATE TABLE #BENEFIT_CODES_1 (
    BENEFIT_CODE VARCHAR(2),
    GROUPINGS VARCHAR(11)
);

-- Skookum benefit codes: removed va, vb, vc, vd, LW, LY, LZ, lF--these are not age rated
INSERT INTO #BENEFIT_CODES_1 (BENEFIT_CODE, GROUPINGS)
VALUES
    ('1v', '1v 5v ve vg'), ('5v', '1v 5v ve vg'), ('ve', '1v 5v ve vg'), ('vg', '1v 5v ve vg'),
    ('3v', '3v 4v vf vh'), ('4v', '3v 4v vf vh'), ('vf', '3v 4v vf vh'), ('vh', '3v 4v vf vh'),
    ('2v', '2v 6v vp vs'), ('6v', '2v 6v vp vs'), ('vp', '2v 6v vp vs'), ('vs', '2v 6v vp vs');

-- Create a table for age bands for "v codes"
CREATE TABLE #AGE_BANDS_1 (
    AGE_GROUP VARCHAR(5),
    AGE_ORDER INT
);

INSERT INTO #AGE_BANDS_1 (AGE_GROUP, AGE_ORDER)
VALUES
    ('00-19', 1), ('20-24', 2), ('25-29', 3), ('30-34', 4),
    ('35-39', 5), ('40-44', 6), ('45-49', 7), ('50-54', 8),
    ('55-59', 9), ('60-64', 10), ('65-69', 11), ('70-74', 12),
    ('75-79', 13), ('80-84', 14), ('85-99', 15);

CREATE TABLE #BENEFIT_CODES_2 (
    BENEFIT_CODE VARCHAR(2),
    GROUPINGS VARCHAR(11)
);

INSERT INTO #BENEFIT_CODES_2 (BENEFIT_CODE, GROUPINGS)
VALUES
	('dh', 'dh'),
	('dg', 'dg'),
    ('dk', 'dk dm'), ('dm', 'dk dm');

-- Create a table for age bands for "d codes"
CREATE TABLE #AGE_BANDS_2 (
    AGE_GROUP VARCHAR(5),
    AGE_ORDER INT
);

INSERT INTO #AGE_BANDS_2 (AGE_GROUP, AGE_ORDER)
VALUES
    ('00-24', 1), ('25-29', 2), ('30-34', 3),
    ('35-39', 4), ('40-44', 5), ('45-49', 6), ('50-54', 7),
    ('55-59', 8), ('60-64', 9), ('65-69', 10), ('70-99', 11);

-- Declare variables
DECLARE @EFFECTIVE_YYYYMMDD DATE = '2024-07-01';
DECLARE @AGE_AS_OF_YYYYMMDD DATE = DATEFROMPARTS(YEAR(@EFFECTIVE_YYYYMMDD), 1, 1);

-- Main query to calculate volume and premium by age group and benefit group
WITH
CTE_1 AS (
    SELECT
        bc.GROUPINGS AS BENEFITS,
        CASE
            WHEN ibe.BENEFIT_CODE = 'vs'
				THEN
					ISNULL(DATEDIFF(YEAR, i.SPOUSE_BIRTH_YYYYMMDD, @AGE_AS_OF_YYYYMMDD)
						  ,DATEDIFF(YEAR, i.BIRTH_YYYYMMDD, @AGE_AS_OF_YYYYMMDD))
			ELSE DATEDIFF(YEAR, i.BIRTH_YYYYMMDD, @AGE_AS_OF_YYYYMMDD)
        END AS AGE,
        ibe.VOLUME,
        ibe.AMT as AMOUNT
    FROM INDFIL_FILE i
    JOIN IBEFIL_FILE ibe ON i.CASE_NUM = ibe.CASE_NUM AND i.CERT_NUM = ibe.CERT_NUM
    JOIN #BENEFIT_CODES_1 bc ON ibe.BENEFIT_CODE COLLATE SQL_Latin1_General_CP1_CS_AS = bc.BENEFIT_CODE COLLATE SQL_Latin1_General_CP1_CS_AS
    WHERE
        i.CASE_NUM = '46185'
        AND i.EFF_YYYYMMDD <= @EFFECTIVE_YYYYMMDD
        AND (i.TERM_YYYYMMDD > @EFFECTIVE_YYYYMMDD OR i.TERM_YYYYMMDD IS NULL)
        AND (COALESCE(ibe.TERM_YYYYMMDD,'') = '' OR ibe.TERM_YYYYMMDD > @EFFECTIVE_YYYYMMDD)
        AND i.CERT_NUM NOT in ('4773','1020','2325')
        AND i.DIV_NUM not in (SELECT [DIV_NUM]
             FROM [FBC].[dbo].[CASFIL_FILE]
             WHERE TERM_YYYYMMDD IS NOT NULL
             AND TERM_YYYYMMDD < @EFFECTIVE_YYYYMMDD
             AND CASE_NUM = '46185')
),
GROUPED_DATA_1 AS (
    SELECT
        BENEFITS,
        CASE
            WHEN AGE < 20 THEN '00-19'
            WHEN AGE < 25 THEN '20-24'
            WHEN AGE < 30 THEN '25-29'
            WHEN AGE < 35 THEN '30-34'
            WHEN AGE < 40 THEN '35-39'
            WHEN AGE < 45 THEN '40-44'
            WHEN AGE < 50 THEN '45-49'
            WHEN AGE < 55 THEN '50-54'
            WHEN AGE < 60 THEN '55-59'
            WHEN AGE < 65 THEN '60-64'
            WHEN AGE < 70 THEN '65-69'
            WHEN AGE < 75 THEN '70-74'
            WHEN AGE < 80 THEN '75-79'
            WHEN AGE < 85 THEN '80-84'
            ELSE '85-99'
        END AS AGE_GROUP,
        COUNT(*) AS LIVES,
        SUM(CAST(VOLUME AS DECIMAL(18,2))) AS VOLUME,
        SUM(CAST(AMOUNT AS DECIMAL(18,2))) AS PREMIUM
    FROM CTE_1
    GROUP BY
        BENEFITS,
        CASE
            WHEN AGE < 20 THEN '00-19'
            WHEN AGE < 25 THEN '20-24'
            WHEN AGE < 30 THEN '25-29'
            WHEN AGE < 35 THEN '30-34'
            WHEN AGE < 40 THEN '35-39'
            WHEN AGE < 45 THEN '40-44'
            WHEN AGE < 50 THEN '45-49'
            WHEN AGE < 55 THEN '50-54'
            WHEN AGE < 60 THEN '55-59'
            WHEN AGE < 65 THEN '60-64'
            WHEN AGE < 70 THEN '65-69'
            WHEN AGE < 75 THEN '70-74'
            WHEN AGE < 80 THEN '75-79'
            WHEN AGE < 85 THEN '80-84'
            ELSE '85-99'
        END
),
CTE_2 AS (
    SELECT
        bc.GROUPINGS AS BENEFITS,
		DATEDIFF(YEAR, i.BIRTH_YYYYMMDD, @AGE_AS_OF_YYYYMMDD) AS AGE,
        ibe.VOLUME,
        ibe.AMT as AMOUNT
    FROM INDFIL_FILE i
    JOIN IBEFIL_FILE ibe ON i.CASE_NUM = ibe.CASE_NUM AND i.CERT_NUM = ibe.CERT_NUM
    JOIN #BENEFIT_CODES_2 bc ON ibe.BENEFIT_CODE COLLATE SQL_Latin1_General_CP1_CS_AS = bc.BENEFIT_CODE COLLATE SQL_Latin1_General_CP1_CS_AS
    WHERE
        i.CASE_NUM = '46185'
        AND i.EFF_YYYYMMDD <= @EFFECTIVE_YYYYMMDD
        AND (i.TERM_YYYYMMDD >= @EFFECTIVE_YYYYMMDD OR i.TERM_YYYYMMDD IS NULL)
        AND (COALESCE(ibe.TERM_YYYYMMDD,'') = '' OR ibe.TERM_YYYYMMDD > @EFFECTIVE_YYYYMMDD)
        AND i.CERT_NUM NOT in ('4773','1020','2325')
        AND i.DIV_NUM not in (SELECT [DIV_NUM]
             FROM [FBC].[dbo].[CASFIL_FILE]
             WHERE TERM_YYYYMMDD IS NOT NULL
             AND TERM_YYYYMMDD < @EFFECTIVE_YYYYMMDD
             AND CASE_NUM = '46185')
),
GROUPED_DATA_2 AS (
    SELECT
        BENEFITS,
        CASE
            WHEN AGE < 25 THEN '00-24'
            WHEN AGE < 30 THEN '25-29'
            WHEN AGE < 35 THEN '30-34'
            WHEN AGE < 40 THEN '35-39'
            WHEN AGE < 45 THEN '40-44'
            WHEN AGE < 50 THEN '45-49'
            WHEN AGE < 55 THEN '50-54'
            WHEN AGE < 60 THEN '55-59'
            WHEN AGE < 65 THEN '60-64'
            WHEN AGE < 70 THEN '65-69'
            ELSE '70-99'
        END AS AGE_GROUP,
        COUNT(*) AS LIVES,
        SUM(CAST(VOLUME AS DECIMAL(18,2))) AS VOLUME,
        SUM(CAST(AMOUNT AS DECIMAL(18,2))) AS PREMIUM
    FROM CTE_2
    GROUP BY
        BENEFITS,
        CASE
            WHEN AGE < 25 THEN '00-24'
            WHEN AGE < 30 THEN '25-29'
            WHEN AGE < 35 THEN '30-34'
            WHEN AGE < 40 THEN '35-39'
            WHEN AGE < 45 THEN '40-44'
            WHEN AGE < 50 THEN '45-49'
            WHEN AGE < 55 THEN '50-54'
            WHEN AGE < 60 THEN '55-59'
            WHEN AGE < 65 THEN '60-64'
            WHEN AGE < 70 THEN '65-69'
            ELSE '70-99'
        END
),
ALL_COMBINATIONS_1 AS (
    SELECT bc.GROUPINGS AS BENEFITS, ab.AGE_GROUP, ab.AGE_ORDER
    FROM (SELECT DISTINCT GROUPINGS FROM #BENEFIT_CODES_1) bc
    CROSS JOIN #AGE_BANDS_1 ab
),
ALL_COMBINATIONS_2 AS (
    SELECT bc.GROUPINGS AS BENEFITS, ab.AGE_GROUP, ab.AGE_ORDER
    FROM (SELECT DISTINCT GROUPINGS FROM #BENEFIT_CODES_2) bc
    CROSS JOIN #AGE_BANDS_2 ab
)

-- Final select with zero-value rows, separated by benefit code groups
SELECT
    ac.BENEFITS,
    ac.AGE_GROUP,
    COALESCE(gd.LIVES, 0) AS LIVES,
    COALESCE(gd.VOLUME, 0) AS VOLUME,
    COALESCE(gd.PREMIUM, 0) AS PREMIUM,
    ac.AGE_ORDER
FROM ALL_COMBINATIONS_1 ac
LEFT JOIN GROUPED_DATA_1 gd ON ac.BENEFITS = gd.BENEFITS AND ac.AGE_GROUP = gd.AGE_GROUP

UNION ALL

SELECT
    ac.BENEFITS,
    ac.AGE_GROUP,
    COALESCE(gd.LIVES, 0) AS LIVES,
    COALESCE(gd.VOLUME, 0) AS VOLUME,
    COALESCE(gd.PREMIUM, 0) AS PREMIUM,
    ac.AGE_ORDER
FROM ALL_COMBINATIONS_2 ac
LEFT JOIN GROUPED_DATA_2 gd ON ac.BENEFITS = gd.BENEFITS AND ac.AGE_GROUP = gd.AGE_GROUP

ORDER BY BENEFITS, AGE_ORDER;