Refer below query.
SQL
SELECT Head,Count
INTO #Temp
FROM
(
SELECT 'Inseminated' as 'Head' , 10 'Count'
union all
SELECT 'Repeated' as 'Head' , 1
union all
SELECT 'Fresher' as 'Head', 2
union all
SELECT 'Open' as 'Head', 1
union all
SELECT 'Insemination Free' as 'Head', 28
)t
SELECT *,ROUND(CAST((CAST(Count AS DECIMAL)/(SELECT SUM(Count) FROM #Temp))*100 AS Float),2) 'Percentage'
FROM #Temp
DROP TABLE #Temp
Output
| Head |
Count |
Percentage |
| Inseminated |
10 |
23.81 |
| Repeated |
1 |
2.38 |
| Fresher |
2 |
4.76 |
| Open |
1 |
2.38 |
| Insemination Free |
28 |
66.67 |
Your query will be like below.
SELECT Head,Count
INTO #Temp
FROM
(
SELECT 'Inseminated:' as 'Head' ,count(t1.TagID) 'Count' FROM tblBreading t1 LEFT JOIN tblPRegTest t2 ON t1.TagID = t2.TagID and t1.I_Date = t2.I_Date WHERE t2.TagID IS NULL
union all
SELECT 'Repeated:' as 'Head' ,count(t1.TagID) 'count' FROM tblPregTest t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE P_Result='Negative' and t1.C_Date > t2.I_Date
union all
SELECT 'Fresher:' as 'Head', count(TagID) 'count' from tblCalving where DATEDIFF(day, C_Date, CONVERT(CHAR(50), GETDATE(), 23)) < 45
union all
SELECT 'Open:' as 'Head', count(t1.TagID) 'count' FROM tblCalving t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE t1.C_Date > t2.I_Date and DATEDIFF(day, t1.C_Date, CONVERT(CHAR(50), GETDATE(), 23)) > 45
union all
SELECT 'Insemination Free:' as 'Head', count(t1.TagID) 'count' FROM tblAnimal t1 LEFT JOIN tblBreading t2 ON t1.TagID = t2.TagID WHERE t2.TagID IS NULL
)t
SELECT *,ROUND(CAST((CAST(Count AS DECIMAL)/(SELECT SUM(Count) FROM #Temp))*100 AS Float),2) 'Percentage'
FROM #Temp
DROP TABLE #Temp
