query result into new table - bit messy

[xponcall]

I have the following, and need to be convert and insert into a new table. This could be done in a some sort of reporting tool, but for now this what I need.

Server: SQL 2000
------------------
table1

Acct	Date	Tran	Qty
101	10/1/2008	A	500
101	10/2/2008	A	1000
101	10/3/2008	A	200
102	10/5/2008	A	500
102	10/6/2008	A	500
101	11/1/2008	S	200
101	11/2/2008	S	1000
101	11/3/2008	S	200
101	11/4/2008	S	400
102	11/5/2008	S	700

table2 - Need something like this: Base on the Date of Inventory A(Acquire), and S(Ship). Eg: Account 101 had 500 on 10/1/2008,
but only 200 was ship on 11/1/2008 which result have 300. left. The 300 will be use to substract from the next ship date.

Acct	bDate	bTran	bQty	sDate	sTran	sQty
101	10/1/2008	A	200	11/1/2008	S	200
101	10/1/2008	A	300	11/2/2008	S	300
101	10/2/2008	A	700	11/2/2008	S	700
101	10/2/2008	A	200	11/3/2008	S	200
101	10/2/2008	A	100	11/4/2008	S	100
101	10/3/2008	A	200	11/4/2008	S	200
101	*	*	*	11/4/2008	S	100
102	10/5/2008	A	500	11/5/2008	S	500
102	10/6/2008	A	200	11/5/2008	S	200
102	10/6/2008	A	300	*	*	*

I been struggle with this all morning, and wonder if anyone have a pointer get this started.

thanks

 

[PWise]

how about this

Select Acct, Sum(Case when tran="a" qty else -qty enf )

from table
Group by Acct

 

[xponcall]

Hi,

thank you very much. base on your code, it will return the result...but for some reason the client want to display each result out by line. Not sure of reason...but that what they want.

create table #tmpInvTbl( acct int, [date] datetime,[tran] char(1), qty int)

insert into #tmpInvTbl
select 101,	'10/1/2008','A',	500 union all
select 101,	'10/2/2008','A',	1000 union all
select 101,	'10/3/2008','A',	200 union all 
select 102,	'10/5/2008','A',	500 union all
select 102,	'10/6/2008','A',	500 union all
select 101,	'11/1/2008','S',	200 union all
select 101,	'11/2/2008','S',	1000 union all
select 101,	'11/3/2008','S',	200 union all
select 101,	'11/4/2008','S',	400 union all
select 102,	'11/5/2008','S',	700

select * from #tmpInvTbl

Select Acct, Sum(Case when [tran]='a' then qty else -qty end )

from #tmpInvTbl
Group by Acct 

display reesult
Acct	(No column name)
101	-100
102	300

drop table #tmpInvTbl