Table 1

[Ned-Laman-NOAA]

• [x] 1. Summarize by SUMMARY_AREA_DEPTH rather than by STRATUM to eliminate cartesian-looking product in table (see SQLPlus attached). Noting here that this comment is unique to the AI and that summary by depth STRATUM within district will be the du jour for the restratified GOA survey when we implement it.
• [x] 2. Order of sampling districts should be consistent and parallel throughout all subsequent tables, figures, and text: my preferred order in the Aleutians is East to West - SBS, EAI, CAI, WAI. However, if this involves major restructuring of text to parallelize order in text with order in tables and figures I would consider this a low priority
• [x] 3. Historically, the field names for this table from left to right are "Survey District", "Depth Range (m)", "Stations Allocated", "Stations Attempted", "Stations Completed", "Area km^2^" referring to total area of depth stratum within Survey District, and "Stations/1,000 km^2^". The text of the field names does not have to be identical but the content should be the same as has been historically presented. At present I recommend the following changes to the table header row: "INPFC Area" changed to "Survey District" since the latter is used more in text; "Succeeded" field needs wider column margin to avoid wrapping; replace "Stratum" and "Stations per 1,000 km2" with "Area (km2)" representing district depth stratum area and using superscript for square; for "Stations per 1,000 km^2" replace ^2 with superscript

-- SQLPlus for Table 1 (historic) create or replace view DATA_REPORT_TABLE_1 as select a.INPFC_AREA, a.DEPTH_RANGE, STATIONS_ALLOCATED, STATION_COUNT STATIONS_ATTEMPTED, HAUL_COUNT STATIONS_COMPLETED, AREA, round(((HAUL_COUNT/AREA)1000),2) SAMPLING_DENSITY, A.SUMMARY_AREA_DEPTH from ( select INPFC_AREA, DEPTH_RANGE, round(sum(AREA),0) AREA,
SUMMARY_AREA_DEPTH -- sum by stratum within a fishing area and depth range from ( select INPFC_AREA, MIN_DEPTH|| ' - ' ||MAX_DEPTH DEPTH_RANGE, AREA, SUMMARY_AREA_DEPTH from GOA.GOA_STRATA where SURVEY = 'AI' ) group by INPFC_AREA, DEPTH_RANGE, SUMMARY_AREA_DEPTH ) a, ( select INPFC_AREA, DEPTH_RANGE, SUMMARY_AREA_DEPTH, count() HAUL_COUNT -- count of hauls successfully completed during survey from ( select INPFC_AREA, MIN_DEPTH|| ' - ' ||MAX_DEPTH DEPTH_RANGE, SUMMARY_AREA_DEPTH, VESSEL, CRUISE, HAUL from GOA.GOA_STRATA A, RACEBASE.HAUL B where B.REGION = 'AI' and B.REGION = A.SURVEY and CRUISE = 202201 and VESSEL in (176,148) and A.STRATUM = B.STRATUM and B.ABUNDANCE_HAUL = 'Y' -- with this constraint it is not possible to accidentally double count a station in common across vessels which is a sampling error in itself ) group by INPFC_AREA,DEPTH_RANGE,SUMMARY_AREA_DEPTH ) b, ( select INPFC_AREA, SUMMARY_AREA_DEPTH, DEPTH_RANGE, count() STATION_COUNT --count of all "stations" (composite stationid/stratum identities) attempted from ( select INPFC_AREA, SUMMARY_AREA_DEPTH, MIN_DEPTH|| ' - ' ||MAX_DEPTH DEPTH_RANGE from GOA.GOA_STRATA a, ( select distinct REGION, -- returns all stations sampled regardless of success but does not count repeated attempts at same station CRUISE, STATIONID, STRATUM from RACEBASE.HAUL where REGION = 'AI' and CRUISE = 202201 and VESSEL in (176,148) and HAUL_TYPE = 3 ) b where B.REGION = A.SURVEY and A.STRATUM = B.STRATUM ) group by INPFC_AREA,DEPTH_RANGE,SUMMARY_AREA_DEPTH ) c, ( select INPFC_AREA, SUMMARY_AREA_DEPTH, DEPTH_RANGE, count() STATIONS_ALLOCATED -- from initial station allocation undertaken during survey planning phase from ( select INPFC_AREA, SUMMARY_AREA_DEPTH, MIN_DEPTH|| ' - ' ||MAX_DEPTH DEPTH_RANGE from GOA.GOA_STRATA a, AI.STATION_ALLOCATION b -- in AI because new stations assigned are id'd by stratum only, this will return new and prev successful stations allocated where B.SURVEY = 'AI'
and B.SURVEY = A.SURVEY and A.STRATUM = B.STRATUM and B.YEAR = 2022 ) group by INPFC_AREA,DEPTH_RANGE,SUMMARY_AREA_DEPTH ) d where A.INPFC_AREA = B.INPFC_AREA and B.INPFC_AREA = C.INPFC_AREA and C.INPFC_AREA = D.INPFC_AREA and A.DEPTH_RANGE = B.DEPTH_RANGE and B.DEPTH_RANGE = C.DEPTH_RANGE and C.DEPTH_RANGE = D.DEPTH_RANGE and A.SUMMARY_AREA_DEPTH = B.SUMMARY_AREA_DEPTH and B.SUMMARY_AREA_DEPTH = C.SUMMARY_AREA_DEPTH and C.SUMMARY_AREA_DEPTH = D.SUMMARY_AREA_DEPTH order by A.SUMMARY_AREA_DEPTH,A.DEPTH_RANGE /

[MargaretSiple-NOAA]

Notes to self:

• [x] check Tbl1Fxn.R script sent by Ned against existing Table 1.
• [x] Add this check to markdown/check_values_against_legacy.Rmd