日期:2014-05-17 浏览次数:20528 次
select 'x' as pjlx,lszh,spdm
,dwdm=(case when substring(dwdm,1,3)='YF' then 'AA-23' else dwdm end)
,dwmc,mxjls,xcljls,kcsjls,zhsmsj
,zfbz=(case when minclbz<-1 then 'y' else 'n' end)
,xclbz=(case when xcljls>0 then 'y' else 'n' end)
,kcsbz=(case when smlxs=1 and kcsjls>0 and kcsjls=xcljls then 'y' else 'n' end)
,smlx=(case when smlxs=1 and maxsmlx in ('销售出库','退货入库','抽检出库','销毁出库')then maxsmlx else '待定' end)
from(
select lszh,spdm,dwdm,dwmc,max(smsj) as zhsmsj,max(isnull(smlx,'')) as maxsmlx
,min(isnull(clbz,0)) as minclbz,count(*) as mxjls
,count(case when isnull(clbz,0) in (0,1,2) then lszh else null end) as xcljls
,count(case when isnull(clbz,0)=2 and isnull(smlx,'')in('销售出库','退货入库','抽检出库','销毁出库') then lszh else null end) as kcsjls
,count(distinct (case when isnull(clbz,0)<0 then null else isnull(smlx,'') end)) as smlxs
from lspy_jj_wlspy with(nolock)
where lszh not in (
select lszh from (
select distinct lszh,spdm,dwdm,dwmc
,kzbz=(case when isnull(spdm,'')='' or isnull(dwdm,'')='' or isnull(dwmc,'')='' then 1 else 0 end)
from lspy_jj_wlspy with(nolock) where isnull(clbz,0)>=0
)x
group by lszh having (count(*)>1 or max(kzbz)=1)
)
group by lszh,spdm,dwdm,dwmc having max(smsj)<convert(varchar(10),getdate(),126)
)y