Error message is:
Conversion failed when converting the varchar value ‘Rejected’ to data type int
and SSMS says its occurring on line 65, which would be line 5 in this excerpt.
There’s more code above, but figured I’d start here:
CREATE TABLE #excepts (Id INT, [Timestamp] DATETIME, [ShiftName] INT, [MessageBody] NVARCHAR(500))
INSERT INTO #excepts
EXEC (@sqlCommand);
SELECT d.ShiftName, d.Exception, d.[Accepted/Rejected], COUNT(*) AS 'Amount'
FROM (
SELECT els.[Timestamp], els.[ShiftName], 'Assign' AS 'Exception', 'Accepted' AS 'Accepted/Rejected'
FROM #excepts els
WHERE els.MessageBody LIKE '%entered Assign.'
AND els.Id + 6 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Accepted%'
))d
GROUP BY
d.[ShiftName],
d.[Exception],
d.[Accepted/Rejected]
UNION ALL
SELECT els.[Timestamp], els.[ShiftName], 'Assign', 'Rejected'
FROM #excepts els
WHERE els.MessageBody LIKE '%entered Assign%'
AND els.Id + 6 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Ignored%'
)
UNION ALL
SELECT els.[Timestamp], els.[ShiftName], 'FULL', 'accepted'
FROM #excepts els
WHERE els.MessageBody LIKE '%entered FULL%'
AND els.Id + 4 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Accepted%'
)
UNION ALL
SELECT els.[Timestamp], els.[ShiftName], 'FULL', 'rejected'
FROM #excepts els
WHERE els.MessageBody LIKE '%entered FULL%'
AND els.Id + 4 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Ignored%'
)
UNION ALL
SELECT els.[Timestamp], els.[ShiftName], 'Arrive', 'accepted'
FROM #excepts els
WHERE els.MessageBody LIKE '%entered Arrive.%'
AND els.Id + 6 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Accepted%'
)
UNION ALL
enter image description here
New contributor
Kenneth LeRoy is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
6
It looks like you have your UNION ALLs in the wrong place.
Your indentation is misleading. Look at the parenthesis. Your first (outer) unioned query is yielding ShiftName, Exception, [Accepted/Rejected], and Amount, while the remaining unioned queries are yielding Timestamp, ShiftName, unnamed-literal-string, and unnamed-literal-string. Amount is an int in the first query, while the matching unnamed literal string 'Rejected' in the second query is not.
Try moving the ) d and GROUP BY to the end of the entire query.
SELECT d.ShiftName, d.Exception, d.[Accepted/Rejected], COUNT(*) AS Amount
FROM (
SELECT els.Timestamp, els.ShiftName, 'Assign' AS Exception, 'Accepted' AS [Accepted/Rejected]
FROM #excepts els
WHERE els.MessageBody LIKE '%entered Assign.'
AND els.Id + 6 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Accepted%'
)
UNION ALL
SELECT els.[Timestamp], els.[ShiftName], 'Assign', 'Rejected'
FROM #excepts els
WHERE els.MessageBody LIKE '%entered Assign%'
AND els.Id + 6 IN (
SELECT Id
FROM #excepts el
WHERE el.MessageBody LIKE '%Ignored%'
)
UNION ALL
...
) d
GROUP BY
d.ShiftName,
d.Exception,
d.[Accepted/Rejected]
See this db<>fiddle for a demo.
1