inventorycheckall.sql

提供商业企业进销存、成本核算、销售人员工资考核结算、地区管理报表（特别是二维报表）

 select a.sgoods_code,a.sgoods_name,a.strademark,a.sclass,
         a.stype,a.sstock_code, isnull(n.sumbgty,0)  as ssumbgty,isnull(b.inqty,0)  as sinqty,
         isnull(d.move_in,0)  as smove,isnull(c.outqty,0)  as soutqty,isnull(e.move_out,0)  as smove_out,
        (isnull(n.sumbgty,0) + isnull(b.inqty,0) + isnull(d.move_in,0) - isnull(c.outqty,0) - isnull(e.move_out,0)) as sendqty
  from  
 (select goods_dict.goods_code as sgoods_code,goods_dict.goods_name as sgoods_name,
         goods_dict.trademark as strademark,goods_dict.class as sclass,
         goods_dict.type as stype,stock_dict.stock_code  as sstock_code  
 from stock_dict, goods_dict)   as a 
  left outer join
  (SELECT bill_det.goods_code, bill_mst.stock_code,  
          sum(isnull(bill_det.qty,0)) as inqty   
    FROM bill_det,   
         bill_mst  
   WHERE ( bill_det.bill_no = bill_mst.bill_no )           and  
         ( bill_mst.bill_date between '2006-01-10'  and '2006-12-10' )   and
         (charge_indicator is null or charge_indicator=1)  and
          bill_mst.type='in'   
         group by bill_det.goods_code, bill_mst.stock_code) as  b
  on a.sgoods_code=b.goods_code and b.stock_code=a.sstock_code
  left outer join 
    (SELECT bill_det.goods_code, bill_mst.stock_code,   
          sum(isnull(bill_det.qty,0)) as outqty   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )           and  
         ( bill_mst.bill_date between '2006-01-10'  and '2006-12-10' )    and
          bill_mst.bill_rp<>'04'                            and
         (charge_indicator is null or charge_indicator=1)   and
          bill_mst.type='ou'   
          group by bill_det.goods_code, bill_mst.stock_code)  as c
   on a.sgoods_code=c.goods_code and c.stock_code=a.sstock_code
   left outer join 
    (SELECT bill_det.goods_code,bill_mst.stock_out as stock_code,    
          sum(isnull(bill_det.qty,0)) as move_in   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )           and  
         ( bill_mst.bill_date between '2006-01-10'  and '2006-12-10' )    and
          bill_mst.bill_rp='04'                             and
         (charge_indicator is null or charge_indicator=1)   and
          bill_mst.type='mo'                                
          group by bill_det.goods_code, bill_mst.stock_out)  as d
   on a.sgoods_code=d.goods_code and d.stock_code=a.sstock_code 
   left outer join 
    (SELECT bill_det.goods_code, bill_mst.stock_code,   
          sum(isnull(bill_det.qty,0)) as move_out   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )          and  
         ( bill_mst.bill_date between '2006-01-10'  and '2006-12-10' )   and
          bill_mst.bill_rp='04'                            and
         (charge_indicator is null or charge_indicator=1)  and
          bill_mst.type='mo'   
          group by bill_det.goods_code, bill_mst.stock_code) as  e
 on a.sgoods_code=e.goods_code and e.stock_code=a.sstock_code 
 left outer join
   (select m.goods_code,vstock , 
        sum(isnull(m.bqty,0)) as  sumbgty
    from
   (SELECT bill_det.goods_code,bill_mst.stock_code  as vstock  ,
          sum(isnull(bill_det.qty,0)) as bqty   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )          and  
         ( bill_mst.bill_date <'2006-01-10'  )                    and
         (charge_indicator is null or charge_indicator=1)  and
          bill_mst.type='in' 
         group by  bill_det.goods_code,bill_mst.stock_code
    union
    SELECT bill_det.goods_code, bill_mst.stock_code  as vstock  ,   
          sum(isnull( 0 - bill_det.qty,0)) as bqty   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )          and  
         ( bill_mst.bill_date <'2006-01-10'  )                    and
          bill_mst.bill_rp<>'04'                           and
         (charge_indicator is null or charge_indicator=1)  and
          bill_mst.type='ou' 
        group by  bill_det.goods_code ,bill_mst.stock_code 
    union
    SELECT bill_det.goods_code,bill_mst.stock_out as vstock ,    
        sum(isnull(bill_det.qty,0)) as bqty   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )           and  
         ( bill_mst.bill_date <'2006-01-10'  )                     and
          bill_mst.bill_rp='04'                             and
         (charge_indicator is null or charge_indicator=1)   and
          bill_mst.type='mo'  
        group by  bill_det.goods_code ,bill_mst.stock_out
    union
    SELECT bill_det.goods_code, bill_mst.stock_code as vstock,   
        sum(isnull(-bill_det.qty,0)) as bqty   
    FROM bill_det,   
         bill_mst  
    WHERE ( bill_det.bill_no = bill_mst.bill_no )           and  
         ( bill_mst.bill_date <'2006-01-10'  )                     and
          bill_mst.bill_rp='04'                             and
         (charge_indicator is null or charge_indicator=1)   and
          bill_mst.type='mo' 
       group by  bill_det.goods_code,bill_mst.stock_code
   ) m 
       group by m.goods_code,m.vstock
   ) n
on a.sgoods_code=n.goods_code and  n.vstock=a.sstock_code
where (isnull(n.sumbgty,0)<>0 or isnull(b.inqty,0)<>0 or isnull(d.move_in,0)<>0  or  isnull(c.outqty,0)<>0 or isnull(e.move_out,0)<>0  
or  (isnull(n.sumbgty,0) + isnull(b.inqty,0) + isnull(d.move_in,0) - isnull(c.outqty,0) - isnull(e.move_out,0))<>0)
order by a.sgoods_code,a.sstock_code