日期:2014-05-17 浏览次数:20663 次
CREATE view [dbo].[v_ypjg_main_wypjg]
as
select 'x' as pjlx,lszh,spdm
,dwdm=(case when substring(dwdm,1,3)='DYF' then '80-PP' 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 ypjg_xs_wypjg 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 ypjg_xs_wypjg 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
union all
select 'g' as pjlx,lszh,spdm
,dwdm=(case when substring(dwdm,1,3)='DYF' then '80-PP' 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 ypjg_gj_wypjg wi