[Solved] SQL Server Error: Incorrect syntax near )

smile
 
on Sep 20, 2021 10:49 PM
493 Views

I am trying to union aonther query with this query like that

SELECT Head,Count
INTO #Temp
FROM
(
    SELECT 'Pregnant' AS 'Head',COUNT(TestID) 'Count'
    FROM
	(
    	SELECT ROW_NUMBER() OVER (PARTITION BY (TagID) ORDER BY C_Date DESC) Row_No,TestID,TagID,C_Date
    	FROM tblPregTest where P_Result='Positive'
    	GROUP BY TestID,TagID,C_Date
	)t
	WHERE Row_No = 1 AND t.TagID NOT IN (SELECT DISTINCT TagID FROM tblCalving)
	union all
	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

it is showing me error.

Msg 102, Level 15, State 1, Line 8 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Line 9 Incorrect syntax near ' '. Msg 102, Level 15, State 1, Line 23 Incorrect syntax near ')'.

How to get solution.

Download FREE API for Word, Excel and PDF in ASP.Net: Download
dharmendr
 
on Sep 21, 2021 06:46 AM

Can't write the query.

Just parsed your query and checked for error, but there is no error in the query which you shared.