iif(cSignAttrib = '退货' ,SUM(FA_SignInfo.iAccountP),0 ) AS 退货,
把这句去掉就好了,可是我需要统计这个数字。
当 cSignAttrib 字段为 退货 的时候,我需要把 iAccountP 字段进行求和。
还能不能用其他方式实现呢?上面的语句在access里面通过,可是sql server 里面通过不了
把这句去掉就好了,可是我需要统计这个数字。
当 cSignAttrib 字段为 退货 的时候,我需要把 iAccountP 字段进行求和。
还能不能用其他方式实现呢?上面的语句在access里面通过,可是sql server 里面通过不了
不支持iif()
a.cDeptNo,
a.cClassNo,
a.cClassName,
a.istandard,
a.dStartTime,
a.dEndTime,
b.cSignAttrib,
SUM(b.iRatingP) AS iRatingP之Sum,
b.iInsertP,
SUM(b.iRebateP) AS iRebateP之Sum,
SUM(b.iAccountP) AS iAccountP之Sum,
SUM(b.iBackP) AS iBackP之Sum,
SUM(case cSignAttrib when '退货' then b.iAccountP else 0 end) AS 退货,
SUM(b.iRatingP)
- b.iInsertP - SUM(b.iRebateP)
- ISNULL(SUM(b.iBackP), 0) AS 非退货,
b.dSigTime
FROM
dbo.BS_ClassInfo a
INNER JOIN
dbo.FA_SignInfo b
ON
a.cClassNo = b.cClassNo
GROUP BY
a.cClassNo, a.cClassName,
a.istandard, a.dStartTime,
a.dEndTime, b.cSignAttrib, b.iInsertP,
b.dSigTime, a.cDeptNo
having
a.dStartTime<='2006-1-31 23:00:00' AND a.dEndTime>='2006-1-1' AND b.cSignAttrib<>'转走' AND b.dSigTime<'2006-1-31 23:00:00'
sum(case cSignAttrib when '退货' then FA_SignInfo.iAccountP else 0 end)
sum(case when iRebateP<0 then 1 else 0 end) AS 退货个数