Here's a quick bit of SQL code that will get condition factor (observed weight / expected weight) per haul and disposition code for haddock. The length-weight regression factors are hard-coded here, but could be linked to a reference table. This is based on how we do it for DQ checks - making sure that the observed weight is within a reasonable range around the expected weight.
select link3, area, nespp4,
fishdisp, sampweight,
round(sum(2.2exp(-11.8111 + 3.0888ln(lenanml))numlen),1) as expwgt,
round(sampweight/sum(2.2exp(-11.8111 + 3.0888ln(lenanml))numlen),1) as kn
from (
select d.link3, a.area, d.nespp4, d.fishdisp, sampweight, lenanml, numlen
from oblnd d join oblnh h on d.link5 = h.link5
join obhau a on a.link3 = h.link3
UNION ALL
select d.link3, a.area, d.nespp4, d.fishdisp, sampweight, lenanml, numlen
from asmlnd d join asmlnh h on d.link3||d.nespp4||d.fishdisp = h.link3||h.nespp4||h.fishdisp
join asmhau a on a.link3 = h.link3
)
where nespp4 = '1477' and fishdisp = '100' and sampweight is not null
group by link3, area, nespp4, fishdisp, sampweight
Here's a quick bit of SQL code that will get condition factor (observed weight / expected weight) per haul and disposition code for haddock. The length-weight regression factors are hard-coded here, but could be linked to a reference table. This is based on how we do it for DQ checks - making sure that the observed weight is within a reasonable range around the expected weight.
select link3, area, nespp4, fishdisp, sampweight, round(sum(2.2exp(-11.8111 + 3.0888ln(lenanml))numlen),1) as expwgt, round(sampweight/sum(2.2exp(-11.8111 + 3.0888ln(lenanml))numlen),1) as kn from ( select d.link3, a.area, d.nespp4, d.fishdisp, sampweight, lenanml, numlen from oblnd d join oblnh h on d.link5 = h.link5 join obhau a on a.link3 = h.link3 UNION ALL select d.link3, a.area, d.nespp4, d.fishdisp, sampweight, lenanml, numlen from asmlnd d join asmlnh h on d.link3||d.nespp4||d.fishdisp = h.link3||h.nespp4||h.fishdisp join asmhau a on a.link3 = h.link3 ) where nespp4 = '1477' and fishdisp = '100' and sampweight is not null group by link3, area, nespp4, fishdisp, sampweight