Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- --Свод по всем вместе с добавленными многолотовыми по измещениям
- Select Coalesce(pg.purchasemethodname, con.purchasemethodname, izv.purchasemethodname) As purchasemethodname,
- pg.rncpg, pg.summpg, con.rnccon, con.summcon, izv.rncizv, izv.summizv,' ' eco, ' ' eco_procent
- From
- (
- Select Case When Upper(purchasemethodname) Like '%КОНКУРС%' And purchasemethodcode in('3359','200608') Then 'Конкурс определенный законом'
- When Upper(purchasemethodname) Like '%АУКЦИОН%' And purchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
- When Upper(purchasemethodname) Like '%КОТИРОВ%' And purchasemethodcode in ('3362','200610') Then 'Запрос котировок'
- When Upper(purchasemethodname) Like '%ПРЕДЛОЖЕНИЙ%' And purchasemethodcode in ('200611') Then 'Запрос предложений'
- When Upper(purchasemethodname) Like '%ЕДИНСТВЕННОГО%' And purchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
- Else 'Иные конкурентные способы'
- End As purchasemethodname, count(*) As rncpg, round(sum(maximumcontractprice::numeric)/1000,2) As summpg
- From eis_purchaseplan_223_inn
- Where shared='false' And
- status <> 'A' And
- bodyitempurchaseplandatareportingyear='2019' And
- bodyitempurchaseplandatapublicationdatetime Between '2019-01-01'And '2019-12-31' And
- bodyitempurchaseplandatareportingyear='2019' And
- bodyitempurchaseplandatastartdate >='2019-01-01' And
- bodyitempurchaseplandataenddate<='2019-12-31'
- Group by Case When Upper(purchasemethodname) Like '%КОНКУРС%' And purchasemethodcode in('3359','200608') Then 'Конкурс определенный законом'
- When Upper(purchasemethodname) Like '%АУКЦИОН%' And purchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
- When Upper(purchasemethodname) Like '%КОТИРОВ%' And purchasemethodcode in ('3362','200610') Then 'Запрос котировок'
- When Upper(purchasemethodname) Like '%ПРЕДЛОЖЕНИЙ%' And purchasemethodcode in ('200611') Then 'Запрос предложений'
- When Upper(purchasemethodname) Like '%ЕДИНСТВЕННОГО%' And purchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
- Else 'Иные конкурентные способы'
- End
- ) As pg Full Join
- (
- Select Case When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%КОНКУРС%' And bodyitemcontractdatapurchasetypeinfocode in('11011') Then 'Конкурс определенный законом'
- When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%АУКЦИОН%' And bodyitemcontractdatapurchasetypeinfocode in ('12012') Then 'Аукцион определенный законом'
- When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%ЕДИНСТВЕННОГО%' And bodyitemcontractdatapurchasetypeinfocode in ('30000') Then 'Закупка у единственного поставщика'
- Else 'Иные конкурентные способы'
- End
- As purchasemethodname, count(*) As rnccon, round(sum(bodyitemcontractdataprice/1000)) As summcon
- From eis_contracts_223_inn
- Where to_timestamp(bodyitemcontractdatacontractdate) Between '2019-01-01'And '2019-12-31'
- Group by Case When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%КОНКУРС%' And bodyitemcontractdatapurchasetypeinfocode in('11011') Then 'Конкурс определенный законом'
- When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%АУКЦИОН%' And bodyitemcontractdatapurchasetypeinfocode in ('12012') Then 'Аукцион определенный законом'
- When Upper(bodyitemcontractdatapurchasetypeinfoname) Like '%ЕДИНСТВЕННОГО%' And bodyitemcontractdatapurchasetypeinfocode in ('30000') Then 'Закупка у единственного поставщика'
- Else 'Иные конкурентные способы'
- End
- ) As con On (pg.purchasemethodname=con.purchasemethodname) Full Join
- (
- Select Case When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОНКУРС%' And bodyitempurchasenoticedatapurchasemethodcode in('3359','200608') Then 'Конкурс определенный законом'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%АУКЦИОН%' And bodyitempurchasenoticedatapurchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОТИРОВ%' And bodyitempurchasenoticedatapurchasemethodcode in ('3362','200610') Then 'Запрос котировок'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ПРЕДЛОЖЕНИЙ%' And bodyitempurchasenoticedatapurchasemethodcode in ('200611') Then 'Запрос предложений'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ЕДИНСТВЕННОГО%' And bodyitempurchasenoticedatapurchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
- Else 'Иные конкурентные способы'
- End As purchasemethodname,
- count( case when a.nmck In ('',' ') then b.bodyitempurchasenoticeaesmbodataregistrationnumber
- else a.bodyitempurchasenoticedataregistrationnumber end) As rncizv,
- round(sum(case when a.nmck='' or a.nmck=' ' then b.lotdatainitialsum else a.nmck::numeric end)/1000) As summizv
- From eis_notice_223all a
- left join eis_notice_223_lotsall b on a.bodyitempurchasenoticedataregistrationnumber=b.bodyitempurchasenoticeaesmbodataregistrationnumber
- Where nmck is Not Null
- Group by Case When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОНКУРС%' And bodyitempurchasenoticedatapurchasemethodcode in('3359','200608') Then 'Конкурс определенный законом'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%АУКЦИОН%' And bodyitempurchasenoticedatapurchasemethodcode in ('3360','3361','200609') Then 'Аукцион определенный законом'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%КОТИРОВ%' And bodyitempurchasenoticedatapurchasemethodcode in ('3362','200610') Then 'Запрос котировок'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ПРЕДЛОЖЕНИЙ%' And bodyitempurchasenoticedatapurchasemethodcode in ('200611') Then 'Запрос предложений'
- When Upper(bodyitempurchasenoticedatapurchasecodename) Like '%ЕДИНСТВЕННОГО%' And bodyitempurchasenoticedatapurchasemethodcode in ('3363') Then 'Закупка у единственного поставщика'
- Else 'Иные конкурентные способы'
- End
- ) As izv On (coalesce(pg.purchasemethodname,con.purchasemethodname)=izv.purchasemethodname)
- Order by purchasemethodname
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement