Skip to content

Instantly share code, notes, and snippets.

@jnkindi
Created August 25, 2019 22:51
Show Gist options
  • Save jnkindi/e41176ac9e30303e2c32481c0a5bb7cb to your computer and use it in GitHub Desktop.
Save jnkindi/e41176ac9e30303e2c32481c0a5bb7cb to your computer and use it in GitHub Desktop.
with parceldetails as (
select
pv.upi,
cp.province_code AS provincecode,
cp.province_name AS provincename,
cd.district_code AS districtcode,
cd.district_name AS districtname,
cs.sector_code AS sectorcode,
cs.sector_name AS sectorname,
cc.cell_code AS cellcode,
cc.cell_name AS cellname,
p.village as villagename,
p.village_code as villagecode,
pv.area AS area,
clu.land_use_type_id AS codeusage,
clu.land_use_name_kinyarwanda AS descriptionusage,
cr.right_type_name as titledeed
FROM admin.parcel_version pv
LEFT JOIN admin.catalog_province cp ON 'substring'(pv.upi, 1, 1) = 'substring'(cp.province_code, 2, 1)
LEFT JOIN admin.catalog_district cd ON 'substring'(replace(pv.upi, '/', ''), 1, 3) = cd.district_code
LEFT JOIN admin.catalog_sector cs ON 'substring'(replace(pv.upi, '/', ''), 1, 5) = cs.sector_code
LEFT JOIN admin.catalog_cell cc ON 'substring'(replace(pv.upi, '/', ''), 1, 7) = cc.cell_code
LEFT JOIN admin.catalog_land_use clu ON clu.land_use_type_id = pv.land_use_type_id
LEFT JOIN sde.parcel p on p.upi=pv.upi
LEFT JOIN admin.catalog_right cr on cr.right_type_id=pv.right_type_id where pv.upi='1/02/11/03/454'
), parcelowner as (
select rr.upi,
CASE
WHEN pa.party_type = 'NON_NATURAL_PERSON' THEN nnp.full_name
WHEN pa.party_type = 'NATURAL_PERSON' THEN concat(np.surname, ' ', np.forenames)
ELSE NULL
END AS ownernames,
CASE
WHEN pa.party_type = 'NON_NATURAL_PERSON' THEN nnp.id_no
WHEN pa.party_type = 'NATURAL_PERSON' THEN np.id_no
ELSE NULL
END AS ownerid,
pa.party_type
FROM admin.real_right rr
LEFT JOIN admin.natural_person_version np on np.party_id=rr.party_id
LEFT JOIN admin.non_natural_person_version nnp on nnp.party_id=rr.party_id
LEFT JOIN admin.party pa on pa.party_id=rr.party_id
where rr.end_transaction_id is null and (np.end_transaction_id is null)
), parcelrep as (
select pr.upi,
concat(np.surname, ' ', np.forenames) AS representativenames,
np.id_no AS representativeid from admin.object_representative pr
left join admin.natural_person_version np on np.party_id=pr.representative_party_id
LEFT JOIN admin.party pa on pa.party_id=pr.representative_party_id
where pr.upi='1/02/11/03/454' and pr.end_transaction_id is null
), status as (
select min(t.id),rr.upi,t.receive_date as registrationdate from admin.real_right rr left join admin.transaction t on t.transaction_id=rr.start_transaction_id
where rr.upi='1/02/11/03/454' and rr.end_transaction_id is null and t.status in ('APPROVED','COMPLETED','PRINTED')
group by rr.upi,t.receive_date)
SELECT distinct parceldetails.*, parcelowner.*, parcelrep.*,status.registrationdate from parceldetails inner join parcelowner on parcelowner.upi= parceldetails.upi inner join parcelrep on parcelrep.upi=parcelowner.upi inner join status on status.upi=parcelowner.upi
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment