Hi smile,
Refer below query.
SQL
DECLARE @tblAnimal AS TABLE(ID INT,TagID INT)
INSERT INTO @tblAnimal VALUES(1,101)
INSERT INTO @tblAnimal VALUES(2,102)
INSERT INTO @tblAnimal VALUES(3,103)
INSERT INTO @tblAnimal VALUES(4,104)
INSERT INTO @tblAnimal VALUES(5,105)
DECLARE @tblBreading AS TABLE(ID INT,TagID INT,InseminationDate DATE)
INSERT INTO @tblBreading VALUES(1,101,'2021-09-03')
INSERT INTO @tblBreading VALUES(2,102,'2021-09-04')
SELECT (SELECT COUNT(*) FROM @tblBreading WHERE InseminationDate IS NOT NULL) 'Total Inseminated Animals',
CAST(CONVERT(NUMERIC(18, 2),(SELECT COUNT(*) FROM @tblBreading WHERE InseminationDate IS NOT NULL)/ CAST(COUNT(*) AS DECIMAL) * 100) AS VARCHAR) + '%' 'Percentage of Inseminated animal'
FROM @tblAnimal
Output
Total Inseminated Animals |
Percentage of Inseminated animal |
2 |
40.00 % |
Refer below article for displaying in chart.