گزارش پیش رو به درخواست کارفرما برای بررسی عملکرد استان ها در ثبت نام بیمارستان ها و درمانگاه ها استخراج شده است.

select provincename
        ,(select name from tpncpartnertype where id=type_id) as partnertypename
        ,count(1) as cnt
        ,sum(hassubpartner) as hassubpartner
        ,sum(hascontract) as hascontract 
        ,sum(subpartner) as subpartner
        ,sum(contractCount) as contractCount
from
(
    select 
            (select name from vpncgeoinfo where province_id=g.province_id and recordtype=1 and year=93) as provincename
            ,p.nationalnumber as partnernn 
            ,p.partnername as partnername 
            ,p.type_id
            ,(case when (select count(1) from tpncpartner where parent_id=p.id)>0 then 1 else 0 end) as hassubpartner
            ,(select count(1) from tpncpartner where parent_id=p.id) as subpartner
            ,(case when (pkgrecieptprocessutil.fncgetpartneractivecontractorroot(p.id))>0 then 1 else 0 end) as hascontract
            ,(select count(distinct pcp.id) from tpncpartner ppp,tpncpartnercontractingparty pcp ,tpnccontractparty c  
                where pcp.partner_id=ppp.id and pcp.contractparty_id=c.id  and ppp.dto is null and c.dto is null and pcp.dto is null CONNECT by prior ppp.id=ppp.parent_id start with ppp.id=p.id) contractCount
from tpncpartner p,tpncaddress a,vpncgeoinfo g
    where p.address_id=a.id
            and a.geo_id=g.id
            and p.dto is null
    order by g.province_id
)
where type_id in (4,622,624)
group by provincename,type_id
order by cnt desc;