从Sql表中计算不同的值

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

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是多余的。此查询不需要它们。