SELECT "NA" AS TREATY_ID,
    "NA" AS TREATY_PLAN_ID,
    all_data_concise.POLICY_NUMBER AS CESSION_ID,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
        "_",
        `all_data_concise`.`BENEFIT_INDICATOR`,
        "_",
        COALESCE(`GenderToUse`, TRIM(`all_data_concise`.`SEX`))
        ) AS CESSION_BENEFIT_ID,
    "NA" AS CESSION_PLAN_ID,
    CAST(NULL AS CHAR(10)) AS ASSUMED_DISTRIBUTION_ID,
    "TEMP" AS ASSUMING_BENEFIT_ID,
    "NA" AS SOURCE_BENEFIT_ID,
    "NA" AS RISK_ASSESSMENT_ID,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y")
        ) AS CESSION_ID_NUMBER,
    `Q04d Final snapshot reference`.STATUS AS POLSTATUS,
    "OCMPY" AS ORIG_COMPANY,
    "ACMPY" AS ASSUMING_COMPANY,
    "Experience Analysis" AS PLAN_NAME,
    MIN(all_data_concise.TABLE) AS PLAN_CODE,
    "TEMP" AS BENEFIT_NAME,
    "TEMP" AS BENEFIT_CODE,
    "TEMP" AS LT_BENEFIT_CODE,
    "NA" AS TREATY_BENEFIT_ID,
    "TEMP" AS TREATY_BENEFIT_NAME,
    "I" AS FUND_ACCOUNTING_TYPE,
    `Q04d Final snapshot reference`.POLICY_NUMBER AS POLICY_NUMBER,
    "NXT" AS AGING_METHOD,
    MIN(all_data_concise.COMMENCEMENT_DATE) AS ISSUE_DATE,
    MIN(all_data_concise.REASSURANCE_COMMENCEMENT_DATE) AS DATE_ON_RISK,
    MIN(all_data_concise.REASSURANCE_COMMENCEMENT_DATE) AS DATE_ON_BOOKS,
    SUM(all_data_concise.SUM_AT_RISK_REASSURED) AS ORIG_FACE_AAR,
    SUM(all_data_concise.SUM_AT_RISK_REASSURED) AS LIFE_REIN_FACE,
    "A" AS PREMFREQ,
    `Q04d Final snapshot reference`.DATE_OF_BIRTH AS DOB,
    COALESCE(`GenderToUse`, `all_data_concise`.`SEX`) AS POLSEX,
    MIN(CASE 
            WHEN MID(TRIM(`BASIC_RATE_GROUP`), 2, 1) = "R"
                THEN "S"
            ELSE "NS"
            END) AS SMOKER_CLASS,
    MIN(100 + TRUNCATE (
            `EXTRA_MORTALITY` / 100,
            0
            )) AS UNDERWRITER_RATING,
    MIN(all_data_concise.AGE_NEXT_BIRTHDAY) AS ADJUSTED_AGE,
    CAST(NULL AS INTEGER) AS AGE_RATE_UP,
    MIN(all_data_concise.AGE_NEXT_BIRTHDAY) AS ISSUE_AGE,
    SUM(all_data_concise.SUM_AT_RISK_REASSURED) AS INFORCE_AAR,
    CASE 
        WHEN LEFT(TRIM(`ASSESSMENT_DATE`), 6) = "TREATY"
            OR LEFT(TRIM(`ASSESSMENT_DATE`), 4) = "RETE"
            THEN "AUT"
        WHEN INSTR(`ASSESSMENT_DATE`, "-") > 0
            THEN "FOB"
        WHEN `ASSESSMENT_DATE` IS NULL
            AND `all_data_concise`.`POLICY_NUMBER` NOT LIKE "1%"
            THEN "NULL"
        ELSE "FAC"
        END AS AUTO_FAC,
    MAX(all_data_concise.BASIC_TERM) AS TERM,
    MIN(`OCCUPATIONAL_LOADING` / 1000 * 11.4) AS FLAT_XTRA,
    CAST(NULL AS INTEGER) AS XTRA_DURATION,
    "A" AS ASMD_RETRO,
    0 AS RETRO_RATING_GROUP,
    0 AS RETRO_UWCLASS,
    0 AS RETRO_OCCUPATION,
    0 AS RETRO_SYNDICATE,
    MIN(INSTR("BNVSP", LEFT(`BASIC_RATE_GROUP`, 1))) AS ASS_RATING_GROUP,
    MIN(all_data_concise.BASIC_RATE_GROUP) AS ASS_UWCLASS,
    LEFT(MAX(TRIM(`OCCUPATION`)), 1) AS ASS_OCCUPATION,
    CAST(NULL AS CHAR(10)) AS ASS_SYNDICATE,
    MAX(CONCAT (
            TRIM(`all_data_concise`.`SURNAME`),
            "_",
            DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
            "_",
            COALESCE(`GenderToUse`, TRIM(`all_data_concise`.`SEX`))
            )) AS INSURED_ID,
    "A Treaty" AS DESCRIPTION,
    CAST(NULL AS CHAR(10)) AS TREATY_GROUP_ID,
    `Q04d Final snapshot reference`.BENEFIT_INDICATOR AS CEDING_BENEFIT_CODE,
    CAST(NULL AS CHAR(10)) AS COVER_BASIS,
    SUM(all_data_concise.PREMIUM) AS STANDARD_PREMIUM,
    SUM(`EXTRA_PREMIUM_REST` + `EXTRA_PREMIUM_MEDICAL`) AS PHONY_FLAT_XTRA,
    CAST(NULL AS CHAR(10)) AS TPD_CLASS,
    SUM(`PREMIUM` + `EXTRA_PREMIUM_REST` + `EXTRA_PREMIUM_MEDICAL`) AS TOTAL_PREMIUM,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
        "_",
        `all_data_concise`.`BENEFIT_INDICATOR`,
        "_",
        COALESCE(`GenderToUse`, TRIM(`all_data_concise`.`SEX`))
        ) AS O_CESSION_BENEFIT_ID,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
        "_",
        `all_data_concise`.`BENEFIT_INDICATOR`
        ) AS GOVERNING_BENEFIT_ID,
    "resoft-28875" AS ASSUMING_COMPANY_ID,
    `Q04d Final snapshot reference`.TermDate_to_use AS TERMINATION_DATE,
    CAST(NULL AS DATE) AS QUOTE_DATE,
    0 AS FUNERAL_BENEFIT,
    MAX(CAST(TRIM(`INDEX_COVER`) AS INTEGER)) AS INDEXATION,
    CAST(NULL AS INTEGER) AS REBATE_SACRIFICE_PCT,
    CAST(NULL AS DATE) AS RECEIVED_DATE,
    CAST(NULL AS INTEGER) AS REBATE_COMMISSION_PCT,
    CAST(NULL AS INTEGER) AS INITIAL_REBATE,
    - 1 AS NIL_PREMIUM_PERIOD,
    0 AS STANDARD_REBATE,
    0 AS RENEWAL_REBATE,
    0 AS IMPUTED_PREMIUM,
    0 AS IMPUTED_ALLOWANCE,
    CAST(NULL AS CHAR(3)) AS RISK_CLASS_CODE,
    0 AS COMMISSION_PCT,
    MIN(all_data_concise.TABLE) AS CEDING_PLAN_CODE,
    MAX(all_data_concise.ACCELERATOR_FACTOR) AS ACC_IND,
    CASE 
        WHEN MAX(`RACE`) = "0"
            THEN "MX"
        ELSE "UL"
        END AS Product
