Question

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

转换为以下？

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

可能不是最有效的答案，但是可以完成工作。

Answer 2

我已经修改了我的SQL，看起来像以下这样：可能有更干净和有效的方法来完成这个任务。我正在使用透视表来得到所需的结果。

抱歉代码很长。

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

SET @FirstDayOfWeek = dbo.[fn_GetFirstDayOfWeek](@Dates);
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

友情链接