SQL code to update: NA and zeros #87
Description
The issue applies to both the CPR and NRS Indices collections, although it is unlikey to occur in NRS as the samples are bigger and we rarely get the situation arising.
For the NoCopepodSpecies_Sample column this should not be NA for an analysed sample. If there is a 'Z' in the CPR_SAMP.SAMPLETYPE column, then we have analysed it.
If the NoCopepodSpecies_Sample calculation is returning NA, but CopeAbundance is > 0 then NoCopepodSpecies_Sample should equal 1 and not NA, if CopeAbundance is = 0, then NoCopepodSpecies_Sample should equal 0, not NA. This is because although we haven't speciated a copepod in the data count, we know that at least one species of copepod is present.
Sometimes in a CPR sample we will analyse the phyto sample but record no phyto, this should be recorded as PhytoAbundance = 0, not NA. If there is a 'P' in the CPR_SAMP.SAMPLETYPE column, then we have analysed it.
Following on from this, as above, if the NoPhytoSpecies_Sample calculation is returning NA, but PhytoAbundance is > 0 then NoPhytoSpecies_Sample should equal 1 and not NA, if PhytoAbundance is = 0, then NoPhytoSpecies_Sample should equal 0, not NA.
Hopefully, this makes sense, it is a bit confusing when you don't know what we are doing in detail,
Let me know if you need more information,
Thanks,
Claire