| 解决方案： |
--先用下面语句添加缺少的部分记录：
insert into ia_individual (cinid,coutid,cvouchcode,cquantity,cissale,cvoutype)
(select cbatchcode, id, Null, isnull(iaoutquantity,iainquantity), case when cvoutype in ('26','27','28','29')
then 1 else 0 end, case when brdflag=1 then 2 else case when isnull(iaoutprice,0)>0 then 0 else 1 end end
from ia_subsidiary left join warehouse on warehouse.cwhcode=ia_subsidiary.cwhcode
where cwhvaluestyle='个别计价法'
and not cbatchcode is null and not id in (select coutid from ia_individual)
and not ia_subsidiary.cvoutype in ('24','30') )
update ia_individual set cvoutype='2' from ia_individual a left join ia_subsidiary b on a.coutid=b.id and case when b.cvoutype in ('26','27','28','29') then 1 else 0 end=a.cissale where a.cvoutype<>'2' and b.brdflag=1
update ia_individual set cinid=c.autoid from ia_individual a left join ia_subsidiary b on a.cinid=b.autoid left join ia_subsidiary c on b.id=c.id and c.cvoutype='01' where b.cvoutype='30'
--查找到出库大于入库的记录
select * from ia_individual where cinid in (select autoid from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join
(select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcode
where outnum>innum)
--可以用下面语句找到个别记价成本分配错误数据
select * from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join
(select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcode
where outnum>innum
--用下面的语句找到cbatchcode错误的明细帐入库记录记录。
select cbatchcode,* from ia_subsidiary where autoid in (select autoid from (select autoid,sum(isnull(iainquantity,0)) as innum from ia_subsidiary where brdflag=1 group by autoid) rdflag1 left join
(select cbatchcode ,sum(isnull(iaoutquantity,0)) as outnum from ia_subsidiary where brdflag=0 group by cbatchcode) rdflag2 on rdflag1.autoid=rdflag2.cbatchcode
where outnum>innum)
|