Following is a view of my table...
以下是我的表格...
I am getting a hard time in getting desired result from a query.
我很难从查询中获得所需的结果。
My requirement looks like below image
我的要求如下图所示
It should be conditioned as follows -- 1)Within start date and end date.
它的条件如下 - 1)在开始日期和结束日期之内。
2)No as Pending .
2)没有待定。
3)Yes as Completed.
3)是,已完成。
4)No+Yes as Total.
4)否+是总计。
5)Based On only one survey Type.
5)仅基于一种调查类型。
This is what i have tried and got result for above mentioned 1 to 4 condition , but how to impliment 5 condition ?
这是我试过并得到上述1到4条件的结果,但是如何实现5条件?
SELECT DISTINCT Userid
,CASE
WHEN [YES] IS NULL
THEN 0
ELSE [YES]
END AS Completed
,CASE
WHEN [NO] IS NULL
THEN 0
ELSE [NO]
END AS Pending
,(
CASE
WHEN [YES] IS NULL
THEN 0
ELSE [YES]
END + CASE
WHEN [NO] IS NULL
THEN 0
ELSE [NO]
END
) AS Total
FROM (SELECT DISTINCT Userid
,SurveyStatus
,COUNT(ParcelId) AS cnt
FROM ParcelAllocationsurvivor
WHERE DateAllocated >= '2013-08-01'
AND DateAllocated <= '2013-08-07'
GROUP BY Userid
,SurveyStatus
) AS p
PIVOT(max(cnt) FOR surveystatus IN ([YES],[NO])) AS pvt
ORDER BY Userid
Can anybody help me out in it.
任何人都可以帮助我。
thanks in advance////
提前致谢////
1 个解决方案
#1
0
You would add a where
clause into the subquery:
您可以在子查询中添加where子句:
SELECT Userid, (case when [YES] is null then 0 else [YES] end) as Completed,
(case when [NO] is null then 0 else [NO] end) as Pending,
(case when [YES] is null then 0 else [YES] end +
case when [NO] is null then 0 else [NO] end
) as Total
FROM (SELECT Userid, SurveyStatus, COUNT(ParcelId) as cnt
FROM ParcelAllocationsurvivor
WHERE DateAllocated >= '2013-08-01' and DateAllocated <='2013-08-07' and
SurveyType = 'Survey1'
group by Userid, SurveyStatus
) AS p
PIVOT(max(cnt) FOR surveystatus IN([YES],[NO])) AS pvt order by Userid;
By the way, the select distinct
are redundant. You do not need them for this query.
顺便说一下,select distinct是多余的。此查询不需要它们。
#1
0
You would add a where
clause into the subquery:
您可以在子查询中添加where子句:
SELECT Userid, (case when [YES] is null then 0 else [YES] end) as Completed,
(case when [NO] is null then 0 else [NO] end) as Pending,
(case when [YES] is null then 0 else [YES] end +
case when [NO] is null then 0 else [NO] end
) as Total
FROM (SELECT Userid, SurveyStatus, COUNT(ParcelId) as cnt
FROM ParcelAllocationsurvivor
WHERE DateAllocated >= '2013-08-01' and DateAllocated <='2013-08-07' and
SurveyType = 'Survey1'
group by Userid, SurveyStatus
) AS p
PIVOT(max(cnt) FOR surveystatus IN([YES],[NO])) AS pvt order by Userid;
By the way, the select distinct
are redundant. You do not need them for this query.
顺便说一下,select distinct是多余的。此查询不需要它们。