avto

WITH t2 AS ( 
    SELECT 
        id, 
        siteid, 
        securitydescription,
        currencyid, marketamount, amounteq 
    FROM ( 
        SELECT 
            d.id, d.siteid, d.securitydescription, d.amounteq, d.currencyid, d.marketamount, 
            ROW_NUMBER() OVER (PARTITION BY d.id ORDER BY d.date_to DESC) AS usman 
        FROM schema_1.second_deal d 
    ) sub_t2 
    WHERE usman = 1 
), 
t_ordered AS (
    SELECT 
        dealid, 
        paramname, 
        paramvalue
    FROM ( 
        SELECT 
            v.dealid, 
            v.paramname, 
            v.paramvalue,
            ROW_NUMBER() OVER (PARTITION BY v.dealid, v.paramname ORDER BY v.date_to DESC) AS javlon 
        FROM schema1.second_deal_dop_param v 
        WHERE v.paramname IN ('CAR_PURCHASE_MARKET', 'TRANSPORT_TYPE', 'ENGINE_NUMBER', 'BODY_N', 
        'PRODUCTION_YEAR', 'ORIG_COUNTRY', 'LICENSE_PLATE', 'CHASSIS_N', 'TRANSP_COLOR', 
        'INVENTORY_N', 'TRANSP_RUN', 'TECH_PASP_SN', 'TECH_PASP_D', 'TECH_PASP_EXP_D', 'TRANSMISSION_TYPE', 
        'TRANS_MAN_NEW', 'COMPLECT', 'TRANSP_МАРКА', 'TRANS_MOD_NEW', 'TRANSP_MODEL', 
        'VEHICLE_ADD_INFO', 'CARPRICE', 'TYPE OF FUEL', 'TECH_SERIAL', 'TECH_NUM', 'TECH_ISSUE', 'VEHICLE_ID', 'TYPEREGNEWCAR')
    ) sub_t 
    WHERE javlon = 1
),
t_pivoted AS (
    SELECT 
        dealid,
        CAR_PURCHASE_MARKET, TRANSPORT_TYPE, ENGINE_NUMBER, BODY_N, 
        PRODUCTION_YEAR, ORIG_COUNTRY, LICENSE_PLATE, CHASSIS_N, TRANSP_COLOR, 
        INVENTORY_N, TRANSP_RUN, TECH_PASP_SN, TECH_PASP_D, TECH_PASP_EXP_D, TRANSMISSION_TYPE, 
        TRANS_MAN_NEW, COMPLECT, TRANSP_МАРКА, TRANS_MOD_NEW, TRANSP_MODEL, 
        VEHICLE_ADD_INFO, CARPRICE, TYPE_OF_FUEL, TECH_SERIAL, TECH_NUM, TECH_ISSUE, VEHICLE_ID, TYPEREGNEWCAR
    FROM t_ordered
    PIVOT (
        MAX(paramvalue) 
        FOR paramname IN (
            'CAR_PURCHASE_MARKET' AS CAR_PURCHASE_MARKET,
            'TRANSPORT_TYPE' AS TRANSPORT_TYPE,
            'ENGINE_NUMBER' AS ENGINE_NUMBER,
            'BODY_N' AS BODY_N,
            'PRODUCTION_YEAR' AS PRODUCTION_YEAR,
            'ORIG_COUNTRY' AS ORIG_COUNTRY,
            'LICENSE_PLATE' AS LICENSE_PLATE,
            'CHASSIS_N' AS CHASSIS_N,
            'TRANSP_COLOR' AS TRANSP_COLOR,
            'INVENTORY_N' AS INVENTORY_N,
            'TRANSP_RUN' AS TRANSP_RUN,
            'TECH_PASP_SN' AS TECH_PASP_SN,
            'TECH_PASP_D' AS TECH_PASP_D,
            'TECH_PASP_EXP_D' AS TECH_PASP_EXP_D,
            'TRANSMISSION_TYPE' AS TRANSMISSION_TYPE,
            'TRANS_MAN_NEW' AS TRANS_MAN_NEW,
            'COMPLECT' AS COMPLECT,
            'TRANSP_МАРКА' AS TRANSP_МАРКА,
            'TRANS_MOD_NEW' AS TRANS_MOD_NEW,
            'TRANSP_MODEL' AS TRANSP_MODEL,
            'VEHICLE_ADD_INFO' AS VEHICLE_ADD_INFO,
            'CARPRICE' AS CARPRICE,
            'TYPE OF FUEL' AS TYPE_OF_FUEL,
            'TECH_SERIAL' AS TECH_SERIAL,
            'TECH_NUM' AS TECH_NUM,
            'TECH_ISSUE' AS TECH_ISSUE,
            'VEHICLE_ID' AS VEHICLE_ID,
            'TYPEREGNEWCAR' AS TYPEREGNEWCAR
        )
    )
),
parent_table AS (
    SELECT id, parentid, toboid 
    FROM schema2_k.deal 
    WHERE dealtypeid = 357
)
SELECT 
    t2.siteid AS "МФО", 
    pt.toboid AS "код ТОБО",
    pt.parentid AS "код анкеты/РНК", 
    COALESCE(t2.id, t.dealid) AS "Код сделки обеспечения",
    t2.amounteq/100 AS "Сумма обеспечения, эквивалент", 
    t2.currencyid AS "Вал. обеспечения", 
    t2.marketamount/100 AS "Рыночная стоимость",  
    t.TRANSPORT_TYPE AS "Тип транспорта",
    t.TRANS_MAN_NEW AS "Марка",    
    t.TRANSP_МАРКА AS "Марка (если другое)",  
    t.TRANS_MOD_NEW AS "Модель",     
    t.TRANSP_MODEL AS "Модель (если другое)", 
    t.COMPLECT AS "Модель и комплектация транспортного средства",     
    t.LICENSE_PLATE AS "Государственный номерной знак",     
    t.PRODUCTION_YEAR AS "Год производства (транспортные средтва)", 
    t.TRANSMISSION_TYPE AS "Тип коробки переключения передач", 
    t.TRANSP_COLOR AS "Цвет",     
    t.TYPE_OF_FUEL AS "Вид топлива",
    t.TECH_PASP_SN AS "Серия и номер  тех.паспорта", 
    t.TECH_PASP_D AS "Дата тех.паспорта",  
    t.TECH_PASP_EXP_D AS "Дата истечения срока действия тех.паспорта", 
    t.BODY_N AS "Номер кузова",             
    t.ENGINE_NUMBER AS "Номер двигателя", 
    t.CHASSIS_N AS "Номер шасси", 
    t.INVENTORY_N AS "Инвентарный номер", 
    t.TRANSP_RUN AS "Пробег", 
    t.ORIG_COUNTRY AS "Страна производителя", 
    t.CAR_PURCHASE_MARKET AS "Рынок приобретения транспортного средства",  
    t.CARPRICE/100 AS "Стоимость транспортного средства (дог.купли-продажи)",     
    t.VEHICLE_ID AS "Уникальный код  транспортного средства",     
    t.TYPEREGNEWCAR AS "Вид регистрации нового автомобиля",     
    t.TECH_ISSUE AS "Орган, выдавший тех.паспорт",     
    t.TECH_SERIAL AS "Серия тех.паспорта",     
    t.TECH_NUM AS "Номер тех.паспорта",     
    t.VEHICLE_ADD_INFO AS "Дополнительное инфо о транс. Средства", 
    t2.securitydescription AS "Дополнительная информация"


FROM t2 
FULL OUTER JOIN t_pivoted t ON t2.id = t.dealid 
LEFT JOIN parent_table pt ON COALESCE(t2.id, t.dealid) = pt.id
WHERE COALESCE(
    t.TRANSPORT_TYPE, t.TRANS_MAN_NEW, t.TRANSP_МАРКА, t.TRANS_MOD_NEW, 
    t.TRANSP_MODEL, t.COMPLECT, t.LICENSE_PLATE, t.PRODUCTION_YEAR, 
    t.TRANSMISSION_TYPE, t.TRANSP_COLOR, t.TYPE_OF_FUEL, t.TECH_PASP_SN, 
    t.TECH_PASP_D, t.TECH_PASP_EXP_D, t.BODY_N, t.ENGINE_NUMBER, t.CHASSIS_N, t.INVENTORY_N, 
    t.TRANSP_RUN, t.ORIG_COUNTRY, t.CAR_PURCHASE_MARKET, t.CARPRICE, 
    t.VEHICLE_ADD_INFO, t.TECH_SERIAL, t.TECH_NUM, t.TECH_ISSUE, t.VEHICLE_ID, t.TYPEREGNEWCAR 
) IS NOT NULL