nedvijimost

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 schema1.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 ('ECONOM_ZONE', 'REGISTR_ORG_CADASTR', 'CADASTR_N', 'CADASTRAMOUNT', 
        'BUILDING_TYPE', 'BUILDING_KIND', 'TO_EXPL_D', 'YEAR_CONSTRUCTION', 'PROPERTY_AREA', 
        'LAND_AREA', 'BUILT_UP_AREA', 'EFFECTIVE_AREA', 'LIVING_SPACE', 'ROOM_CNT', 'PROVISION_SOURCE')
    ) sub_t 
    WHERE javlon = 1
),
t_pivoted AS (
    SELECT 
        dealid,
        ECONOM_ZONE, REGISTR_ORG_CADASTR, CADASTR_N, CADASTRAMOUNT, 
        BUILDING_TYPE, BUILDING_KIND, TO_EXPL_D, YEAR_CONSTRUCTION, PROPERTY_AREA, 
        LAND_AREA, BUILT_UP_AREA, EFFECTIVE_AREA, LIVING_SPACE, ROOM_CNT, PROVISION_SOURCE
    FROM t_ordered
    PIVOT (
        MAX(paramvalue) 
        FOR paramname IN (
            'ECONOM_ZONE' AS ECONOM_ZONE,
            'REGISTR_ORG_CADASTR' AS REGISTR_ORG_CADASTR,
            'CADASTR_N' AS CADASTR_N,
            'CADASTRAMOUNT' AS CADASTRAMOUNT,
            'BUILDING_KIND' AS BUILDING_KIND,
            'BUILDING_TYPE' AS BUILDING_TYPE,           
            'TO_EXPL_D' AS TO_EXPL_D,
            'YEAR_CONSTRUCTION' AS YEAR_CONSTRUCTION,
            'PROPERTY_AREA' AS PROPERTY_AREA,
            'LAND_AREA' AS LAND_AREA,
            'BUILT_UP_AREA' AS BUILT_UP_AREA,
            'EFFECTIVE_AREA' AS EFFECTIVE_AREA,
            'LIVING_SPACE' AS LIVING_SPACE,
            'ROOM_CNT' AS ROOM_CNT,
            'PROVISION_SOURCE' AS PROVISION_SOURCE
            )
    )
),
parent_table AS (
    SELECT id, parentid, toboid 
    FROM schema2.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.ECONOM_ZONE AS "Экономическая зона", 
    t.REGISTR_ORG_CADASTR AS "Регистрирующий орган кадастра", 
    t.CADASTR_N AS "Кадастровый номер",
    t.CADASTRAMOUNT/100 AS "Кадастровая стоимость", 
    t.BUILDING_KIND AS "Вид недвижимости", 
    t.BUILDING_TYPE AS "Тип недвижимости",  
    t.TO_EXPL_D AS "Дата ввода в эксплуатацию", 
    t.YEAR_CONSTRUCTION AS "Год постройки", 
    t.PROPERTY_AREA AS "Общая площадь (кв.м.)", 
    t.LAND_AREA AS "Площадь земельного участка (кв.м.)", 
    t.BUILT_UP_AREA AS "Площадь застройки (кв.м.)", 
    t.EFFECTIVE_AREA AS "Полезная площадь (кв.м.)", 
    t.LIVING_SPACE AS "Жилая площадь (кв.м.)", 
    t.ROOM_CNT AS "Количество комнат", 
    t.PROVISION_SOURCE 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.ECONOM_ZONE, t.REGISTR_ORG_CADASTR, t.CADASTR_N, t.CADASTRAMOUNT, t.BUILDING_KIND, t.BUILDING_TYPE, 
    t.TO_EXPL_D, t.YEAR_CONSTRUCTION, t.PROPERTY_AREA, t.LAND_AREA, t.BUILT_UP_AREA, t.EFFECTIVE_AREA, t.LIVING_SPACE, t.ROOM_CNT) IS NOT NULL