[go: up one dir, main page]
Scribd
Upload
12 views2 pages

Old SQL Script

The document outlines a SQL query that retrieves various metrics related to service providers, including total contracts, daily contracts, and candidate counts. It involves multiple joins to aggregate data from organization contracts, candidates, and districts, filtering based on specific conditions. The final output includes a row number and essential details about each service provider's performance metrics.

Uploaded by

Shukrullo Temirov
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
12 views2 pages

Old SQL Script

The document outlines a SQL query that retrieves various metrics related to service providers, including total contracts, daily contracts, and candidate counts. It involves multiple joins to aggregate data from organization contracts, candidates, and districts, filtering based on specific conditions. The final output includes a row number and essential details about each service provider's performance metrics.

Uploaded by

Shukrullo Temirov
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
You are on page 1/ 2

select row_number() over(order by t.

total_contracts) rownum,
s.org_id, s.org_short_name as name, total_contracts, area_count,
district_count, disposal_count, daily_contract,
total_candidates, legal, yatt, other, total_sum, daily_sum
from service_providers s
left join (select provider_org_id,
count(t2.id) filter ( where extract(day from
t2.created_at) = extract(day from now())) as daily_contract,
count(t2.id)
as total_contracts,
sum(total_sum)
as total_sum,
sum(t2.total_sum)
filter ( where extract(day from t2.created_at) =
extract(day from now())) as daily_sum
from (select provider_org_id,
oc.created_at,
oc.id,
coalesce(oc.monthly_fee,
oc.average_monthly_sum) / 1000000 as total_sum

from organization_contracts oc

where state = 1
) t2 group by provider_org_id) t on t.provider_org_id =
s.org_id

left join (select d.org_id,


null

as area_count,
max(d.total)
as district_count,
max(d.disposal_count)
as disposal_count,
count(c.id)
as total_candidates,
count(c.id) filter ( where c.owner_tin is not null )
as legal,
count(c.id) filter ( where c.owner_pinfl is not null and
owner_tin is null ) as yatt,
count(c.id) filter ( where c.owner_tin is null and
c.owner_pinfl is null ) as other
from candidates_v2 c
left join (select d.area_id,
d.org_id,
count(d.id) total,
sum(disposal_count) as disposal_count
from districts d
left join (select district_id,
count(id) as disposal_count
from disposals
where state = 1
group by district_id) d1
on d1.district_id = d.id
where d.state = 1
group by d.area_id, d.org_id) d on d.area_id
= c.area_id
where c.state = 1
and c.source_type != 1
group by d.org_id) d on d.org_id = s.org_id
where s.state = 1 and s.obl_id = #obl_id

-- select case when district_id = 0 then #obl_id else obl_id end as


obl_id,
-- case when district_id = 0 then #area_id else area_id end as
area_id,
-- case when district_id = 0 then #district_id else district_id end as
district_id,
-- case when personal_program_id = 0 then #personal_program_id else
personal_program_id end as personal_program_id
-- from poor_family_memebers_ih s
-- where s.id = #id
-- union all
-- select #obl_id as obl_id, #area_id as area_id, #district_id as district_id,
#personal_program_id as personal_program_id
-- limit 1

You might also like