Govedo - status živali

Cattle - Sum of animal by status

declare @animalType varchar(3)
set @animalType=(SELECT acSetOfItemCattle FROM dbo.tFA_Param)
select s.acStatusName,
        COUNT(acAnimal) as num        
from tFA_LiveStock 
join tFA_SetOfAnimalStatus s on s.acStatusCode = 'S' and s.acStatusType = 'HS'
where acSetOfAnimalType = @animalType
and acStatusHealth = 'S' 
group by s.acStatusName
union all
select s.acStatusName,
        COUNT(acAnimal) as num        
from tFA_LiveStock
join tFA_SetOfAnimalStatus s on s.acStatusCode = 'WC' and s.acStatusType = 'RS'
where acSetOfAnimalType = @animalType
and acStatusRepro = 'WC' 
group by s.acStatusName
union all
select s.acStatusName,
        COUNT(acAnimal) as num        
from tFA_LiveStock
join tFA_SetOfAnimalStatus s on s.acStatusCode = 'IL' and s.acStatusType = 'PS'
where acSetOfAnimalType = @animalType
and acStatusProd = 'IL'
group by s.acStatusName
union all
select s.acStatusName,
        COUNT(acAnimal) as num        
from tFA_LiveStock
join tFA_SetOfAnimalStatus s on s.acStatusCode = 'DR' and s.acStatusType = 'AS'
where acSetOfAnimalType = @animalType
and acStatus = 'DR'
group by s.acStatusName