
select p.project_name, p.project_id, p.created_datetime, a.uprn

    into adhoc.bduk_project_checks_20260527_stg1

from projects.projects p


inner join basedata.abp a on st_intersects(p.geom,a.geom)

where (p.created_datetime <= '2025-12-16' and p.created_user != 1) or (p.created_datetime >= '2025-12-19' and p.created_user != 1)

;

create index bduk_project_checks_20260527_stg1_uprn_idx on adhoc.bduk_project_checks_20260527_stg1 (uprn);
vacuum analyse adhoc.bduk_project_checks_20260527_stg1;


with

    b1 as (select '2025-04-08'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom
           from adhoc.scci_bduk_mdu_geom_20250408 b
                    inner join basedata.abp on b.postcode = abp.postcode)
,b2 as (

select '2025-05-19'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom
from adhoc.scci_cfl_mdu_pia_distance_with_bduk_geom_20250519 b
inner join basedata.abp on b.toid = abp.cross_reference
where b.voucher like '%Y%'

)
,b3 as (
select '2025-08-07'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom
from adhoc.bduk_go_list_address_view_geom b
inner join basedata.abp on b.uprn = abp.uprn)
,b4 as (
select '2025-10-07'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom
from adhoc.bduk_go_list_fmp_work_20251003_address_geom b
inner join basedata.abp on b.uprn = abp.uprn)
,b5 as (select '2026-10-17'::date as bduk_date, abp.postcode, abp.cross_reference as toid, abp.uprn, abp.geom
        from adhoc.bduk_go_list_fmp_work_20260217_geom b
                 inner join basedata.abp on b.uprn = abp.uprn)



select p.project_name, p.project_id, p.created_datetime,
/*
count(case when b1.uprn is not null then 1 else null end) bduk_2025_04,
count(case when b2.uprn is not null then 1 else null end) bduk_2025_05,
count(case when b3.uprn is not null then 1 else null end) bduk_2025_08,
count(case when b4.uprn is not null then 1 else null end) bduk_2025_10,
count(case when b5.uprn is not null then 1 else null end) bduk_2026_02*/

b1.uprn bduk_2025_04,
b2.uprn bduk_2025_05,
b3.uprn bduk_2025_08,
b4.uprn bduk_2025_10,
b5.uprn bduk_2026_02

into adhoc.bduk_project_checks_20260527_stg2

from adhoc.bduk_project_checks_20260527_stg1 p

left join b1 on p.uprn = b1.uprn
left join b2 on  p.uprn = b2.uprn
left join b3 on  p.uprn = b3.uprn
left join b4 on  p.uprn = b4.uprn
left join b5 on p.uprn = b5.uprn

;


select
    project_id, project_name,created_datetime::date,

    count(bduk_2025_04)bduk_2025_04,
    count(bduk_2025_05)bduk_2025_05,
    count(bduk_2025_08)bduk_2025_08,
    count(bduk_2025_10)bduk_2025_10,
    count(bduk_2026_02)bduk_2026_02

    from adhoc.bduk_project_checks_20260527_stg2


group by project_id, project_name, created_datetime ;