scrapie counts by age and sex.txt 2.24 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
SELECT 
	COUNT (*) FROM 
	(SELECT 
		sheep_table.sheep_id 
		, (SELECT 
			id_scrapie_flock_table.scrapie_flockid 
			FROM id_info_table 
			INNER JOIN id_scrapie_flock_table ON id_scrapie_flock_table.id_flockid = id_info_table.id_flockid  
			WHERE 
				official_id = "1"
				AND id_info_table.sheep_id = sheep_table.sheep_id 
				AND  (tag_date_off IS NULL or tag_date_off = '')) 
			AS scrapieflock
		, (SELECT 
			tag_number 
			FROM id_info_table 
			WHERE 
				official_id = "1" 
				AND id_info_table.sheep_id = sheep_table.sheep_id 
				AND  (tag_date_off IS NULL or tag_date_off = '')) 
			AS fedtag
		, (SELECT 
			tag_number 
			FROM id_info_table 
			WHERE 
				tag_type = "4" 
				AND id_info_table.sheep_id = sheep_table.sheep_id 
				AND  (tag_date_off IS NULL or tag_date_off = '')) 
			AS farmtag
		, (SELECT 
			tag_number 
			FROM id_info_table 
			WHERE 
				tag_type = "2" 
				AND id_info_table.sheep_id = sheep_table.sheep_id 
				AND (tag_date_off IS NULL or tag_date_off = '') 
				AND ( id_info_table.official_id is NULL or id_info_table.official_id = 0 )) 
			AS eidtag
		, flock_prefix_table.flock_name
		, sheep_table.sheep_name
		, codon171_table.codon171_alleles
		, sheep_table.birth_date
		, sheep_sex_table.sex_abbrev
		, birth_type_table.birth_type
Oogie McGuire's avatar
Oogie McGuire committed
45
		, farm_location_table.farm_location_name
46 47 48 49 50
	FROM sheep_table 
	INNER JOIN codon171_table ON sheep_table.codon171 = codon171_table.id_codon171id 
	INNER JOIN flock_prefix_table ON sheep_table.flock_prefix = flock_prefix_table.flock_prefixid
	LEFT JOIN birth_type_table ON sheep_table.birth_type = birth_type_table.id_birthtypeid 
	LEFT JOIN sheep_sex_table ON sheep_table.sex = sheep_sex_table.sex_sheepid 
Oogie McGuire's avatar
Oogie McGuire committed
51
	LEFT JOIN farm_location_table ON sheep_table.id_locationid  = farm_location_table.id_farmlocationid
52 53 54 55 56 57 58 59 60 61 62 63 64
	WHERE 
		(sheep_table.remove_date IS NULL or sheep_table.remove_date is '')
--		Edit this to be the appropriate year to get either older or younger sheep.
--		Change the year to be the year of the current lambs
--		Change the condition to be < for older sheep and > for the lambs
		AND sheep_table.birth_date < "2017%"
--		Edit this to be the proper sex 1 for rams, 2 for ewes and 3 for wethers
		AND sheep_table.sex = 2
	ORDER BY 
		sheep_sex_table.sex_abbrev asc 
		, sheep_table.birth_date asc
)

Oogie McGuire's avatar
Oogie McGuire committed
65 66