Add Year column to "AKFIN_SPECIMEN"

[Lee-Cronin-Fine-NOAA]

Issue

I was pulling the length-weight data for my assessment from the "AKFIN_SPECIMEN" table and noticed there is no column that indicates the year in which the data was collected. This information would be valuable to have since it would allow assessment authors to see if there has been a change in the length-weight relationship over years. I was wondering if it would be possible to add this column to the "AKFIN_SPECIMEN" table?

[zoyafuso-NOAA]

Hi Lee,

I agree this information is important. To reduce redundancies, only the key bits of info for a table are included (to the extent possible) and the idea is to join other akfin_* tables to get axillary information (like year, vessel, region, lat/lon, etc.). For example, the YEAR column can be extracted from akfin_cruise (with key field cruisejoin) and it can then be joined with the akfin_specimen table via the hauljoin key. Try out this example below and you can add further WHERE clauses after the two JOIN lines

select * from gap_products.akfin_specimen 
join (select hauljoin, cruisejoin from gap_products.akfin_haul) using (HAULJOIN)
join (select cruisejoin, year from gap_products.akfin_cruise) using (CRUISEJOIN)

[zoyafuso-NOAA]

Hey @Lee-Cronin-Fine-NOAA ,

Just checking in on this issue. Did you find this code helpful or is there something I can clarify further?

Thanks, Zack

[Lee-Cronin-Fine-NOAA]

Hey Zack,

The code you gave me worked.

Thanks for the help and checking in.

Sorry for the late reply.

Lee