search

bcgov / TheOrgBook

A public repository of verifiable claims about organizations. A key component of the Verifiable Organization Network.
http://von.pathfinder.gov.bc.ca
Apache License 2.0
78 stars 66 forks source link

Reporting to compare BC Reg and OrgBook databases #831

Closed ianco closed 5 years ago

ianco commented 5 years ago

Compare databases to verify OrgBook is an accurate representation of BC Reg.

ianco commented 5 years ago

summary counts of active and historical corps by type (based on current state)

select corp.corp_typ_cd, op_state.op_state_typ_cd, count(*) from bc_registries.corporation corp, bc_registries.corp_state state, bc_registries.corp_op_state op_state, bc_registries.corp_type ctype where state.corp_num = corp.corp_num and state.end_event_id is null and op_state.state_typ_cd = state.state_typ_cd and corp.corp_typ_cd = ctype.corp_typ_cd group by corp.corp_typ_cd, op_state.op_state_typ_cd;

detail listing of the above, based on current state

select corp.corp_num, corp.recognition_dts, corp.corp_typ_cd corp_typ_cd, ctype.corp_class, ctype.short_desc, state.state_typ_cd, op_state.op_state_typ_cd op_state_typ_cd from bc_registries.corporation corp, bc_registries.corp_state state, bc_registries.corp_op_state op_state, bc_registries.corp_type ctype where state.corp_num = corp.corp_num and state.end_event_id is null and op_state.state_typ_cd = state.state_typ_cd and corp.corp_typ_cd = ctype.corp_typ_cd;