Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. Sql Server 2008

Question

Hi,

I am having an issue with the below mentioned sql query. Basically I am trying to list all the system users that have captured data in the current week and previous week for each day.

Problem with my query below I get the "Only one expression can be specified in the select list when the subquery is not introduced with EXISTS. ". I have changed a part of this query to uses EXISTS but still get the same error on the last line of the subquery.

DECLARE @Dates date;
SET @Dates = '20091012';
DECLARE @FirstDayOfWeek date;
DECLARE @lastDayOfWeek date;

SET @FirstDayOfWeek = dbo.[fn_GetFirstDayOfWeek](@Dates);
SET @lastDayOfWeek = DATEADD(day, 6,@FirstDayOfWeek);

SET NOCOUNT ON
SELECT DISTINCT q.CapturerId, u.Firstname, u.Lastname INTO #Users
from Wop_Questionnaires q JOIN
        Frwk_Users u
    ON
        q.CapturerId = u.Id JOIN
    Core_Areas a  ON 
    u.X_Wop_ProvinceId = a.Id

WHERE u.X_Wop_ProvinceId = 5



SELECT (SELECT #Users.Firstname+' '+#Users.Lastname, COUNT(q.CapturerId) 
    from Wop_Questionnaires q  JOIN #Users on q.CapturerId = #Users.CapturerId 
    where EXISTS(SELECT #Users.CapturerID from #Users WHERE q.CapturerId = #Users.CapturerId) 
    AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',@FirstDayOfWeek),'19000101')
    And q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',@FirstDayOfWeek)+1,'19000101')
    GROUP BY q.CapturerId, #Users.Firstname,#Users.Lastname) Sun,
(SELECT #Users.Firstname+' '+#Users.Lastname, COUNT(q.CapturerId) 
    from Wop_Questionnaires q  JOIN #Users on q.CapturerId = #Users.CapturerId 
    where EXISTS (SELECT #Users.CapturerID from #Users WHERE q.CapturerId = #Users.CapturerId) 
    AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,1,@FirstDayOfWeek)),'19000101')
    AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,1,@FirstDayOfWeek))+1,'19000101')
    GROUP BY q.CapturerId, #Users.Firstname,#Users.Lastname) Mon,
(SELECT #Users.Firstname+' '+#Users.Lastname, COUNT(q.CapturerId) 
    from Wop_Questionnaires q  JOIN #Users on q.CapturerId = #Users.CapturerId 
    where EXISTS(SELECT #Users.CapturerID from #Users WHERE q.CapturerId = #Users.CapturerId) 
    AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,2,@FirstDayOfWeek)),'19000101')
    AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,2,@FirstDayOfWeek))+1,'19000101')
    GROUP BY q.CapturerId, #Users.Firstname,#Users.Lastname) Tue;


--;--

DROP TABLE #Users;
SET NOCOUNT OFF

Thanks Tebogo

Answer 1

Convert to the following?

SELECT... GROUP BY Sun 
UNION 
SELECT... GROUP BY Mon 
UNION 
SELECT ... GROUP BY Tue 

Probably not the most efficient answer, but should do the job.

Answer 2

Have revised my sql to look like the following: There's probably a much cleaner and efficient way to do this. I am using a Pivot to arrive the desired result.

Excuse the long code.

DECLARE @Dates date; SET @Dates = '20091015'; DECLARE @FirstDayOfWeek date; DECLARE @lastDayOfWeek date;

SET @FirstDayOfWeek = dbo.fn_GetFirstDayOfWeek; SET @lastDayOfWeek = DATEADD(day, 6,@FirstDayOfWeek);

SET NOCOUNT ON SELECT q.CapturerId, u.Firstname+' '+u.Lastname Name INTO #Userss from Wop_Questionnaires q JOIN Frwk_Users u ON q.CapturerId = u.Id JOIN Core_Areas a ON u.X_Wop_ProvinceId = a.Id

WHERE u.X_Wop_ProvinceId = 5 select #Userss.CapturerId from #Userss CREATE TABLE #Final(Name Varchar(50),cDay VARCHAR(50), NumRecs int);

INSERT INTO #Final(Name, cDay, NumRecs) SELECT #Userss.Name,'Sun' as cDay, q.CapturerId NumRecs from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where q.CapturerId IN(SELECT #Userss.CapturerID from #Userss) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',@FirstDayOfWeek),'19000101') And q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',@FirstDayOfWeek)+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

UNION ALL

SELECT #Userss.Name,'Mon' as cDay, q.CapturerId NumRecs
from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where EXISTS (SELECT #Userss.CapturerID from #Userss WHERE q.CapturerId = #Userss.CapturerId) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,1,@FirstDayOfWeek)),'19000101') AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,1,@FirstDayOfWeek))+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

UNION ALL

SELECT #Userss.Name,'Tue' cDay, q.CapturerId NumRecs from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where EXISTS(SELECT #Userss.CapturerID from #Userss WHERE q.CapturerId = #Userss.CapturerId) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,2,@FirstDayOfWeek)),'19000101') AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,2,@FirstDayOfWeek))+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

UNION ALL

SELECT #Userss.Name,'Wed' cDay, q.CapturerId NumRecs from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where EXISTS(SELECT #Userss.CapturerID from #Userss WHERE q.CapturerId = #Userss.CapturerId) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,3,@FirstDayOfWeek)),'19000101') AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,3,@FirstDayOfWeek))+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

UNION ALL

SELECT #Userss.Name,'Thur' cDay, q.CapturerId NumRecs from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where EXISTS(SELECT #Userss.CapturerID from #Userss WHERE q.CapturerId = #Userss.CapturerId) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,4,@FirstDayOfWeek)),'19000101') AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,4,@FirstDayOfWeek))+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

UNION ALL

SELECT #Userss.Name,'Fri' cDay, q.CapturerId NumRecs from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where EXISTS(SELECT #Userss.CapturerID from #Userss WHERE q.CapturerId = #Userss.CapturerId) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,5,@FirstDayOfWeek)),'19000101') AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,5,@FirstDayOfWeek))+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

UNION ALL

SELECT #Userss.Name,'Sat' cDay, q.CapturerId NumRecs from Wop_Questionnaires q JOIN #Userss on q.CapturerId = #Userss.CapturerId where EXISTS(SELECT #Userss.CapturerID from #Userss WHERE q.CapturerId = #Userss.CapturerId) AND q.CaptureDate >=DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,6,@FirstDayOfWeek)),'19000101') AND q.CaptureDate < DATEADD(DAY,DATEDIFF(DAY,'19000101',DATEADD(DAY,6,@FirstDayOfWeek))+1,'19000101') GROUP BY #Userss.Name, q.CapturerId

--SELECT * from #Final; --;--*/

SELECT Name, [Sun] as [Sun], [Mon] as [Mon], [Tue] as [Tue], [Wed] as [Wed], [Thur] as [Thur], [Fri] as [Fri], [Sat] as [Sat], [Sun]+[Mon]+[Tue]+[Wed]+[Thur]+[Fri]+[Sat] as Total FROM (SELECT Name, cDay, NumRecs FROM #Final) as Src PIVOT (Count(NumRecs) For cDay IN ([Sun],[Mon],[Tue],[Wed],[Thur],[Fri],[Sat])) as Pvt ORDER BY Name

DROP TABLE #Userss; DROP TABLE #Final; SET NOCOUNT OFF