select
    NP_CODE_NAME,
    FP_CODE_NAME,
    customername,
    grbsname,
    kol_nr,
    sum_nr,
    finsum_nr,
    kol_n,
    sum_r,
    finsum_r,
    fin_itog,
    kol_z,
    nmck_z,
    fin_z,
    kol_net,
    nmck_net,
    fin_z_net,
    ROW_NUMBER() OVER (PARTITION BY NP_CODE_NAME ORDER BY FP_CODE_NAME) rn
from (
select
    tar.NP_CODE_NAME, tar.FP_CODE_NAME,
    COALESCE(con.customername, zak_raz.orgtitle, zak_net.orgtitle) customername,
    COALESCE(con.grbsname, zak_raz.grbstitle, zak_net.grbstitle) grbsname,
    con.kol_nr,
    con.sum_nr,
    con.finsum_nr,
    con.kol_n,
    con.sum_r,
    con.finsum_r,
    con.fin_itog,
    zak_raz.kol_req kol_z,
    zak_raz.nmck nmck_z,
    zak_raz.fins fin_z,
    zak_net.kol_req kol_net,
    zak_net.nmck nmck_net,
    zak_net.fins fin_z_net,
    ROW_NUMBER() OVER (PARTITION BY tar.NP_CODE_NAME,tar.FP_CODE_NAME,COALESCE(con.customername, zak_raz.orgtitle, zak_net.orgtitle) ORDER BY tar.NP_CODE_NAME,tar.FP_CODE_NAME desc) rn
from DWH_TARGETEXPENSEITEMCODE tar
left join
    (
               SELECT
                      t.NP_CODE_NAME,t.FP_CODE_NAME,
                      con.customername, con.grbsname,
                      count (case when con.CONTRACTREJECTDATE is Null then con.LOTID end) kol_nr, -- количество нерассторгнутых контрактов
                      round(sum (case when con.CONTRACTREJECTDATE is Null then con.CONTRACTSIGNINGPRICE end) / 1000000,1) sum_nr, -- цена контракта нерассторгнутых
                      ROW_NUMBER() OVER (PARTITION BY max(t.NP_CODE_NAME)/*,t.FP_CODE_NAME*/ ORDER BY t.FP_CODE_NAME) rn ,
                      round(sum (case when con.CONTRACTREJECTDATE is Null then tar.finsum end)/1000000,1) finsum_nr, --финансирование 2019г.

                      count ( case when con.CONTRACTREJECTDATE is Not Null then con.LOTID end) kol_n,
                      round(sum (case when con.CONTRACTREJECTDATE is Not Null then con.CONTRACTSIGNINGPRICE end) / 1000000,1) sum_r,
                      round(sum (case when con.CONTRACTREJECTDATE is Not Null then tar.finsum end)/1000000,1) finsum_r,

                      round(sum(finsum)/1000000,1) fin_itog
                FROM (
                        SELECT TARGETEXPENSEITEMCODE, LOT_ID, sum(FINSUM) finsum
                        FROM DWH_CONTRACT_FIN_KGNTV
                        where YEAR = 2019 and TYPE = 1 and TARGETEXPENSEITEMCODE in (select TARGETEXPENSEITEMCODE from DWH_TARGETEXPENSEITEMCODE)
                        GROUP BY TARGETEXPENSEITEMCODE, LOT_ID
                      ) tar
                left join DWH_CONTRACT_KGNTV con ON con.LOTID = tar.LOT_ID
                right join DWH_TARGETEXPENSEITEMCODE t on tar.TARGETEXPENSEITEMCODE = t.TARGETEXPENSEITEMCODE
                --where con.contractrnk is not null --and t.fp_code_name = 'Спорт - норма жизни' and con.customername is null
             --   where con.customername is not null
                where con.customerid=46890
                Group by t.NP_CODE_NAME,t.FP_CODE_NAME,con.customername, con.grbsname
                Order by t.NP_CODE_NAME,t.FP_CODE_NAME
      )con on tar.fp_code_name = con.FP_CODE_NAME
Left join
    (
        Select
                t.NP_CODE_NAME,t.FP_CODE_NAME,lot.orgtitle, lot.grbstitle,
                ROW_NUMBER() OVER (PARTITION BY max(t.NP_CODE_NAME) ORDER BY t.FP_CODE_NAME) rn ,
                count(lot.reqnum) kol_req,
                round(sum(lot.F_PRODUCTPRICE)/1000000,1) nmck,
                round(sum(lot.FINSUM)/1000000,1) fins

        From (
                Select fin.LOTUUID l_lot, pro.lotuuid p_lot, pro.REQNUM, fin.finsum, fin.TARGETEXPENSEITEMCODE, pro.F_PRODUCTPRICE, pro.REQUESTID, pro.orgtitle, pro.grbstitle
                from (
                              select LOTUUID, TARGETEXPENSEITEMCODE,
                              sum(FINVALUE) FINSUM
                              from dwh_lot_fin_kgntv
                              where FINYEAR = 2019 and TARGETEXPENSEITEMCODE in (select TARGETEXPENSEITEMCODE from DWH_TARGETEXPENSEITEMCODE)
                                    GROUP BY LOTUUID, TARGETEXPENSEITEMCODE
                        ) fin
                  LEFT join DWH_PROCEDURES_KGNTV pro on pro.LOTUUID=fin.LOTUUID
                  left join DWH_CONTRACT_KGNTV con on fin.lotuuid=con.LOTID
                  where pro.REQNUM is not null and con.CONTRACTRNK is null and ( pro.FINAL_DECISION <>'Отмена закупки' or pro.FINAL_DECISION is null) and pro.LOTSTAGE <> 'Процедура отменена'
                        and pro.orgid=46890 and pro.reqnum not in ( 0172200002519000004, 0372200121119000002, 0372200121119000004, 0372200121119000005, 0372200121119000003, 0372200121119000001, 0172200003819000048, 0172200001419000075,
                                                          0172200006619000033, 0172200004619000029, 0372200277319000130, 0372200277319000130, 0172200003619000029, 0372200277319000104, 0172200000419000080, 0372200099919000012,
                                                          0372200009419000008, 0372200137919000012, 0372200074719000006, 0372200137919000024, 0372200000719000008, 0372200167619000006, 0372200037419000030, 0372200037419000040,
                                                          0372200037419000020, 0372200225619000007, 0372200138319000020, 0372200097619000012, 0372200164419000005, 0372200097619000011, 0372200029619000009, 0372200062219000012,
                                                          0372200122219000030, 0372200176719000001, 0372200141819000006, 0372200141819000009, 0372200121119000032, 0372200274019000003, 0372200097619000005, 0372200277319000084,
                                                          0372200129219000002, 0372200062219000017, 0372200137919000021, 0372200140019000014, 0372200286519000007, 0372200278019000030, 0372200138219000033, 0372200274019000025,
                                                          0372200274019000016, 0372200056619000003, 0372200099919000008, 0372200056719000002, 0372200138319000001, 0372200275919000018, 0372200037419000039, 0372200277319000155,
                                                          0372200290019000046, 0372200273219000023, 0372200277319000134, 0372200275919000014, 0372200233519000021, 0372200062419000007, 0372200006119000009, 0372200141819000019,
                                                          0372200067419000016, 0372200202919000023, 0372200137919000029, 0372200202919000017, 0372200202919000014, 0372200062419000026, 0372200062419000004, 0372200137919000014,
                                                          0372200212419000023, 0372200037419000025, 0372200273219000024, 0372200273219000017, 0372200273219000026, 0372200057119000015, 0372200202919000015, 0372200060119000011,
                                                          0372200273219000029, 0372200001719000013, 0372200104719000038, 0372200288519000024, 0372200182619000007, 0372200006119000014, 0372200070819000015, 0372200263019000030,
                                                          0372200202919000018, 0372200273219000018, 0372200006119000023, 0372200263019000022, 0372200009919000021, 0372200074819000014, 0372200273219000007, 0372200181919000010,
                                                          0372200037419000038, 0372200037419000015, 0372200062419000017, 0372200263319000006, 0372200138219000020, 0372200082419000067)
              )lot
          RIGHT JOIN DWH_TARGETEXPENSEITEMCODE t on lot.TARGETEXPENSEITEMCODE = t.TARGETEXPENSEITEMCODE
          --where lot.orgtitle is not null
          group by t.NP_CODE_NAME,t.FP_CODE_NAME,lot.orgtitle, lot.grbstitle
          Order by t.NP_CODE_NAME,t.FP_CODE_NAME
    )zak_raz on tar.fp_code_name =zak_raz.FP_CODE_NAME
left join
    (
      Select
            t.NP_CODE_NAME,t.FP_CODE_NAME, lot.orgtitle, lot.grbstitle,
            ROW_NUMBER() OVER (PARTITION BY max(t.NP_CODE_NAME) ORDER BY t.FP_CODE_NAME) rn ,
            count(lot.reqnum) kol_req,
            round(sum(lot.F_PRODUCTPRICE)/1000000,1) nmck,
            round(sum(lot.FINSUM)/1000000,1) fins

      From (
              Select fin.LOTUUID l_lot, pro.lotuuid p_lot, pro.REQNUM, fin.finsum, fin.TARGETEXPENSEITEMCODE, pro.F_PRODUCTPRICE, pro.REQUESTID, pro.orgtitle, pro.grbstitle
              from (
                    select LOTUUID, TARGETEXPENSEITEMCODE,
                           sum(FINVALUE) FINSUM
                    from dwh_lot_fin_kgntv
                    where FINYEAR = 2019 and TARGETEXPENSEITEMCODE in (select TARGETEXPENSEITEMCODE from DWH_TARGETEXPENSEITEMCODE)
                   GROUP BY LOTUUID, TARGETEXPENSEITEMCODE
                    ) fin
                LEFT join DWH_PROCEDURES_KGNTV pro on pro.LOTUUID=fin.LOTUUID
                left join DWH_CONTRACT_KGNTV con on fin.lotuuid=con.LOTID
                where pro.REQNUM is null and con.CONTRACTRNK is null and ( pro.FINAL_DECISION <>'Отмена закупки' or pro.FINAL_DECISION is null) and pro.LOTSTAGE <> 'Процедура отменена' and  pro.OOSLOTNUM is not null
                      and pro.fordertypename <> 'Особая закупка'
                      and pro.orgid=46890
            )lot
      RIGHT JOIN DWH_TARGETEXPENSEITEMCODE t on lot.TARGETEXPENSEITEMCODE = t.TARGETEXPENSEITEMCODE
    --  where lot.orgtitle is not null
      group by t.NP_CODE_NAME,t.FP_CODE_NAME, lot.orgtitle, lot.grbstitle
      Order by t.NP_CODE_NAME,t.FP_CODE_NAME
    )zak_net on tar.fp_code_name=zak_net.FP_CODE_NAME
Order by tar.NP_CODE_NAME,tar.FP_CODE_NAME
)
where rn=1 --and FP_CODE_NAME ='Спорт - норма жизни'
ORDER BY NP_CODE_NAME, FP_CODE_NAME;