select agent,
case when qq is null then 0 else 16.67 end q1,
case when email is null then 0 else 16.67 end e1,
case when addr is null then 0 else 16.67 end a1,
case when birthdate is null then 0 else 16.67 end b1,
case when childname is null then 0 else 16.67 end c1,
case when buydate is null then 0 else 16.67 end y1from emc_srv_card 我要做资料完整度计算,如图,信息录入为空的字段为0,不为空为16.67
我要的结果如下:
agent 资料完整度
liwei 95.12%
李威 96.12%
admin 98.12%==================各位请帮帮忙,我一共才27分了。
case when qq is null then 0 else 16.67 end q1,
case when email is null then 0 else 16.67 end e1,
case when addr is null then 0 else 16.67 end a1,
case when birthdate is null then 0 else 16.67 end b1,
case when childname is null then 0 else 16.67 end c1,
case when buydate is null then 0 else 16.67 end y1from emc_srv_card 我要做资料完整度计算,如图,信息录入为空的字段为0,不为空为16.67
我要的结果如下:
agent 资料完整度
liwei 95.12%
李威 96.12%
admin 98.12%==================各位请帮帮忙,我一共才27分了。
只有计算结果,没有计算过程?
liwei 95.12%
李威 96.12%
admin 98.12%
晕 还没看懂 看看你怎么解决的select agent,cast(Convert(decimal(18,2),avg(y1)) as varchar(10))+'%' as avg1 from(
select agent,(case when (qq is null or qq='') then 0 else 16.667 end +
case when email is null or email='' then 0 else 16.667 end +
case when addr is null or addr='' then 0 else 16.667 end +
case when birthdate is null or birthdate='' then 0 else 16.667 end +
case when childname is null or childname='' then 0 else 16.667 end +
case when buydate is null or buydate='' then 0 else 16.667 end )y1 from emc_srv_card) t group by agent