SQLTeam.com | Weblogs | Forums

Need help with UNION of multiple queries

I'm trying to get the results of the following queries combined, but can't figure out how. Can someone please help?

SELECT  @quizCount = 0, @completedCount = COUNT(DISTINCT ac.assignmentId), @userId = userId
    FROM	dbo.assignments_completed ac
			INNER JOIN dbo.assignements a ON ac.assignmentId = a.assignmentId
	WHERE	userId IN (SELECT userId FROM users WHERE mgrUserId = @managerId) AND
			a.sourceType = @INDQUIZ AND 
			a.enabled = 1
	GROUP BY userId

	SET @CompletedTotal = @CompletedTotal + @completedCount;
	INSERT INTO @temp
	SELECT @quizCount, @CompletedTotal, @userId
	
	SELECT  @quizCount = 0, @completedCount = COUNT(DISTINCT ac.assignmentId), @userId = qc.userId 
    FROM	dbo.assignments_completed ac
			INNER JOIN dbo.quizzes_completed qc ON ac.assignmentId = qc.eventId
			INNER JOIN dbo.assignements a ON ac.assignmentId = a.eventId
	WHERE	qc.userId IN (SELECT userId FROM users WHERE mgrUserId = @managerId) AND
			a.sourceType = @GRPQUIZ AND 			
			a.enabled = 1
	GROUP BY qc.userId

	SET @CompletedTotal = @CompletedTotal + @completedCount;
	INSERT INTO @temp
	SELECT @quizCount, @CompletedTotal, @userId
	 
	SELECT  @quizCount = 0, @completedCount = COUNT(DISTINCT ac.assignmentId), @userId = qc.userId
    FROM	dbo.assignments_completed ac
			INNER JOIN dbo.quizzes_completed qc ON ac.assignmentId = qc.eventId
			INNER JOIN dbo.assignements a ON ac.assignmentId = a.eventId
	WHERE	qc.userId IN (SELECT userId FROM users WHERE mgrUserId = @managerId) AND
			a.sourceType = @SUMQUIZ AND 
			a.enabled = 1
	GROUP BY qc.userId

	SET @CompletedTotal = @CompletedTotal + @completedCount;
	INSERT INTO @temp
	SELECT @quizCount, @CompletedTotal, @userId

    SELECT DISTINCT u.userId, u.fname, u.lname, t.quizCount AS 'Current', t.completedCount AS 'Completed'
	FROM users u
	INNER JOIN @temp t ON u.userId = t.userId
	WHERE mgrUserId = @managerId

Thanks.

try

select statment1
union all
select statement2
union all
select statment3

Thanks, but that just gives the following error.

Variable assignment is not allowed in a statement containing a top level UNION, INTERSECT or EXCEPT operator.

With no sample data, it's definitely a shot in the dark, but here goes:

INSERT INTO @temp
    SELECT  0, COUNT(DISTINCT ac.assignmentId), userId
    FROM	dbo.assignments_completed ac
			INNER JOIN dbo.assignements a ON ac.assignmentId = a.assignmentId
	WHERE	userId IN (SELECT userId FROM users WHERE mgrUserId = @managerId) AND
			a.sourceType = @INDQUIZ AND 
			a.enabled = 1
	GROUP BY userId
UNION ALL
	SELECT  0, COUNT(DISTINCT ac.assignmentId), qc.userId 
    FROM	dbo.assignments_completed ac
			INNER JOIN dbo.quizzes_completed qc ON ac.assignmentId = qc.eventId
			INNER JOIN dbo.assignements a ON ac.assignmentId = a.eventId
	WHERE	qc.userId IN (SELECT userId FROM users WHERE mgrUserId = @managerId) AND
			a.sourceType = @GRPQUIZ AND 			
			a.enabled = 1
	GROUP BY qc.userId
UNION ALL
	SELECT  0, COUNT(DISTINCT ac.assignmentId), qc.userId
    FROM	dbo.assignments_completed ac
			INNER JOIN dbo.quizzes_completed qc ON ac.assignmentId = qc.eventId
			INNER JOIN dbo.assignements a ON ac.assignmentId = a.eventId
	WHERE	qc.userId IN (SELECT userId FROM users WHERE mgrUserId = @managerId) AND
			a.sourceType = @SUMQUIZ AND 
			a.enabled = 1
	GROUP BY qc.userId

SET @CompletedTotal = @CompletedTotal + @@ROWCOUNT;

INSERT INTO @temp
SELECT 0, @CompletedTotal, (SELECT TOP (1) userId FROM @temp) AS userId


    SELECT DISTINCT u.userId, u.fname, u.lname, t.quizCount AS 'Current', t.completedCount AS 'Completed'
	FROM users u
	INNER JOIN @temp t ON u.userId = t.userId
	WHERE mgrUserId = @managerId

Thanks for the reply Scott, that was close, but now I was just informed that the report is going to be changed!