Appendix G.30
G.30 Update Locations from MOE WWDB
- Tables (OAK_20160831_MASTER compatible)
- D_BOREHOLE
- D_BOREHOLE_CONSTRUCTION
- D_DATA_SOURCE
- D_GEOLOGY_FEATURE
- D_GEOLOGY_LAYER
- D_INTERVAL
- D_INTERVAL_MONITOR
- D_INTERVAL_REF_ELEV
- D_INTERVAL_TEMPORAL_2
- D_LOCATION
- D_LOCATION_ALIAS
- D_LOCATION_ELEV
- D_LOCATION_PURPOSE
- D_LOCATION_QA
- D_PUMPTEST
- D_PUMPTEST_STEP
- Tables (MOE Water Well Database, MOE_20190509)
- TblBore_Hole
- TblCasing
- TblFormation
- TblHole
- TblMethod_Construction
- TblPipe
- TblPlug
- TblPump_Test
- TblPump_Test_Detail
- TblScreen
- TblWater
- TblWWR
- Estimated Recurrence Time: After MOE WWDB import.
After the MOE WWDB (MOEDB) has been imported, a check should be made as to whether any locations currently present in the master database (ORMGPDB) have been updated in the MOEDB. We’ll determine this on a table-by-table basis. Many of the methods and queries used herein are adapted from Section G.10.
G.30.1 D_GEOLOGY_LAYER
Here we are looking at those locations that do not have any records present in D_GEOLOGY_LAYER. We cannot rely upon differences in record counts between this table and [tblFormation] (in the MOEDB) as geologic layer corrections are made in the ORMGPDB. We will create a table to be used as a reference to those LOC_IDs for which we will extract information from [tblFormation].
select
t.*
,t2.rcount_moe
--count(*) as rcount
into moe_20190509.dbo.ORMGP_20190509_upd_DGL
from
(
select
dbore.loc_id
,v.moe_well_id
,v.moe_bore_hole_id
,dgl.rcount
,dloc.loc_type_code
from
oak_20160831_master.dbo.d_borehole as dbore
inner join oak_20160831_master.dbo.d_location as dloc
on dbore.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dbore.loc_id=dlqa.loc_id
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as yc
on dbore.loc_id=yc.loc_id
left outer join
(
select
loc_id
,count(*) as rcount
from
oak_20160831_master.dbo.d_geology_layer
group by
loc_id
) as dgl
on dbore.loc_id=dgl.loc_id
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on dbore.loc_id=v.loc_id
where
dgl.rcount is null
and dloc.loc_type_code=1
and dlqa.qa_coord_confidence_code<>117
) as t
inner join
(
select
m.bore_hole_id
,count(*) as rcount_moe
from
moe_20190509.dbo.tblformation as m
where
m.mat1 is not null
and cast(m.mat1 as int)<>0
group by
bore_hole_id
) as t2
on t.moe_bore_hole_id=t2.bore_hole_id
Script: G_30_01_01_DGL_BORE_HOLE_ID.sql
Now that we have a list of locations which require updating, make sure that their material codes in the MOEDB are not specified as ‘Other’ (i.e. ‘27’); make them ‘Unknown’ (i.e. ‘0’) instead. This needs to be accomplished for each of the three material fields, only the first is shown here.
update MOE_20190509.dbo.TblFormation
set
MAT1=0
from
MOE_20190509.dbo.TblFormation as mf
inner join ORMGP_20190509_upd_DGL as od
on mf.bore_hole_id=od.moe_bore_hole_id
where
mf.MAT1=27
Script: G_30_01_02_DGL_MATS.sql
Check that the depth units are one of ‘ft’ or ‘m’ - modify others as necessary (not shown).
select
od.LOC_ID
,mf.*
from
MOE_20190509.dbo.TblFormation as mf
inner join ORMGP_20190509_upd_DGL as od
on mf.bore_hole_id=od.moe_bore_hole_id
where
not(mf.FORMATION_END_DEPTH_UOM in ('ft','m'))
order by
od.loc_id,mf.formation_top_depth
Script: G_30_01_03_DGL_UNITS.sql
We can now create the temporary file containing the new MOEDB information, formated to match D_GEOLOGY_LAYER. Note that we are including values for DATA_ID, SYS_TEMP1 and SYS_TEMP2 for subsequent tracking purposes.
select
od.LOC_ID
,cast( null as int ) as GEOL_ID
,cast( case
when moef.COLOR is null or moef.COLOR=0 then null
else moef.COLOR
end as int) as [GEOL_MAT_COLOUR_CODE]
,case
when moef.FORMATION_END_DEPTH_UOM='m' then ( dbore.bh_gnd_elev - moef.formation_top_depth )
else ( dbore.bh_gnd_elev - ( moef.formation_top_depth * 0.3048 ) )
end as GEOL_TOP_ELEV
,case
when moef.FORMATION_END_DEPTH_UOM='m' then ( dbore.bh_gnd_elev - moef.formation_end_depth )
else ( dbore.bh_gnd_elev - ( moef.formation_end_depth * 0.3048 ) )
end as GEOL_BOT_ELEV
,moef.FORMATION_TOP_DEPTH as GEOL_TOP_OUOM
,moef.FORMATION_END_DEPTH as GEOL_BOT_OUOM
,cast(moef.FORMATION_END_DEPTH_UOM as varchar(20)) as GEOL_UNIT_OUOM
,moef.LAYER as GEOL_MOE_LAYER
,cast( case
-- check if all mat fields are null
when moef.MAT1 is null and moef.MAT2 is null and moef.MAT3 is null then 0
-- if mat1 is null but mat2 is not, make mat1=mat2
when moef.MAT1 is null and moef.MAT2 is not null then moef.MAT2
-- if mat1 and mat2 is null but mat3 is not, make mat1=mat3
when moef.MAT1 is null and moef.MAT2 is null and moef.MAT3 is not null then moef.MAT3
else moef.MAT1
end as int) as GEOL_MAT1_CODE
,cast( case
-- if all mat fields are null, leave mat2 as is
-- if mat1 is null, mat2 has been moved to mat1, return null
-- we won't move mat3 to mat2
when moef.MAT1 is null and moef.MAT2 is not null then null
else moef.MAT2
end as int) as GEOL_MAT2_CODE
,cast( case
-- if mat1 and mat2 is null but mat3 is not, make mat1=mat3
when moef.MAT1 is null and moef.MAT2 is null and moef.MAT3 is not null then null
else moef.MAT3
end as int) as GEOL_MAT3_CODE
-- include some comments if we've messed about with the mat codes
,cast( case
when moef.MAT1 is null and moef.MAT2 is null and moef.MAT3 is null then 'No material, assigned unknown'
when moef.MAT1 is null and moef.MAT2 is not null then 'No mat1, assigned mat2 to mat1'
when moef.MAT1 is null and moef.MAT2 is null and moef.MAT3 is not null then 'No mat1 or mat2, assigned mat3 to mat1'
else null
end as varchar(255)) as GEOL_COMMENT
,cast( 521 as int ) as DATA_ID
,'20190531a' as SYS_TEMP1
,20190531 as SYS_TEMP2
,row_number() over (order by od.loc_id) as rkey
into O_D_GEOLOGY_LAYER
from
ORMGP_20190509_upd_DGL as od
inner join MOE_20190509.dbo.TblFormation as moef
on od.moe_bore_hole_id=moef.bore_hole_id
inner join oak_20160831_master.dbo.d_borehole as dbore
on od.loc_id=dbore.loc_id
We will now create the new GEOL_IDs necessary for import.
update moe_20190509.dbo.o_d_geology_layer
set
geol_id=t2.geol_id
from
moe_20190509.dbo.o_d_geology_layer as od
inner join
(
select
t.geol_id
,row_number() over (order by t.geol_id) as rkey
from
(
select
top 5000
v.new_id as geol_id
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select geol_id from oak_20160831_master.dbo.d_geology_layer )
) as t
) as t2
on od.rkey=t2.rkey
And, finally, insert the new MOEDB information.
insert into oak_20160831_master.dbo.d_geology_layer
(
[LOC_ID],
[GEOL_ID],
[GEOL_MAT_COLOUR_CODE],
[GEOL_TOP_ELEV],
[GEOL_BOT_ELEV],
[GEOL_TOP_OUOM],
[GEOL_BOT_OUOM],
[GEOL_UNIT_OUOM],
[GEOL_MOE_LAYER],
[GEOL_MAT1_CODE],
[GEOL_MAT2_CODE],
[GEOL_MAT3_CODE],
[GEOL_COMMENT],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
)
select
[LOC_ID],
[GEOL_ID],
[GEOL_MAT_COLOUR_CODE],
[GEOL_TOP_ELEV],
[GEOL_BOT_ELEV],
[GEOL_TOP_OUOM],
[GEOL_BOT_OUOM],
[GEOL_UNIT_OUOM],
[GEOL_MOE_LAYER],
[GEOL_MAT1_CODE],
[GEOL_MAT2_CODE],
[GEOL_MAT3_CODE],
[GEOL_COMMENT],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
from
moe_20190509.dbo.o_d_geology_layer
Script: G_30_01_04_DGL_ADD.sql
G.30.2 D_GEOLOGY_FEATURE
Here we are looking at those locations that do not have any records present in D_GEOLOGY_FEATURE. Similar to the process for D_GEOLOGY_LAYER, we shouldn’t rely upon differences in record counts between the MOEDB and ORMDB. We’ll (again) create a table to be used as a reference to those LOC_IDs for which we will extract information.
select
t.*
,t2.rcount_moe
--count(*) as rcount
into moe_20190509_final.dbo.ORMGP_20190509_upd_DGF
from
(
select
dbore.loc_id
,v.moe_well_id
,v.moe_bore_hole_id
,dgl.rcount
from
oak_20160831_master.dbo.d_borehole as dbore
inner join oak_20160831_master.dbo.d_location as dloc
on dbore.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dbore.loc_id=dlqa.loc_id
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as yc
on dbore.loc_id=yc.loc_id
left outer join
(
select
loc_id
,count(*) as rcount
from
oak_20160831_master.dbo.d_geology_feature
group by
loc_id
) as dgl
on dbore.loc_id=dgl.loc_id
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on dbore.loc_id=v.loc_id
where
dgl.rcount is null
and dloc.loc_type_code=1
and dlqa.qa_coord_confidence_code<>117
and v.moe_bore_hole_id is not null
) as t
inner join
(
select
moep.bore_hole_id
,count(*) as rcount_moe
from
MOE_20190509.dbo.TblPipe as moep
inner join MOE_20190509.[dbo].[TblWater] as moew
on moep.PIPE_ID=moew.PIPE_ID
group by
moep.bore_hole_id
) as t2
on t.moe_bore_hole_id=t2.bore_hole_id
Script: G_30_02_01_DGF_BORE_HOLE_ID.sql
We can then create the base table that will be used for import into the ORMDB.
select
y.LOC_ID
,cast(null as int) as FEATURE_ID
,case
when moew.kind is null or moew.kind=0 then null -- not specified
else moew.kind -- matches YC codes
end
as [FEATURE_CODE]
,'Water Found' as [FEATURE_DESCRIPTION]
,moew.WATER_FOUND_DEPTH as [FEATURE_TOP_OUOM]
,moew.WATER_FOUND_DEPTH_UOM as [FEATURE_UNIT_OUOM]
,cast(521 as int) as DATA_ID
,ROW_NUMBER() over (order by y.LOC_ID) as rkey
into MOE_20190509.dbo.O_D_GEOLOGY_FEATURE
from
MOE_20190509.dbo.ORMGP_20190509_upd_DGF as y
inner join MOE_20190509.dbo.TblPipe as moep
on y.moe_bore_hole_id=moep.Bore_Hole_ID
inner join MOE_20190509.[dbo].[TblWater] as moew
on moep.PIPE_ID=moew.PIPE_ID
Update the FEATURE_ID field.
update moe_20190509.dbo.o_d_geology_feature
set
feature_id=t2.feature_id
from
moe_20190509.dbo.o_d_geology_feature as od
inner join
(
select
t.feature_id
,row_number() over (order by t.feature_id) as rkey
from
(
select
top 10000
v.new_id as feature_id
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select feature_id from oak_20160831_master.dbo.d_geology_feature )
) as t
) as t2
on od.rkey=t2.rkey
And, finally, insert the records into D_GEOLOGY_FEATURE.
insert into oak_20160831_master.dbo.d_geology_feature
(
[LOC_ID],
[FEATURE_ID],
[FEATURE_CODE],
[FEATURE_DESCRIPTION],
[FEATURE_TOP_OUOM],
[FEATURE_UNIT_OUOM],
[DATA_ID],
SYS_TEMP1,
SYS_TEMP2
)
select
[LOC_ID],
[FEATURE_ID],
[FEATURE_CODE],
[FEATURE_DESCRIPTION],
[FEATURE_TOP_OUOM],
[FEATURE_UNIT_OUOM],
[DATA_ID],
cast( '20190509a' as varchar(255) ) as SYS_TEMP1,
cast( 20190509 as int ) as SYS_TEMP2
from
moe_20190509.dbo.o_d_geology_feature
Script: G_30_02_02_DGF_ADD.sql
G.30.3 D_BOREHOLE_CONSTRUCTION
We will now examine those MOE locations present in the ORMDB that do not have any borehole construction details. Determine these locations and create a temporary reference table (for speed/access purposes). Index the resultant table-fields (i.e. the identifier fields) to enhance access. Note that there may be instances of multiple BORE_HOLE_IDs present for a single LOC_ID. These seem to be decommissioned or alteration records. Refer also to Section G.10.10 for additional details.
select
dbore.loc_id
,v.moe_bore_hole_id
--into moe_20190509_final.dbo.ORMGP_20190509_base_DBHCONS
from
oak_20160831_master.dbo.d_borehole as dbore
inner join oak_20160831_master.dbo.d_location as dloc
on dbore.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dbore.loc_id=dlqa.loc_id
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as yc
on dbore.loc_id=yc.loc_id
left outer join
(
select
loc_id
,count(*) as rcount
from
oak_20160831_master.dbo.d_borehole_construction as dbc
inner join oak_20160831_master.dbo.d_borehole as dbore
on dbc.bh_id=dbore.bh_id
group by
dbore.loc_id
) as d
on dbore.loc_id=d.loc_id
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on dbore.loc_id=v.loc_id
where
d.rcount is null
and dloc.loc_type_code=1
and dlqa.qa_coord_confidence_code<>117
and v.moe_bore_hole_id is not null
group by
dbore.loc_id,v.moe_bore_hole_id
Assemble any casing information for these locations.
select
-- note that we're using BORE_HOLE_ID as a temporary BH_ID
y.LOC_ID
,y.moe_BORE_HOLE_ID as BH_ID
,case
when moec.MATERIAL is null then 10 -- unknown
when moec.MATERIAL = 1 then 21 -- steel casing
when moec.MATERIAL = 2 then 16 -- galvanized
when moec.MATERIAL = 3 then 23 -- concrete
when moec.MATERIAL = 4 then 24 -- open hole
when moec.MATERIAL = 5 then 25 -- plastic
when moec.MATERIAL = 6 then 32 -- fibreglass
end
as [CON_SUBTYPE_CODE]
,moec.DEPTH_FROM as [CON_TOP_OUOM]
,moec.DEPTH_TO as [CON_BOT_OUOM]
,moec.CASING_DEPTH_UOM as [CON_UNIT_OUOM]
,moec.CASING_DIAMETER as [CON_DIAMETER_OUOM]
,moec.CASING_DIAMETER_UOM as [CON_DIAMETER_UNIT_OUOM]
,convert(varchar(255),null) as CON_COMMENT
--into MOE_20190509_final.dbo.ORMGP_20190509_upd_DBHCONS
from
MOE_20190509_final.dbo.ORMGP_20190509_base_DBHCONS as y
inner join MOE_20190509_final.dbo.TblPipe as moep
on y.moe_BORE_HOLE_ID=moep.Bore_Hole_ID
inner join MOE_20190509_final.dbo.TblCasing as moec
on moep.PIPE_ID=moec.PIPE_ID
where
not
(
moec.DEPTH_TO is null
and moec.CASING_DEPTH_UOM is null
and moec.CASING_DIAMETER is null
and moec.CASING_DIAMETER_UOM is null
)
Add any plug information.
insert into MOE_20190509_final.dbo.ORMGP_20190509_upd_DBHCONS
(LOC_ID,BH_ID,CON_SUBTYPE_CODE,CON_TOP_OUOM,CON_BOT_OUOM,CON_UNIT_OUOM)
select
-- note that we're using BORE_HOLE_ID as a temporary BH_ID
y.LOC_ID
,y.moe_BORE_HOLE_ID as BH_ID
,31 as CON_SUBTYPE_CODE
,moep.PLUG_FROM as [CON_TOP_OUOM]
,moep.PLUG_TO as [CON_BOT_OUOM]
,moep.PLUG_DEPTH_UOM as [CON_UNIT_OUOM]
from
MOE_20190509_final.dbo.ORMGP_20190509_base_DBHCONS as y
inner join MOE_20190509_final.dbo.TblPlug as moep
on y.moe_BORE_HOLE_ID=moep.BORE_HOLE_ID
where
not
(
moep.PLUG_FROM is null
and moep.PLUG_TO is null
and moep.PLUG_DEPTH_UOM is null
)
Swap the top and bottom depths if the former is larger than the second (not shown). Create the final, formatted table including the updated DATA_ID and a set of tags for SYS_TEMP1 and SYS_TEMP2 (for reference). Pull the actual BH_ID for the location from the ORMDB. Note that we’re introducing a counter for later assignment of random identifiers.
SELECT
d.[BH_ID]
,[CON_SUBTYPE_CODE]
,[CON_TOP_OUOM]
,[CON_BOT_OUOM]
,[CON_UNIT_OUOM]
,[CON_DIAMETER_OUOM]
,[CON_DIAMETER_UNIT_OUOM]
,[CON_COMMENT]
,cast( 521 as int ) as DATA_ID
,cast(null as int) as SYS_RECORD_ID
,cast( '20190509a' as varchar(255) ) as SYS_TEMP1
,cast( 20190509 as int ) as SYS_TEMP2
,ROW_NUMBER() over (order by y.BH_ID) as rkey
,y.bh_id as moe_bore_hole_id
--into moe_20190509_final.dbo.O_D_BOREHOLE_CONSTRUCTION
FROM
MOE_20190509_final.[dbo].ORMGP_20190509_upd_DBHCONS as y
inner join oak_20160831_master.dbo.d_borehole as d
on y.loc_id=d.loc_id
Script: G_30_03_01_DBHCONS.sql
Update the SYS_RECORD_ID and insert the final information into D_BOREHOLE_CONSTRUCTION in the ORMDB (not shown).
G.30.4 Depths
In order to process the screen information (as found in the next section, G.30.5) for some of the screen types, we’ll need to determine the depth of the borehole in question. Similar to the methodology in Section G.10, we’ll look at tables for each of: formation; water found; borehole construction; MOE assigned. From these, we’ll determine the maximum depth.
First, we’ll assemble all those locations in the ORMDB that currently have no depth associated with them as found in D_BOREHOLE. This is irregardless of whether they have updated depths in the MOEDB.
select
dbore.loc_id
,v.moe_bore_hole_id
,cast(null as float) as fm_max_depth_m
,cast(null as float) as dgf_max_depth_m
,cast(null as float) as casing_max_depth_m
,cast(null as float) as dbc_max_depth_m
,cast(null as float) as moe_max_depth_m
,cast(null as float) as max_depth_m
into moe_20200721.dbo.ORMGP_20200721_upd_DEPTH
from
oak_20160831_master.dbo.d_borehole as dbore
inner join oak_20160831_master.dbo.d_location as dloc
on dbore.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dbore.loc_id=dlqa.loc_id
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as yc
on dbore.loc_id=yc.loc_id
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on dbore.loc_id=v.loc_id
where
dbore.bh_bottom_ouom is null
and dloc.loc_type_code=1
and dlqa.qa_coord_confidence_code<>117
and v.moe_bore_hole_id is not null
group by
dbore.loc_id,v.moe_bore_hole_id
Note that we’re creating a series of NULL fields which we will subsequently populate. Now, determine the maximum formation depth.
update moe_20200721.dbo.ormgp_20200721_upd_depth
set
fm_max_depth_m= t2.fm_max_depth_m
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm2
inner join
(
select
t.moe_bore_hole_id
,max(t.fm_end_depth) as fm_max_depth_m
from
(
select
orm.moe_bore_hole_id
,case
when moe.[FORMATION_END_DEPTH_UOM]='ft' then moe.[FORMATION_END_DEPTH]*0.3048
else moe.[FORMATION_END_DEPTH]
end as fm_end_depth
from
moe_20200721.dbo.ORMGP_20200721_upd_DEPTH as orm
inner join moe_20200721.dbo.tblformation as moe
on orm.moe_bore_hole_id=moe.bore_hole_id
) as t
group by
t.moe_bore_hole_id
) as t2
on orm2.moe_bore_hole_id=t2.moe_bore_hole_id
Check the casing for inverted top- and bottom-depths.
update moe_20200721.dbo.tblcasing
set
depth_from=depth_to
,depth_to=depth_from
where
depth_from>depth_to
Then process the various tables for construction details/depths.
update moe_20200721.dbo.ormgp_20200721_upd_depth
set
dbc_max_depth_m= t2.dbc_max_depth_m
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join
(
select
t.moe_bore_hole_id
,max(t.dbc_depth) as dbc_max_depth_m
from
(
select
orm.moe_bore_hole_id
,case
when mcase.casing_depth_uom = 'ft' then mcase.depth_to * 0.3048
else mcase.depth_to
end as dbc_depth
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join moe_20200721.dbo.tblbore_hole as mbore
on orm.moe_bore_hole_id=mbore.bore_hole_id
inner join moe_20200721.dbo.tblcasing as mcase
on mbore.well_id=mcase.well_id
union all
select
orm.moe_bore_hole_id
,case
when mplug.plug_depth_uom = 'ft' then mplug.plug_to * 0.3048
else mplug.plug_to
end as dbc_depth
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join moe_20200721.dbo.tblplug as mplug
on orm.moe_bore_hole_id=mplug.bore_hole_id
union all
select
orm.moe_bore_hole_id
,case
when mscr.scrn_depth_uom = 'ft' then mscr.scrn_end_depth * 0.3048
else mscr.scrn_end_depth
end as dbc_depth
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join moe_20200721.dbo.tblbore_hole as mbore
on orm.moe_bore_hole_id=mbore.bore_hole_id
inner join moe_20200721.dbo.tblscreen as mscr
on mbore.well_id=mscr.well_id
union all
select
orm.moe_bore_hole_id
,case
when mpump.levels_uom = 'ft' then mpump.recom_depth * 0.3048
else mpump.recom_depth
end as dbc_depth
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join moe_20200721.dbo.tblbore_hole as mbore
on orm.moe_bore_hole_id=mbore.bore_hole_id
inner join moe_20200721.dbo.tblpump_test as mpump
on mbore.well_id=mpump.well_id
) as t
group by
t.moe_bore_hole_id
) as t2
on orm.moe_bore_hole_id=t2.moe_bore_hole_id
We’ll repeat part of this query in order to process the casing depths separately.
update moe_20200721.dbo.ormgp_20200721_upd_depth
set
casing_max_depth_m= t2.casing_max_depth_m
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join
(
select
t.moe_bore_hole_id
,max(t.dbc_depth) as casing_max_depth_m
from
(
select
orm.moe_bore_hole_id
,case
when mcase.casing_depth_uom = 'ft' then mcase.depth_to * 0.3048
else mcase.depth_to
end as dbc_depth
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join moe_20200721.dbo.tblbore_hole as mbore
on orm.moe_bore_hole_id=mbore.bore_hole_id
inner join moe_20200721.dbo.tblcasing as mcase
on mbore.well_id=mcase.well_id
) as t
group by
t.moe_bore_hole_id
) as t2
on orm.moe_bore_hole_id=t2.moe_bore_hole_id
Populate the geology features (i.e. water found) depths.
update moe_20200721.dbo.ormgp_20200721_upd_depth
set
dgf_max_depth_m= t2.dgf_max_depth_m
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join
(
select
t.moe_bore_hole_id
,max(t.dgf_depth) as dgf_max_depth_m
from
(
select
orm.moe_bore_hole_id
,case
when mwater.water_found_depth_uom = 'ft' then mwater.water_found_depth * 0.3048
else mwater.water_found_depth
end as dgf_depth
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join moe_20200721.dbo.tblbore_hole as mbore
on orm.moe_bore_hole_id=mbore.bore_hole_id
inner join moe_20200721.dbo.tblwater as mwater
on mbore.well_id=mwater.well_id
) as t
group by
t.moe_bore_hole_id
) as t2
on orm.moe_bore_hole_id=t2.moe_bore_hole_id
And then, finally, the maximum depth as specified by the MOE.
update moe_20190509_final.dbo.ormgp_20190509_upd_depth
set
moe_max_depth_m= t2.moe_max_depth_m
from
moe_20190509_final.dbo.ormgp_20190509_upd_depth as orm
inner join
(
select
t.moe_bore_hole_id
,max(t.moe_depth) as moe_max_depth_m
from
(
select
orm.moe_bore_hole_id
,case
when mhole.hole_depth_uom = 'ft' then mhole.depth_to * 0.3048
else mhole.depth_to
end as moe_depth
from
moe_20190509_final.dbo.ormgp_20190509_upd_depth as orm
inner join moe_20190509_final.dbo.tblhole as mhole
on orm.moe_bore_hole_id=mhole.bore_hole_id
) as t
group by
t.moe_bore_hole_id
) as t2
on orm.moe_bore_hole_id=t2.moe_bore_hole_id
We can now determine the maximum depth based upon the values of these four fields.
update moe_20200721.dbo.ormgp_20200721_upd_depth
set
max_depth_m= t.max_depth_m
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
inner join
(
select
orm.moe_bore_hole_id
,(
select max(v) from
(
values (fm_max_depth_m),(dgf_max_depth_m),(dbc_max_depth_m),(moe_max_depth_m)
) as value(v)
) as max_depth_m
from
moe_20200721.dbo.ormgp_20200721_upd_depth as orm
) as t
on orm.moe_bore_hole_id=t.moe_bore_hole_id
Script: G_30_04_01_DEPTHS.sql
This can now be used as a reference (value) in the following section.
G.30.5 D_INTERVAL and Associated
We will now examine those MOE locations present in the ORMDB that do not currently have screens identified. Similar to Section G.10, the methodology will be adapted based upon the screen type (e.g. reported, open hole, etc…) as each of these require slightly differing steps.
We’ll determine those locations already present in the ORMDB and create a temporary table - this is for speed purposes. The alternative, on-the-fly analysis, takes a significant time. Note that the resulting table will subsequently be indexed by hand (i.e. the identifier fields), again for speed purposes. We’re including locations/screens that are already present in the ORMDB D_INTERVAL table which have a screen type of ‘Screen Information Omitted’ (INT_TYPE_CODE 28; these were imported from an earlier MOEDB but did not have screen information at that time). A non-null INT_ID identifies these locations.
select
t.*
,dint.int_id
into moe_20200721.dbo.ORMGP_20200721_base_DINT
from
(
select
dbore.loc_id
,v.moe_bore_hole_id
from
oak_20160831_master.dbo.d_borehole as dbore
inner join oak_20160831_master.dbo.d_location as dloc
on dbore.loc_id=dloc.loc_id
inner join oak_20160831_master.dbo.d_location_qa as dlqa
on dbore.loc_id=dlqa.loc_id
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as yc
on dbore.loc_id=yc.loc_id
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on dbore.loc_id=v.loc_id
left outer join oak_20160831_master.dbo.d_interval as dint
on dbore.loc_id=dint.loc_id
where
(dint.int_id is null or dint.int_type_code=28)
and dloc.loc_type_code=1
and dlqa.qa_coord_confidence_code<>117
and v.moe_bore_hole_id is not null
group by
dbore.loc_id,v.moe_bore_hole_id
) as t
left outer join oak_20160831_master.dbo.d_interval as dint
on t.loc_id=dint.loc_id
Now that we have a listing of the locations and their associated BORE_HOLE_ID, determine which of these actually has a reported screen. We’ll create the initial compatible table from which we’ll generate D_INTERVAL and D_INTERVAL_MONITOR.
select
d.LOC_ID
,d.INT_ID
,d.moe_bore_hole_id as TMP_INT_ID
,18 as tmp_INT_TYPE_CODE
,moeslot.CONV_YC_SLOT as MON_SCREEN_SLOT
,moes.SCRN_MATERIAL as MON_SCREEN_MATERIAL
,moes.SCRN_DIAMETER as MON_DIAMETER_OUOM
,moes.SCRN_DIAMETER_UOM as MON_DIAMETER_UNIT_OUOM
,moes.SCRN_TOP_DEPTH as MON_TOP_OUOM
,moes.SCRN_END_DEPTH as MON_BOT_OUOM
,moes.SCRN_DEPTH_UOM as MON_UNIT_OUOM
,cast(null as varchar(255)) as MON_COMMENT
into moe_20200721.dbo.ORMGP_20200721_upd_DINTMON
from
moe_20200721.dbo.ORMGP_20200721_base_DINT as d
inner join
MOE_20200721.dbo.TblPipe as moep
on d.moe_bore_hole_id=moep.Bore_Hole_ID
inner join
MOE_20200721.dbo.TblScreen as moes
on moep.PIPE_ID=moes.PIPE_ID
left outer join
MOE_20200721.dbo.YC_20200721_MOE_SLOT as moeslot
on moes.Slot=moeslot.MOE_SLOT
where
moes.SCRN_TOP_DEPTH is not null
or moes.SCRN_END_DEPTH is not null
Script: G_30_05_01_DINT_BORE_HOLE_ID.sql
We’ll now check whether we have a non-reported screen, in this case an OPEN_HOLE (as declared in the MOEDB; bottom-of-casing to bottom-of-hole). Add these to the temporary table ORMGP_20190509_upd_DINTMON.
insert into moe_20200721.dbo.ormgp_20200721_upd_dintmon
(
loc_id, tmp_int_id, tmp_int_type_code, mon_top_ouom, mon_bot_ouom, mon_unit_ouom, mon_comment
)
select
orm.LOC_ID
,orm.moe_bore_hole_id as tmp_INT_ID
,cast( 21 as int ) as tmp_INT_TYPE_CODE
,od.casing_max_depth_m as MON_TOP_OUOM
,od.max_depth_m as MON_BOT_OUOM
,cast( 'm' as varchar(50) ) as MON_UNIT_OUOM
,cast( 'open hole; bottom-of-casing to bottom-of-hole' as varchar(255) ) as MON_CMMENT
from
moe_20200721.dbo.ormgp_20200721_base_dint as orm
inner join moe_20200721.dbo.ormgp_20200721_upd_depth as od
on orm.moe_bore_hole_id=od.moe_bore_hole_id
inner join moe_20200721.dbo.tblbore_hole as moebh
on orm.moe_bore_hole_id=moebh.bore_hole_id
where
moebh.OPEN_HOLE='Y'
and orm.moe_bore_hole_id not in
( select tmp_int_id from moe_20200721.dbo.ormgp_20200721_upd_dintmon )
If there are bedrock boreholes (and they haven’t previously been assigned a screen), we can make the assumption that the screen is from the bottom-of-casing (penetrating to bedrock) to the bottom of the hole. Add these (note that this query can take some time to complete).
insert into moe_20200721.dbo.ormgp_20200721_upd_dintmon
(
loc_id, int_id, tmp_INT_ID, tmp_int_type_code, mon_top_ouom, mon_bot_ouom, mon_unit_ouom, mon_comment
)
select
orm.LOC_ID
,orm.INT_ID
,orm.moe_bore_hole_id as tmp_INT_ID
,cast( 22 as int ) as tmp_INT_TYPE_CODE
,t2.geol_top_m as MON_TOP_OUOM
,od.max_depth_m as MON_BOT_OUOM
,'m' as MON_UNIT_OUOM
,cast( 'bedrock, no valid casing; open hole, top-of-bedrock to bottom-of-hole' as varchar(255) ) as MON_COMMENT
from
moe_20200721.dbo.ormgp_20200721_base_dint as orm
inner join moe_20200721.dbo.ormgp_20200721_upd_depth as od
on orm.moe_bore_hole_id=od.moe_bore_hole_id
inner join
(
select
t.moe_bore_hole_id
,min(t.geol_top_m) as geol_top_m
,max(t.geol_bot_m) as geol_bot_m
from
(
select
orm.moe_bore_hole_id
,case
when mform.formation_end_depth_uom = 'ft' then mform.formation_top_depth * 0.3048
else mform.formation_top_depth
end as geol_top_m
,case
when mform.formation_end_depth_uom = 'ft' then mform.formation_end_depth * 0.3048
else mform.formation_end_depth
end as geol_bot_m
from
moe_20200721.dbo.ormgp_20200721_base_dint as orm
inner join moe_20200721.dbo.tblformation as mform
on orm.moe_bore_hole_id=mform.bore_hole_id
inner join oak_20160831_master.dbo.r_geol_mat1_code as rgmc
on cast( mform.mat1 as int )=rgmc.geol_mat1_code
where
rgmc.geol_mat1_rock=1
) as t
group by
t.moe_bore_hole_id
) as t2
on orm.moe_bore_hole_id=t2.moe_bore_hole_id
where
orm.moe_bore_hole_id not in
( select tmp_int_id from moe_20200721.dbo.ormgp_20200721_upd_dintmon )
The remainder of the (possible) screens should be considered overburden boreholes. As such, we’ll assume a ‘0.3m’ screen above the bottom-of-hole. Only those with valid depths are considered.
insert into moe_20200721.dbo.ormgp_20200721_upd_dintmon
(
loc_id, int_id, tmp_int_id, tmp_int_type_code, mon_top_ouom, mon_bot_ouom, mon_unit_ouom, mon_comment
)
select
orm.LOC_ID
,orm.INT_ID
,orm.moe_bore_hole_id as tmp_INT_ID
,19 as tmp_INT_TYPE_CODE
,( od.max_depth_m - 0.3 ) as MON_TOP_OUOM
,od.max_depth_m as MON_BOT_OUOM
,cast( 'm' as varchar(50) ) as MON_UNIT_OUOM
,cast( 'overburden; assumed screen, 0.3m above bottom-of-hole' as varchar(255) ) as MON_COMMENT
from
moe_20200721.dbo.ormgp_20200721_base_dint as orm
inner join moe_20200721.dbo.ormgp_20200721_upd_depth as od
on orm.moe_bore_hole_id=od.moe_bore_hole_id
where
od.max_depth_m is not null
and orm.moe_bore_hole_id not in
( select tmp_int_id from moe_20200721.dbo.ormgp_20200721_upd_dintmon )
Script: G_30_05_02_DINT_OTHER.sql
Make sure the ORMGP_20190509_upd_DINTMON has an indexed key (this may not be necessary - try running the ‘select’ statement before creating a new table to test the run time). We can now create the D_INTERVAL and D_INTERVAL_MONITOR tables, the latter table should be assembled first (update the DATA_ID, SYS_TEMP1 and SYS_TEMP2 fields).
select
dim.LOC_ID
,dim.tmp_INT_ID
,dim.tmp_INT_TYPE_CODE
,dim.INT_ID
,dim.MON_SCREEN_SLOT
,cast(moeccm.DES as varchar(255)) as MON_SCREEN_MATERIAL
,dim.MON_DIAMETER_OUOM
,dim.MON_DIAMETER_UNIT_OUOM
,dim.MON_TOP_OUOM
,dim.MON_BOT_OUOM
,dim.MON_UNIT_OUOM
,dim.MON_COMMENT
,cast(null as int) as MON_FLOWING
,cast( null as int ) as MON_ID
,cast( 522 as int ) as DATA_ID
,cast( '20200810a' as varchar(255) ) as SYS_TEMP1
,cast( 20200810 as int ) as SYS_TEMP2
,row_number() over (order by dim.loc_id) as rkey
into moe_20200721.dbo.O_D_INTERVAL_MONITOR
from
MOE_20200721.dbo.ORMGP_20200721_upd_DINTMON as dim
left outer join MOE_20200721.dbo._code_casing_material as moeccm
on dim.MON_SCREEN_MATERIAL=moeccm.CODE
Assemble the D_INTERVAL table. Note that we’re using an assigned date of ‘1867-07-01’ for those missing INT_START_DATEs. We’re also using using a ‘group by’ statement to avoid creating duplicate interval/screen records.
select
t.LOC_ID
,t.INT_ID
,t.tmp_INT_ID
,cast( v.moe_well_id as varchar(255) ) as INT_NAME
,cast( t.tmp_int_id as varchar(255) ) as INT_NAME_ALT1
,t.tmp_INT_TYPE_CODE as INT_TYPE_CODE
,case
when moe.date_completed is not null then moe.date_completed
else cast('1867-07-01' as datetime)
end as INT_START_DATE
,cast(1 as int) as INT_CONFIDENTIALITY_CODE
,cast(1 as int) as INT_ACTIVE
,cast( case
when t.int_id is not null then 1
else null
end as int) as int_exists
,cast(522 as int) as [DATA_ID]
,cast( '20200721a' as varchar(255) ) as SYS_TEMP1
,cast( 20200721 as int ) as SYS_TEMP2
,row_number() over (order by t.int_id) as rkey
into moe_20200721.dbo.O_D_INTERVAL
from
(
select
dim.LOC_ID
,dim.INT_ID
,dim.tmp_INT_ID
,dim.tmp_INT_TYPE_CODE
from
moe_20200721.dbo.O_D_INTERVAL_MONITOR as dim
group by
dim.loc_id,dim.tmp_int_id,dim.int_id,dim.tmp_int_type_code
) as t
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on t.tmp_int_id=v.moe_bore_hole_id
inner join moe_20200721.dbo.tblbore_hole as moe
on t.tmp_int_id=moe.bore_hole_id
Perform a check to make sure there are not any duplicate records in D_INTERVAL. Correct/delete those rows (not shown).
Script: G_30_05_03_DINT_DIM.sql
We can now update those INT_IDs that already exist in the ORMDB as well as add any new intervals. We’ll update the INT_IDs first.
update oak_20160831_master.dbo.d_interval
set
int_name= d.int_name
,int_name_alt1= d.int_name_alt1
,int_type_code= d.int_type_code
,int_start_date= d.int_start_date
,int_confidentiality_code= d.int_confidentiality_code
,int_active= d.int_active
,data_id= d.data_id
,sys_temp1= d.sys_temp1
,sys_temp2= d.sys_temp2
from
oak_20160831_master.dbo.d_interval as dint
inner join moe_20210119.dbo.o_d_interval as d
on dint.int_id=d.int_id
where
d.int_exists=1
and
(
( d.int_start_date <> dint.int_start_date )
or
( d.int_type_code <> dint.int_type_code )
or
( d.int_name_alt1 <> dint.int_name_alt1 )
)
We’ll need to create some random INT_IDs for the remainder (i.e. these intervals are not already present in the ORMDB).
update moe_20200721.dbo.o_d_interval
set
int_id= t2.int_id
from
moe_20200721.dbo.o_d_interval as dint
inner join
(
select
t.int_id
,row_number() over (order by t.int_id) as rkey
from
(
select
top 5200
v.new_id as int_id
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select int_id from oak_20160831_master.dbo.d_interval )
) as t
) as t2
on dint.rkey=t2.rkey
where
dint.int_exists is null
Insert them into D_INTERVAL.
insert into oak_20160831_master.dbo.d_interval
(
[LOC_ID],
[INT_ID],
[INT_NAME],
[INT_NAME_ALT1],
[INT_TYPE_CODE],
[INT_START_DATE],
[INT_CONFIDENTIALITY_CODE],
[INT_ACTIVE],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
)
select
[LOC_ID],
[INT_ID],
[INT_NAME],
[INT_NAME_ALT1],
[INT_TYPE_CODE],
[INT_START_DATE],
[INT_CONFIDENTIALITY_CODE],
[INT_ACTIVE],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
from
moe_20200721.dbo.o_d_interval as d
where
d.int_exists is null
Now that the intervals are updated or created, we can update O_D_INTERVAL_MONITOR and then insert into D_INTERVAL_MONITOR. First we’ll associate the INT_ID.
update moe_20200721.dbo.o_d_interval_monitor
set
int_id= dint.int_id
from
moe_20200721.dbo.o_d_interval_monitor as dim
inner join moe_20200721.dbo.o_d_interval as dint
on dim.tmp_int_id=dint.tmp_int_id
where
dint.int_exists is null
And then populate the MON_ID.
update moe_20200721.dbo.o_d_interval_monitor
set
mon_id= t2.mon_id
from
moe_20200721.dbo.o_d_interval_monitor as d
inner join
(
select
t.mon_id
,row_number() over (order by t.mon_id) as rkey
from
(
select
top 5200
v.new_id as mon_id
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select mon_id from oak_20160831_master.dbo.d_interval_monitor )
) as t
) as t2
on d.id=t2.rkey
And, finally, add the new records into D_INTERVAL_MONITOR.
insert into oak_20160831_master.dbo.d_interval_monitor
(
[INT_ID],
[MON_SCREEN_SLOT],
[MON_SCREEN_MATERIAL],
[MON_DIAMETER_OUOM],
[MON_DIAMETER_UNIT_OUOM],
[MON_TOP_OUOM],
[MON_BOT_OUOM],
[MON_UNIT_OUOM],
[MON_COMMENT],
[MON_FLOWING],
[MON_ID],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
)
select
[INT_ID],
[MON_SCREEN_SLOT],
[MON_SCREEN_MATERIAL],
[MON_DIAMETER_OUOM],
[MON_DIAMETER_UNIT_OUOM],
[MON_TOP_OUOM],
[MON_BOT_OUOM],
[MON_UNIT_OUOM],
[MON_COMMENT],
[MON_FLOWING],
[MON_ID],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
from
moe_20200721.dbo.o_d_interval_monitor as odim
where
odim.int_id not in
( select distinct(int_id) from oak_20160831_master.dbo.d_interval_monitor )
Script: G_30_05_04_DINT_DIM_ADD.sql
Note that the MON_FLOWING field may need to be updated in a future step.
G.30.6 D_INTERVAL_REF_ELEV
For these new or updated intervals, we’ll check and correct (or add to) the intervals in the D_INTERVAL_REF_ELEV table. We should first make sure that an elevation has been assigned in the D_BOREHOLE table (as we’re actually calculating a reference elevation, herein, we’ll need the ground elevation at the location). Note that we’re using the temporary D_INTERVAL table to determine which intervals need to be updated.
-- create view V_MOE_20200721_UPD_ELEVS as
select
dbore.loc_id
,dloc.loc_coord_easting as x
,dloc.loc_coord_northing as y
from
moe_20200721.dbo.o_d_interval as dint
inner join oak_20160831_master.dbo.d_borehole as dbore
on dint.loc_id=dbore.loc_id
inner join oak_20160831_master.dbo.d_location as dloc
on dbore.loc_id=dloc.loc_id
left outer join oak_20160831_master.dbo.d_interval_ref_elev as dire
on dint.int_id=dire.int_id
where
( dire.int_id is null or dire.ref_elev is null )
and dbore.bh_gnd_elev is null
We’ll create a view from this, determine the elevations and populate the D_BOREHOLE table (not shown). Now we can create the temporary D_INTERVAL_REF_ELEV table.
select
dint.INT_ID
,dire.SYS_RECORD_ID
,dint.int_start_date as REF_ELEV_START_DATE
,( dbore.bh_gnd_elev + 0.75 ) as REF_ELEV
,( dbore.bh_gnd_elev + 0.75 ) as REF_ELEV_OUOM
,cast('masl' as varchar(50)) as REF_ELEV_UNIT_OUOM
,0.75 as REF_STICK_UP
,cast('0.75' as varchar(50)) as REF_POINT
,cast( 522 as int ) as DATA_ID
,cast( '20200810a' as varchar(255) ) as SYS_TEMP1
,cast( 20200810 as int ) as SYS_TEMP2
,case
when dire.int_id is not null then 1
else null
end as int_exists
,row_number() over (order by dint.int_id) as rkey
into moe_20200721.dbo.O_D_INTERVAL_REF_ELEV
from
moe_20200721.dbo.o_d_interval as dint
inner join oak_20160831_master.dbo.d_borehole as dbore
on dint.loc_id=dbore.loc_id
left outer join oak_20160831_master.dbo.d_interval_ref_elev as dire
on dint.int_id=dire.int_id
where
dire.int_id is null
or dire.ref_elev is null
And then populate the NULL SYS_RECORD_IDs.
update moe_20200721.dbo.o_d_interval_ref_elev
set
sys_record_id= t2.sri
from
moe_20200721.dbo.o_d_interval_ref_elev as dire
inner join
(
select
t.sri
,row_number() over (order by t.sri) as rkey
from
(
select
top 1500
v.new_id as sri
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select sys_record_id from oak_20160831_master.dbo.d_interval_ref_elev )
) as t
) as t2
on dire.rkey=t2.rkey
where
dire.int_exists is null
We can update the fields for those INT_IDs (actually through SYS_RECORD_ID) that already exist in the ORMDB.
update oak_20160831_master.dbo.d_interval_ref_elev
set
ref_elev_start_date= m.ref_elev_start_date
,ref_elev= m.ref_elev
,ref_elev_ouom= m.ref_elev_ouom
,ref_elev_unit_ouom= m.ref_elev_unit_ouom
,ref_stick_up= m.ref_stick_up
,ref_point= m.ref_point
,data_id= m.data_id
,sys_temp1= m.sys_temp1
,sys_temp2= m.sys_temp2
from
oak_20160831_master.dbo.d_interval_ref_elev as dire
inner join moe_20200721.dbo.o_d_interval_ref_elev as m
on dire.sys_record_id=m.sys_record_id
where
m.int_exists is not null
And we can insert the remainder.
insert into oak_20160831_master.dbo.d_interval_ref_elev
(
[INT_ID],
[SYS_RECORD_ID],
[REF_ELEV_START_DATE],
[REF_ELEV],
[REF_ELEV_OUOM],
[REF_ELEV_UNIT_OUOM],
[REF_STICK_UP],
[REF_POINT],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
)
select
[INT_ID],
[SYS_RECORD_ID],
[REF_ELEV_START_DATE],
[REF_ELEV],
[REF_ELEV_OUOM],
[REF_ELEV_UNIT_OUOM],
[REF_STICK_UP],
[REF_POINT],
[DATA_ID],
[SYS_TEMP1],
[SYS_TEMP2]
from
moe_20200721.dbo.o_d_interval_ref_elev as m
where
m.int_exists is null
Script: G_30_06_01_DIRE.sql
G.30.7 D_INTERVAL_TEMPORAL_2 - Static Waterlevels
We will not rely upon the interval list generated in Section G.30.5, previously. This was tested (i.e. limited to the interval list) and only returns a subset of the missing values. Instead, we’ll build the values to be incorporated from the complete list of MOEDB related boreholes.
select
dint.int_id
,v.moe_bore_hole_id
into moe_20200721.dbo.ORMGP_20200721_upd_DIT2_628
from
oak_20160831_master.dbo.v_sys_moe_locations as v
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as y
on v.loc_id=y.loc_id
inner join oak_20160831_master.dbo.d_interval as dint
on v.loc_id=dint.loc_id
left outer join
(
select
int_id
from
oak_20160831_master.dbo.d_interval_temporal_2
where
rd_type_code=0
and rd_name_code=628
group by
int_id
) as d2
on dint.int_id=d2.int_id
inner join moe_20200721.dbo.tblpipe as moetp
on v.moe_bore_hole_id=moetp.bore_hole_id
inner join moe_20200721.dbo.tblpump_test as moept
on moetp.pipe_id=moept.pipe_id
where
d2.int_id is null
and moept.static_lev is not null
group by
dint.int_id,v.moe_bore_hole_id
We can now use this list to generate the D_INTERVAL_TEMPORAL_2 compatible table containing the static water levels.
select
dbore.LOC_ID
,dint.INT_ID
,cast(0 as int) as RD_TYPE_CODE
,cast(628 as int) as RD_NAME_CODE
,case
when dint.int_start_date is null then cast( '1867-07-01' as datetime )
else dint.INT_START_DATE
end as [RD_DATE]
,cast(
case
when moept.LEVELS_UOM like 'ft' then dbore.bh_gnd_elev-(0.3048*moept.Static_lev)
else dbore.bh_gnd_elev-moept.Static_lev
end
as float
) as[RD_VALUE]
,cast(6 as int) as UNIT_CODE
,cast('Water Level - Manual - Static' as varchar(255)) as RD_NAME_OUOM
,cast(moept.Static_lev as float) as [RD_VALUE_OUOM]
,cast(moept.LEVELS_UOM as varchar(50)) as [RD_UNIT_OUOM]
,cast(1 as int) as [REC_STATUS_CODE]
,cast(null as varchar(255)) as RD_COMMENT
,cast(522 as int) as DATA_ID
,cast(null as int) as SYS_RECORD_ID
,ROW_NUMBER() over (order by dint.INT_ID) as rkey
into moe_20200721.dbo.O_D_INTERVAL_TEMPORAL_2_628
from
moe_20200721.dbo.ormgp_20200721_upd_dit2_628 as d2
inner join moe_20200721.dbo.tblpipe as moetp
on d2.moe_bore_hole_id=moetp.bore_hole_id
inner join moe_20200721.dbo.tblpump_test as moept
on moetp.pipe_id=moept.pipe_id
inner join oak_20160831_master.dbo.d_interval as dint
on d2.int_id=dint.int_id
inner join oak_20160831_master.dbo.d_borehole as dbore
on dint.loc_id=dbore.loc_id
Check this table (i.e. O_D_INTERVAL_TEMPORAL_2_628) for NULL RD_VALUE fields - these do not have a valid BH_GND_ELEV value in D_BOREHOLE. Fix this issue (not shown) then re-create the table (using the above script). We will now populate the SYS_RECORD_ID field.
update moe_20200721.dbo.o_d_interval_temporal_2_628
set
sys_record_id= t2.sri
from
moe_20200721.dbo.o_d_interval_temporal_2_628 as d
inner join
(
select
t.sri
,row_number() over (order by t.sri) as rkey
from
(
select
top 3000
v.new_id as sri
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select sys_record_id from oak_20160831_master.dbo.d_interval_temporal_2 )
) as t
) as t2
on d.rkey=t2.rkey
And insert the values into D_INTERVAL_TEMPORAL_2.
insert into oak_20160831_master.dbo.d_interval_temporal_2
(
[INT_ID],
[RD_TYPE_CODE],
[RD_NAME_CODE],
[RD_DATE],
[RD_VALUE],
[UNIT_CODE],
[RD_NAME_OUOM],
[RD_VALUE_OUOM],
[RD_UNIT_OUOM],
[REC_STATUS_CODE],
[RD_COMMENT],
[DATA_ID],
SYS_TEMP1,
SYS_TEMP2,
[SYS_RECORD_ID]
)
select
[INT_ID],
[RD_TYPE_CODE],
[RD_NAME_CODE],
[RD_DATE],
[RD_VALUE],
[UNIT_CODE],
[RD_NAME_OUOM],
[RD_VALUE_OUOM],
[RD_UNIT_OUOM],
[REC_STATUS_CODE],
[RD_COMMENT],
[DATA_ID],
cast( '20200810b' as varchar(255) ) as SYS_TEMP1,
cast( 20200810 as int ) as SYS_TEMP2,
[SYS_RECORD_ID]
from
moe_20200721.dbo.o_d_interval_temporal_2_628
Script: G_30_07_01_DIT2_628.sql
G.30.8 D_PUMPTEST and related
We should first check and update the status of flowing wells in the ORMDB as tagged in the MOEDB (this information is found in the ‘tblpump_test’ table in the latter).
update oak_20160831_master.dbo.d_interval_monitor
set
mon_flowing= 1
,data_id= case
when dim.data_id is null then 522
else dim.data_id
end
,mon_comment= case
when dim.data_id is null then 'MON_FLOWING update from DATA_ID 522'
else dim.mon_comment + '; MON_FLOWING update from DATA_ID 522'
end
,sys_temp1= cast( '20200810c' as varchar(255) )
,sys_temp2= cast( 20200810 as int )
from
oak_20160831_master.dbo.d_interval_monitor as dim
inner join oak_20160831_master.dbo.d_interval as dint
on dim.int_id=dint.int_id
inner join oak_20160831_master.dbo.v_sys_moe_locations as v
on dint.loc_id=v.loc_id
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as y
on v.loc_id=y.loc_id
inner join moe_20200721.dbo.tblpipe as moetp
on v.moe_bore_hole_id=moetp.bore_hole_id
inner join moe_20200721.dbo.tblpump_test as moept
on moetp.pipe_id=moept.pipe_id
where
moept.FLOWING like 'Y'
and dim.mon_flowing is null
and dint.int_type_code in
( select int_type_code from oak_20160831_master.dbo.v_sys_int_type_code_screen )
We can now assemble those INT_IDs (and related) that require pumping information to be added.
select
dint.int_id
,v.moe_well_id
,v.moe_bore_hole_id
,moept.pipe_id
into moe_20200721.dbo.ORMGP_20200721_upd_DPUMP
from
oak_20160831_master.dbo.v_sys_moe_locations as v
inner join oak_20160831_master.dbo.v_sys_agency_ypdt as y
on v.loc_id=y.loc_id
inner join oak_20160831_master.dbo.d_interval as dint
on v.loc_id=dint.loc_id
left outer join
(
select
int_id
from
oak_20160831_master.dbo.d_pumptest
group by
int_id
) as d
on dint.int_id=d.int_id
inner join moe_20200721.dbo.tblpipe as moetp
on v.moe_bore_hole_id=moetp.bore_hole_id
inner join moe_20200721.dbo.tblpump_test as moept
on moetp.pipe_id=moept.pipe_id
where
d.int_id is null
-- there are non-screen intervals, make sure not to include them
and dint.int_type_code in ( select int_type_code from oak_20160831_master.dbo.v_sys_int_type_code_screen )
and (
moept.Recom_depth is not null
or moept.Recom_rate is not null
or moept.Flowing_rate is not null
or moept.PUMP_TEST_ID in
(select PUMP_TEST_ID from moe_20200721.dbo.tblpump_test_detail)
)
And then assemble the temporary O_D_PUMPTEST table.
select
d.INT_ID
,cast( null as int ) as PUMP_TEST_ID
,moept.pump_test_id as moe_pump_test_id
,case
when dint.int_start_date is not null then dint.int_start_date
else cast( '1867-07-01' as datetime )
end as PUMPTEST_DATE
,cast(d.moe_well_id as varchar(20)) as PUMPTEST_NAME
,cast(
case
when moept.LEVELS_UOM='ft' then moept.Recom_depth*0.3048
else moept.Recom_depth
end as float) as [REC_PUMP_DEPTH_METERS]
,cast(
case
when moept.RATE_UOM='LPM' then moept.Recom_rate/4.55
else moept.Recom_rate
end as float) as [REC_PUMP_RATE_IGPM]
,cast(
case
when moept.RATE_UOM='LPM' then moept.Flowing_rate/4.55
else moept.Flowing_rate
end as float) as [FLOWING_RATE_IGPM]
,cast(522 as int) as [DATA_ID]
,cast(
case
when moept.PUMPING_TEST_METHOD is null then null
when moept.PUMPING_TEST_METHOD=0 then null
when moept.PUMPING_TEST_METHOD=1 then 1
when moept.PUMPING_TEST_METHOD=2 then 2
when moept.PUMPING_TEST_METHOD=3 then 4
when moept.PUMPING_TEST_METHOD=4 then 8
when moept.PUMPING_TEST_METHOD=5 then 9
else 10
end as int) as [PUMPTEST_METHOD_CODE]
,cast(1 as int) as [PUMPTEST_TYPE_CODE] -- this is a constant rate indicator
,cast(
case
when moept.WATER_STATE_AFTER_TEST is null then 0
else moept.WATER_STATE_AFTER_TEST
end as int) as [WATER_CLARITY_CODE]
,ROW_NUMBER() over (order by dint.INT_ID) as rkey
into moe_20200721.dbo.O_D_PUMPTEST
from
moe_20200721.dbo.TblPump_Test as moept
inner join moe_20200721.dbo.ormgp_20200721_upd_dpump as d
on moept.pipe_id=d.pipe_id
inner join oak_20160831_master.dbo.d_interval as dint
on d.int_id=dint.int_id
Now, corresponding to the methodology in G.10.19, we’ll adjust the MON_FLOWING tag in D_INTERVAL_MONITOR (as well as related fields in various other tables). We will also modify the FLOWING_RATE_IGPM (as necessary). First, do we have a null MON_FLOWING while REC_PUMP_RATE_IGPM equals FLOWING_RATE_IGPM - if so, make FLOWING_RATE_IGPM null.
update moe_20200721.dbo.o_d_pumptest
set
flowing_rate_igpm=null
from
moe_20200721.dbo.o_d_pumptest as d
where
d.INT_ID
not in
(
select
dim.INT_ID
from
oak_20160831_master.dbo.d_interval_monitor as dim
where
dim.MON_FLOWING is not null
)
and d.REC_PUMP_RATE_IGPM=d.FLOWING_RATE_IGPM
Next, there is no REC_PUMP_RATE_IGPM but there is a FLOWING_RATE_IGPM - tag MON_FLOWING.
update oak_20160831_master.dbo.d_interval_monitor
set
mon_flowing=1
from
oak_20160831_master.dbo.d_interval_monitor as dim
where
dim.INT_ID
in
(
select
d.INT_ID
from
moe_20200721.dbo.o_d_pumptest as d
where
d.INT_ID
in
(
select
d2.INT_ID
from
oak_20160831_master.dbo.d_interval_monitor as d2
where
d2.MON_FLOWING is null
)
and d.REC_PUMP_RATE_IGPM is null
and d.FLOWING_RATE_IGPM is not null
)
If the FLOWING_RATE_IGPM is less than the REC_PUMP_RATE_IGPM and MON_FLOWING is not tagged, tag MON_FLOWING.
update oak_20160831_master.dbo.d_interval_monitor
set
mon_flowing=1
from
oak_20160831_master.dbo.d_interval_monitor as dim
where
dim.INT_ID
in
(
select
d.INT_ID
from
moe_20200721.dbo.o_d_pumptest as d
where
d.INT_ID
in
(
select
d2.INT_ID
from
oak_20160831_master.dbo.d_interval_monitor as d2
where
d2.MON_FLOWING is null
)
and d.REC_PUMP_RATE_IGPM is null
and d.FLOWING_RATE_IGPM is not null
)
If the FLOWING_RATE_IGPM is greater than REC_PUMP_RATE_IGPM and MON_FLOWING is not tagged, null the FLOWING_RATE_IGPM (this is considered an error).
update moe_20200721.dbo.o_d_pumptest
set
flowing_rate_igpm=null
from
moe_20200721.dbo.o_d_pumptest as dpump
where
dpump.INT_ID
in
(
select
d.INT_ID
from
moe_20200721.dbo.o_d_pumptest as d
where
d.INT_ID
in
(
select
d2.INT_ID
from
oak_20160831_master.dbo.d_interval_monitor as d2
where
d2.MON_FLOWING is null
)
and dpump.FLOWING_RATE_IGPM>dpump.REC_PUMP_RATE_IGPM
)
Script: G_30_08_01_DPUMP.sql
We can now build the pumping drawdown (i.e. ‘D’) and recovery (i.e. ‘R’) information for the particular pump tests (as determined in the previous step). First the drawdown information.
select
curr.Pump_Test_id as moe_pump_test_id
,cast(moept.Pumping_rate as float) as PUMP_RATE
,cast(moept.RATE_UOM as varchar(50)) as PUMP_RATE_UNITS
,cast(moept.Pumping_rate as float) as PUMP_RATE_OUOM
,cast(moept.RATE_UOM as varchar(50)) as PUMP_RATE_UNITS_OUOM
,dateadd(minute,curr.TestDuration,dpump.PUMPTEST_DATE) as [PUMP_START]
,dateadd(minute,prev.TestDuration,dpump.PUMPTEST_DATE) as [PUMP_END]
,cast(521 as int) as DATA_ID
,null as [SYS_RECORD_ID]
,null as [rkey]
,prev.TestType as [testtype]
,prev.TestLevel as [testlevel]
,prev.TESTLEVEL_UOM as [testlevel_uom]
into MOE_20200721.dbo.ORMGP_20200721_upd_DPUMPSTEP
from
(
select
moeptd.Pump_Test_id
,moeptd.TestDuration
,ROW_NUMBER() over (order by moeptd.Pump_Test_id,moeptd.TestDuration) as rnum
from
MOE_20200721.dbo.TblPump_Test_Detail as moeptd
where
moeptd.TestType='D'
) as [curr]
inner join
(
select
moeptd.Pump_Test_id
,moeptd.TestDuration
,moeptd.TestLevel
,moeptd.TestType
,moeptd.TESTLEVEL_UOM
,ROW_NUMBER() over (order by moeptd.Pump_Test_id,moeptd.TestDuration) as rnum
from
MOE_20200721.dbo.TblPump_Test_Detail as moeptd
where
moeptd.TestType='D'
) as [prev]
on curr.Pump_Test_id=prev.Pump_Test_id and curr.rnum=(prev.rnum-1)
inner join MOE_20200721.dbo.TblPump_Test as moept
on curr.Pump_Test_id=moept.PUMP_TEST_ID
inner join moe_20200721.dbo.o_d_pumptest as dpump
on curr.pump_test_id=dpump.moe_pump_test_id
order by
curr.Pump_Test_id,PUMP_START
Now we can create the first time step - add to the previous table.
insert into MOE_20200721.dbo.ORMGP_20200721_upd_DPUMPSTEP
(
moe_pump_test_id
,[PUMP_RATE]
,[PUMP_RATE_UNITS]
,[PUMP_RATE_OUOM]
,[PUMP_RATE_UNITS_OUOM]
,[PUMP_START]
,[PUMP_END]
,[DATA_ID]
,[SYS_RECORD_ID]
,[rnum]
,[testtype]
,[testlevel]
,[testlevel_uom]
)
select
curr.Pump_Test_id as moe_pump_test_id
,cast(moept.Pumping_rate as float) as PUMP_RATE
,cast(moept.RATE_UOM as varchar(50)) as PUMP_RATE_UNITS
,cast(moept.Pumping_rate as float) as PUMP_RATE_OUOM
,cast(moept.RATE_UOM as varchar(50)) as PUMP_RATE_UNITS_OUOM
,dateadd(minute,0,dpump.PUMPTEST_DATE) as [PUMP_START]
,dateadd(minute,curr.TestDuration,dpump.PUMPTEST_DATE) as [PUMP_END]
,cast(521 as int) as DATA_ID
,cast(null as int) as [SYS_RECORD_ID]
,cast(null as int) as [rkey]
,moeptd.TestType as [testtype]
,moeptd.TestLevel as [testlevel]
,moeptd.TESTLEVEL_UOM as [testlevel_uom]
from
(
select
moeptd.Pump_Test_id
,min(moeptd.TestDuration) as TestDuration
from
MOE_20200721.dbo.TblPump_Test_Detail as moeptd
where
moeptd.TestType='D'
group by
moeptd.Pump_Test_id
) as curr
inner join MOE_20200721.dbo.TblPump_Test as moept
on curr.Pump_Test_id=moept.PUMP_TEST_ID
inner join MOE_20200721.dbo.TblPump_Test_Detail as moeptd
on curr.Pump_Test_id=moeptd.Pump_Test_id and curr.TestDuration=moeptd.TestDuration
inner join moe_20200721.dbo.o_d_pumptest as dpump
on curr.pump_test_id=dpump.moe_pump_test_id
where
moeptd.TestType='D'
order by
curr.Pump_Test_id,PUMP_START
Now add the pumping recovery information (based upon the maximum datetime for the particular PUMP_TEST_ID).
insert into moe_20200721.dbo.ormgp_20200721_upd_dpumpstep
(
moe_pump_test_id
,pump_end
,data_id
,sys_record_id
,rnum
,testtype
,testlevel
,testlevel_uom
)
select
curr.Pump_Test_id as moe_pump_test_id
,dateadd(minute,curr.TestDuration,mt.enddate) as [PUMP_END]
,cast(522 as int) as DATA_ID
,null as [SYS_RECORD_ID]
,null as [rnum]
,curr.TestType as [testtype]
,curr.TestLevel as [testlevel]
,curr.TESTLEVEL_UOM as [testlevel_uom]
from
(
select
moeptd.Pump_Test_id
,moeptd.TestDuration
,moeptd.TestType
,moeptd.TestLevel
,moeptd.TESTLEVEL_UOM
from
MOE_20200721.dbo.TblPump_Test_Detail as moeptd
where
moeptd.TestType='R'
) as [curr]
inner join
(
select
ycps.moe_Pump_Test_id
,max(ycps.PUMP_END) as enddate
from
MOE_20200721.dbo.ORMGP_20200721_upd_dpumpstep as ycps
where
ycps.TestType='D'
group by
ycps.moe_Pump_Test_id
) as mt
on curr.Pump_Test_id=mt.moe_Pump_Test_id
inner join MOE_20200721.dbo.TblPump_Test as moept
on curr.Pump_Test_id=moept.PUMP_TEST_ID
inner join MOE_20200721.dbo.o_D_PUMPTEST as dpump
on curr.Pump_Test_id=dpump.pump_test_id
order by
curr.Pump_Test_id,PUMP_END
Create the O_D_PUMPTEST_STEP table based upon a grouping of the grouping of pumping rate and units. Note that only the drawdown information is being used.
select
cast( null as int ) as PUMP_TEST_ID
,t1.moe_PUMP_TEST_ID
,t1.PUMP_RATE
,t1.PUMP_RATE_UNITS
,t1.PUMP_RATE_OUOM
,t1.PUMP_RATE_UNITS_OUOM
,t1.PUMP_START
,t1.PUMP_END
,cast(522 as int) as DATA_ID
,t1.SYS_RECORD_ID
,t1.rkey
into MOE_20200721.dbo.O_D_PUMPTEST_STEP
from
(
SELECT
[moe_PUMP_TEST_ID]
,[PUMP_RATE]
,[PUMP_RATE_UNITS]
,[PUMP_RATE_OUOM]
,[PUMP_RATE_UNITS_OUOM]
,min([PUMP_START]) as [PUMP_START]
,max([PUMP_END]) as [PUMP_END]
,cast( null as int ) as SYS_RECORD_ID
,ROW_NUMBER() over (order by moe_PUMP_TEST_ID) as rkey
FROM MOE_20200721.[dbo].ormgp_20200721_upd_dpumpstep
where
testtype='D'
group by
moe_Pump_Test_id,PUMP_RATE,PUMP_RATE_UNITS,PUMP_RATE_OUOM,PUMP_RATE_UNITS_OUOM
) as t1
We can now add the pumping and recovery water levels into a compatible D_INTERVAL_TEMPORAL_2 table. Add an index to this table.
select
dp.INT_ID
,case
when dps.testtype='D' then cast(65 as int) -- i.e. moe pumping level
else cast(64 as int) -- i.e. moe recovery level
end as [RD_TYPE_CODE]
,cast(70899 as int) as [RD_NAME_CODE] -- i.e. Water Level - Manual - Other
,dps.PUMP_END as [RD_DATE]
,case
when dps.testlevel_uom='m' then dbore.bh_gnd_elev - dps.testlevel
else dbore.bh_gnd_elev - (dps.testlevel*0.3048)
end as [RD_VALUE]
,cast(6 as int) as [UNIT_CODE]
,cast('Water Level - Manual - Other' as varchar(100)) as RD_NAME_OUOM
,cast(dps.testlevel as float) as RD_VALUE_OUOM
,cast(dps.testlevel_uom as varchar(50)) as RD_UNIT_OUOM
,cast(1 as int) as REC_STATUS_CODE
,cast(
case
when dps.testtype='D' then 'Pumping - Drawdown'
else 'Pumping - Recovery'
end as varchar(255) ) as [RD_COMMENT]
,dps.DATA_ID as [DATA_ID]
,dps.SYS_RECORD_ID
,dps.rkey
into moe_20200721.dbo.O_D_INTERVAL_TEMPORAL_2_70899
from
MOE_20200721.[dbo].ORMGP_20200721_upd_DPUMPSTEP as dps
inner join MOE_20200721.dbo.O_D_PUMPTEST as dp
on dps.moe_PUMP_TEST_ID=dp.moe_PUMP_TEST_ID
inner join oak_20160831_master.dbo.d_interval as dint
on dp.int_id=dint.int_id
inner join oak_20160831_master.dbo.d_borehole as dbore
on dint.loc_id=dbore.loc_id
where
dps.testtype is not null
and dbore.bh_gnd_elev is not null
order by
dp.INT_ID,RD_DATE
Finally populate the SYS_RECORD_ID field.
update moe_20200721.dbo.o_d_interval_temporal_2_70899
set
sys_record_id= t2.sri
from
moe_20200721.dbo.o_d_interval_temporal_2_70899 as d
inner join
(
select
t.sri
,row_number() over (order by t.sri) as rkey
from
(
select
top 15000
v.new_id as sri
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select sys_record_id from oak_20160831_master.dbo.d_interval_temporal_2 )
) as t
) as t2
on d.rkey=t2.rkey
And then insert into the D_INTERVAL_TEMPORAL_2 table.
insert into oak_20160831_master.dbo.d_interval_temporal_2
(
[INT_ID],
[RD_TYPE_CODE],
[RD_NAME_CODE],
[RD_DATE],
[RD_VALUE],
[UNIT_CODE],
[RD_NAME_OUOM],
[RD_VALUE_OUOM],
[RD_UNIT_OUOM],
[REC_STATUS_CODE],
[RD_COMMENT],
[DATA_ID],
[SYS_RECORD_ID],
SYS_TEMP1,
SYS_TEMP2
)
select
[INT_ID],
[RD_TYPE_CODE],
[RD_NAME_CODE],
[RD_DATE],
[RD_VALUE],
[UNIT_CODE],
[RD_NAME_OUOM],
[RD_VALUE_OUOM],
[RD_UNIT_OUOM],
[REC_STATUS_CODE],
[RD_COMMENT],
[DATA_ID],
[SYS_RECORD_ID],
cast( '20200811a' as varchar(255) ) as SYS_TEMP1,
cast( 20200811 as int ) as SYS_TEMP2
from
moe_20200721.dbo.o_d_interval_temporal_2_70899
Script: G_30_08_02_DPUMPSTEP.sql
We’ll now incorporate the information from the pumping tables into the ORMDB. Create the PUMP_TEST_ID values for O_D_PUMPTEST.
update moe_20200721.dbo.o_d_pumptest
set
pump_test_id= t2.pump_test_id
from
moe_20200721.dbo.o_d_pumptest as d
inner join
(
select
t.pump_test_id
,row_number() over (order by t.pump_test_id) as rkey
from
(
select
top 2000
v.new_id as pump_test_id
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select pump_test_id from oak_20160831_master.dbo.d_pumptest )
) as t
) as t2
on d.rkey=t2.rkey
Insert these values into D_PUMPTEST.
insert into oak_20160831_master.dbo.d_pumptest
(
[INT_ID],
[PUMP_TEST_ID],
[PUMPTEST_DATE],
[PUMPTEST_NAME],
[REC_PUMP_DEPTH_METERS],
[REC_PUMP_RATE_IGPM],
[FLOWING_RATE_IGPM],
[DATA_ID],
[PUMPTEST_METHOD_CODE],
[PUMPTEST_TYPE_CODE],
[WATER_CLARITY_CODE],
SYS_TEMP1,
SYS_TEMP2
)
select
[INT_ID],
[PUMP_TEST_ID],
[PUMPTEST_DATE],
[PUMPTEST_NAME],
[REC_PUMP_DEPTH_METERS],
[REC_PUMP_RATE_IGPM],
[FLOWING_RATE_IGPM],
[DATA_ID],
[PUMPTEST_METHOD_CODE],
[PUMPTEST_TYPE_CODE],
[WATER_CLARITY_CODE],
cast( '20200811b' as varchar(255) ) as SYS_TEMP1,
cast( 20200811 as int ) as SYS_TEMP2
from
moe_20200721.dbo.o_d_pumptest
Now, update the PUMP_TEST_ID field in O_D_PUMPTEST_STEP.
update moe_20200721.dbo.o_d_pumptest_step
set
pump_test_id= dp.pump_test_id
from
moe_20200721.dbo.o_d_pumptest_Step as dps
inner join moe_20200721.dbo.o_d_pumptest as dp
on dps.moe_pump_test_id=dp.moe_pump_test_id
And then populate the SYS_RECORD_ID field.
update moe_20200721.dbo.o_d_pumptest_step
set
sys_record_id= t2.sri
from
moe_20200721.dbo.o_d_pumptest_step as d
inner join
(
select
t.sri
,row_number() over (order by t.sri) as rkey
from
(
select
top 1600
v.new_id as sri
from
oak_20160831_master.dbo.v_sys_random_id_bulk_001 as v
where
v.new_id not in
( select sys_record_id from oak_20160831_master.dbo.d_pumptest_step )
) as t
) as t2
on d.rkey=t2.rkey
Finally, insert this information into D_PUMPTEST_STEP.
insert into oak_20160831_master.dbo.d_pumptest_step
(
[PUMP_TEST_ID],
[PUMP_RATE],
[PUMP_RATE_UNITS],
[PUMP_RATE_OUOM],
[PUMP_RATE_UNITS_OUOM],
[PUMP_START],
[PUMP_END],
[DATA_ID],
[SYS_RECORD_ID],
SYS_TEMP1,
SYS_TEMP2
)
select
[PUMP_TEST_ID],
[PUMP_RATE],
[PUMP_RATE_UNITS],
[PUMP_RATE_OUOM],
[PUMP_RATE_UNITS_OUOM],
[PUMP_START],
[PUMP_END],
[DATA_ID],
[SYS_RECORD_ID],
cast( '20200811b' as varchar(255) ) as SYS_TEMP1,
cast( 20200811 as int ) as SYS_TEMP2
from
moe_20200721.dbo.o_d_pumptest_step
Script: G_30_08_03_DPID.sql