Logic GasAccounting|GasReporting

UPDATE CE SET CE.GASAccounting =
CASE WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CB'
    AND SUBSTRING (CE.BUCODE, 3, 1) NOT IN ('0', 'C')
    THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 3) = 'CBC'
    THEN 'COMMERCIAL'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'SM'
    THEN CASE
            WHEN C.PRODUCTTYPE LIKE '%CASH%' OR C.PRODUCTTYPE LIKE '%TUNAI%'
            THEN 'CONSUMER'
            ELSE 'SMALL BUSINESS'
            END
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MB'
    THEN 'MICRO BANKING'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CL'
    THEN 'CONSUMER'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('FI','GI')
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('CC')
        THEN 'CREDIT CARD'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'TR'
        THEN 'TREASURY'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CR'
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MM'
        THEN 'SMALL BUSINESS'
    WHEN CE.BUCODE IS NULL
        THEN NULL
    ELSE
        'SMALL BUSINESS'
END,
CE.GASReporting =
CASE WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CB'
    AND SUBSTRING (CE.BUCODE, 3, 1) NOT IN ('0', 'C')
    THEN 'WHOLESALE CIB'
    WHEN SUBSTRING (CE.BUCODE, 1, 3) = 'CBC'
    THEN 'WHOLESALE COMMERCIAL'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'SM'
    THEN CASE
            WHEN C.PRODUCTTYPE LIKE '%CASH%' OR C.PRODUCTTYPE LIKE '%TUNAI%'
            THEN 'RETAIL INDIVIDU'
            ELSE 'RETAIL SME'
            END
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MB'
    THEN 'RETAIL MICRO'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CL'
    THEN 'RETAIL INDIVIDU'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('FI','GI')
        THEN 'WHOLESALE CIB'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('CC')
        THEN 'CREDIT CARD'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'TR'
        THEN 'TREASURY'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CR'
        THEN 'SAM'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MM'
        THEN 'RETAIL SME'
    WHEN CE.BUCODE IS NULL
        THEN NULL
    ELSE 'RETAIL SME'
END
FROM FDR..ContractsExtension CE
INNER JOIN FDR..Contracts C ON C.ContractId = CE.ContractId
WHERE
C.OriginSystemId IN ('F05') AND
C.ReportingDate = '2019-08-31'
AND BUCode IS NOT NULL

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

UPDATE C SET C.Segment =
CASE WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CB'
    AND SUBSTRING (CE.BUCODE, 3, 1) NOT IN ('0', 'C')
    THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 3) = 'CBC'
    THEN 'COMMERCIAL'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'SM'
    THEN CASE
            WHEN C.PRODUCTTYPE LIKE '%CASH%' OR C.PRODUCTTYPE LIKE '%TUNAI%'
            THEN 'CONSUMER'
            ELSE 'SMALL BUSINESS'
            END
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MB'
    THEN 'MICRO BANKING'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CL'
    THEN 'CONSUMER'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('FI','GI')
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) IN ('CC')
        THEN 'CREDIT CARD'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'TR'
        THEN 'TREASURY'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'CR'
        THEN 'CORPORATE'
    WHEN SUBSTRING (CE.BUCODE, 1, 2) = 'MM'
        THEN 'SMALL BUSINESS'
    WHEN CE.BUCODE IS NULL
        THEN NULL
    ELSE
        'SMALL BUSINESS'
END
FROM FDR..Contracts C
INNER JOIN FDR..ContractsExtension CE ON CE.ContractId = C.ContractId
WHERE
C.OriginSystemId IN ('F05') AND
C.ReportingDate = '2019-08-31'
AND BUCode IS NOT NULL