Create check: BiologySumOfIndividualWeights <= CatchSubsampleWeight

[HjalteParner]

If Catch species category weight and subsample weight are reported, then the sum of individuals weights for that haul, species and category in Biology should be less than or equal to the catch weights.

5 873 catch records having catch weights > sum of individual weights

WITH CTE (CruiseLocalID,[HaulID],[SpeciesID],[SpeciesCategory],[SpeciesCategoryWeight],[SubSampleWeight],[BiologySumOfIndividualWeight]) AS ( SELECT [Cruise].[LocalID] AS 'CruiseLocalID' ,[HaulID] ,[SpeciesID] ,[SpeciesCategory] ,IIF([WeightUnitID] = 1, [SpeciesCategoryWeight], [SpeciesCategoryWeight] / 1000) AS 'SpeciesCategoryWeight' ,IIF([WeightUnitID] = 1, [SubSampleWeight], [SubSampleWeight] / 1000) AS 'SubSampleWeight' ,(SELECT SUM(IIF([WeightUnitID] = 1, [IndividualWeight], [IndividualWeight] / 1000)) FROM [Biology] WHERE [Biology].[CatchID] = [Catch].[ID]) AS 'BiologySumOfIndividualWeight' FROM [Catch] INNER JOIN [Haul] ON [Catch].[HaulID] = [Haul].[ID] INNER JOIN [Cruise] ON [Haul].[CruiseID] = [Cruise].[ID] ) SELECT *, [SubSampleWeight] - [BiologySumOfIndividualWeight] FROM CTE WHERE [SpeciesCategoryWeight] IS NOT NULL AND [SubsampleWeight] IS NOT NULL AND [BiologySumOfIndividualWeight] > [SubSampleWeight]

AcousticBioticCatchSpeciesCategorySubsampleWeightsWrong.csv

[elorsepp]

@HjalteParner promised to do something to test the tolerances

[elorsepp]

Everybody, please have a look at the .csv file and test to come up with suggestions for tolerances.