The code below (see code that doesn't work) gives me a error "syntax error in union". Once I click ok it highlight the bracket in ") as Q1".
The strange thing is if I break this query apart then it works. There must be something wrong with my syntax surrounding the aliasing. I would like to keep it together if I can. Any help would be appreciated.
The strange thing is if I break this query apart then it works. There must be something wrong with my syntax surrounding the aliasing. I would like to keep it together if I can. Any help would be appreciated.
Code:
//code that doesn't work
SELECT
AssignmentPositions.Employee
FROM
[
SELECT Q1.Employee, Q1.StartDate, Min(Q1.EndDate) AS EndDate
FROM (
SELECT Position.Employee as Employee, Position.StartDate as StartDate, Position.EndDate as EndDate
FROM [Position]
UNION ALL
SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
FROM Assignment
) as Q1
GROUP BY Q1.Employee, Q1.StartDate
ORDER BY Q1.StartDate
]. AS AssignmentPositions
Code:
//Code that works
SELECT Position.Employee as Employee, Position.StartDate as StartDate, Position.EndDate as EndDate
FROM [Position]
UNION ALL
SELECT Assignment.Employee as Employee, Assignment.StartDate as StartDate, Assignment.EndDate as EndDate
FROM Assignment
) as Q1
GROUP BY Q1.Employee, Q1.StartDate
ORDER BY Q1.StartDate