FROM `Q04d Final snapshot reference`
INNER JOIN all_data_concise ON (`Q04d Final snapshot reference`.POLICY_NUMBER = all_data_concise.POLICY_NUMBER
        )
    AND (`Q04d Final snapshot reference`.DATE_OF_BIRTH = all_data_concise.DATE_OF_BIRTH)
    AND (`Q04d Final snapshot reference`.BENEFIT_INDICATOR = all_data_concise.BENEFIT_INDICATOR)
    AND (`Q04d Final snapshot reference`.MaxMonthToUse = all_data_concise.MaxMonth)
    AND (`Q04d Final snapshot reference`.SEX = all_data_concise.SEX)
LEFT JOIN `Q01e Genders` ON (all_data_concise.POLICY_NUMBER = `Q01e Genders`.POLICY_NUMBER)
    AND (all_data_concise.DATE_OF_BIRTH = `Q01e Genders`.DATE_OF_BIRTH)
    AND (all_data_concise.SURNAME = `Q01e Genders`.SURNAME)
    AND (all_data_concise.INITIALS = `Q01e Genders`.INITIALS)
GROUP BY all_data_concise.POLICY_NUMBER,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y")
        ),
    all_data_concise.date_of_birth,
    `Q04d Final snapshot reference`.STATUS,
    "TEMP",
    `Q04d Final snapshot reference`.POLICY_NUMBER,
    `Q04d Final snapshot reference`.DATE_OF_BIRTH,
    COALESCE(`GenderToUse`, `all_data_concise`.`SEX`),
    CASE 
        WHEN LEFT(TRIM(`ASSESSMENT_DATE`), 6) = "TREATY"
            OR LEFT(TRIM(`ASSESSMENT_DATE`), 4) = "RETE"
            THEN "AUT"
        WHEN INSTR(`ASSESSMENT_DATE`, "-") > 0
            THEN "FOB"
        WHEN `ASSESSMENT_DATE` IS NULL
            AND `all_data_concise`.`POLICY_NUMBER` NOT LIKE "1*"
            THEN "NULL"
        ELSE "FAC"
        END,
    all_data_concise.assessment_date,
    `Q04d Final snapshot reference`.BENEFIT_INDICATOR,
    all_data_concise.benefit_indicator,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
        "_",
        `all_data_concise`.`BENEFIT_INDICATOR`,
        "_",
        COALESCE(`GenderToUse`, TRIM(`all_data_concise`.`SEX`))
        ),
    `q01e genders`.gendertouse,
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
        "_",
        `all_data_concise`.`BENEFIT_INDICATOR`
        ),
    `Q04d Final snapshot reference`.TermDate_to_use,
    "TEMP",
    "TEMP",
    "TEMP",
    "TEMP",
    CONCAT (
        `all_Data_concise`.`POLICY_NUMBER`,
        "_",
        DATE_FORMAT(`all_data_concise`.`DATE_OF_BIRTH`, "%d%m%Y"),
        "_",
        `all_data_concise`.`BENEFIT_INDICATOR` & "_" & COALESCE(`GenderToUse`, TRIM(`all_data_concise`.`SEX`))
        ),
		  all_data_concise.sex
	HAVING `Q04d Final snapshot reference`.`BENEFIT_INDICATOR` IS NOT NULL LIMIT 1000;