User456628452 posted
i am having stored procedure, where i have added union all and group by. i am getting error, incorrect syntax near END, expecting as ID or QUOTED_ID, need to generate a report like this
CREATE PROCEDURE dbo.StaffTaskReport
-- Add the parameters for the stored procedure here
@StartDate dateTime,
@EndDate dateTime,
@SectionId int
AS
BEGIN
SET NOCOUNT ON;
SELECT users,
SUM(CompletedOnTime) AS CompletedOnTime,
SUM(CompletedOverDue) AS CompletedOverDue,
SUM(InProgress) AS InProgress,
SUM(OverDue) AS OverDue,
SUM(Hold) AS Hold
FROM
(
SELECT c.NameEN AS users, COUNT(a.Id) AS CompletedOnTime, 0 AS CompletedOverDue, 0 AS InProgress,0 AS OverDue, 0 AS Hold
FROM Individuals AS c INNER JOIN
Employments AS d ON c.Id = d.IndividualId INNER JOIN
Users AS b ON c.Id = b.IndividualId INNER JOIN
TaskItems AS a ON d.Id = a. EmploymentId
WHERE (a.TaskDueDate IS NOT NULL) AND (a.CompletedDate <= a.TaskDueDate) AND (a.CompletedDate IS NOT NULL) and (a.CompletedDate between @StartDate and @EndDate)
GROUP BY c.NameEN
UNION ALL
SELECT c.NameEN as users, 0 AS CompletedOnTime, COUNT(a.Id) AS CompletedOverDue, 0 AS InProgress,0 AS OverDue, 0 AS Hold
FROM Individuals AS c INNER JOIN
Employments AS d ON c.Id = d.IndividualId INNER JOIN
Users AS b ON c.Id = b.IndividualId INNER JOIN
TaskItems AS a ON d.Id = a.EmploymentId
WHERE (a.TaskDueDate IS NOT NULL) AND (a.CompletedDate > a.TaskDueDate) AND (a.CompletedDate IS NOT NULL) AND (a.CompletedDate between @StartDate and @EndDate)
GROUP BY c.NameEN
UNION ALL
SELECT c.NameEN as users, 0 AS CompletedOnTime,0 AS CompletedOverDue,COUNT(a.Id) AS InProgress,0 AS OverDue, 0 AS Hold
FROM Individuals AS c INNER JOIN
Employments AS d ON c.Id = d.IndividualId INNER JOIN
Users AS b ON c.Id = b.IndividualId INNER JOIN
TaskItems AS a ON d.Id = a.EmploymentId
WHERE (a.TaskStateId = 2) and (a.OwnerAssignedDate between @StartDate and @EndDate)
GROUP BY c.NameEN
UNION ALL
SELECT c.NameEN as users, 0 AS CompletedOnTime,0 AS CompletedOverDue,0 AS InProgress,COUNT(a.Id) AS OverDue, 0 AS Hold
FROM Individuals AS c INNER JOIN
Employments AS d ON c.Id = d.IndividualId INNER JOIN
Users AS b ON c.Id = b.IndividualId INNER JOIN
TaskItems AS a ON d.Id = a.EmploymentId
WHERE (a.TaskDueDate IS NOT NULL) AND (a.TaskDueDate > GetDATE()) AND (a.TaskDueDate between @StartDate and @EndDate)
GROUP BY c.NameEN
UNION ALL
SELECT c.NameEN as users, 0 AS CompletedOnTime,0 AS CompletedOverDue,0 AS InProgress,0 AS OverDue, COUNT(a.Id) AS Hold
FROM Individuals AS c INNER JOIN
Employments AS d ON c.Id = d.IndividualId INNER JOIN
Users AS b ON c.Id = b.IndividualId INNER JOIN
TaskItems AS a ON d.Id = a.EmploymentId
WHERE (a.TaskDueDate IS NOT NULL) AND (a.TaskDueDate > GetDATE()) AND (a.TaskDueDate between @StartDate and @EndDate)
GROUP BY c.NameEN
)
END
GO
| |
Completed on Time |
Hold |
Pending |
Completed After Due Date |
OverDue |
| Employ A |
5 |
1 |
2 |
2 |
0 |
| Employ B |
5 |
0 |
2 |
1 |
0 |
| Employ C |
8 |
0 |
4 |
2 |
0 |
| Employ D |
9 |
0 |
2 |
1 |
0 |
| Employ E |
9 |
1 |
2 |
2 |
0 |
stored procedure error, incorrect syntax
