get carcass weights.txt 1.57 KB
Newer Older
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47
--	Get all the carcass weights for all sheep
--	Includes sort by sex

SELECT 
	sheep_table.sheep_id
	, sheep_table.sheep_name
	, sheep_sex_table.sex_abbrev
	, sheep_table.birth_date
	, sheep_table.death_date
	, sheep_evaluation_table.age_in_days, age_in_days/365 AS years
	, sheep_evaluation_table.trait_score11 AS carcass_weight
	, units_table.units_name
FROM sheep_evaluation_table 
	INNER JOIN 	sheep_table 		ON sheep_evaluation_table.sheep_id 		= sheep_table.sheep_id 
	INNER JOIN 	units_table 		ON sheep_evaluation_table.trait_units11 	= units_table.id_unitsid
	INNER JOIN 	sheep_sex_table 	ON sheep_table.sex 					= sheep_sex_table.sex_sheepid
WHERE 
	trait_name11 = 27
ORDER BY 
	years
	, sheep_sex_table.sex_abbrev
	, sheep_table.birth_date ASC

--	This version gets carcass weights for sheep removed on a specific date or year
-- 	Get on a specific year/month of slaughter

SELECT 
	sheep_table.sheep_id
	, sheep_table.sheep_name
	, sheep_sex_table.sex_abbrev
	, sheep_table.birth_date
	, sheep_table.death_date
	, sheep_evaluation_table.age_in_days, age_in_days/365 AS years
	, sheep_evaluation_table.trait_score11 as carcass_weight
	, units_table.units_name
FROM sheep_evaluation_table 
	INNER JOIN sheep_table 		ON sheep_evaluation_table.sheep_id 		= sheep_table.sheep_id 
	INNER JOIN units_table 			ON sheep_evaluation_table.trait_units11 	= units_table.id_unitsid
	INNER JOIN sheep_sex_table 		ON sheep_table.sex 					= sheep_sex_table.sex_sheepid
WHERE 
	trait_name11 = 27
	AND sheep_table.remove_date LIKE "2017-06%"
ORDER BY 
	years
	, sheep_sex_table.sex_abbrev
	, sheep_table.birth_date ASC