drop table [asBatch]
CREATE TABLE [dbo].[asBatch](	[code] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[inv_no] [numeric](18, 0) NULL,	[inv_date] [datetime] NULL,	[deptno] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[qty_in] [numeric](18, 0) NULL,	[qty_out] [numeric](18, 0) NULL,	[qty_balance] [numeric](18, 0) NULL,	[cost] [numeric](18, 2) NULL, [iRowId] [numeric](18, 0) IDENTITY(1,1) NOT NULL) ON [PRIMARY]

INSERT INTO asbatch (code, inv_no, inv_date,deptno,qty_in,qty_out,qty_balance,cost)
(select code, inv_no, tr_date,deptno,tr_qty,0,(CASE tr_type when 'R' then 1 when 'C' then 2 else 0 end),unit_price + cost_other from asict   where tr_type in('R','C')) union all (select code, 00000, '01-jan-2011','OP',qty_open,0,0,cost_open from asicm   where qty_open>0)  order by tr_Date
drop table [asSalesBatch]

CREATE TABLE [dbo].[asSalesBatch](	[code] [varchar](1000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, [inv_no] [numeric](18, 0) NULL, [batch] [numeric](18, 0) NULL,	[inv_date] [datetime] NULL,	[deptno] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,	[qty_in] [numeric](18, 0) NULL,	[qty_out] [numeric](18, 0) NULL,	[qty_balance] [numeric](18, 0) NULL,	[cost] [numeric](18, 2) NULL,	[cost_avg] [numeric](18, 2) NULL,[iRowId] [numeric](18, 0) IDENTITY(1,1) NOT NULL) ON [PRIMARY]



INSERT INTO asSalesBatch (code, inv_no,batch, inv_date,deptno,qty_in,qty_out,qty_balance,cost,cost_avg)
(select code, inv_no,0, tr_date,deptno,tr_qty,0,it_slno,0,0 from asict   where tr_type='S')   order by tr_Date