How to update the particular line in procedure of sql server
i ve the table like
struct:
------
create table inv_tab
(
company_no char(50),
invoice_no varchar(50),
model_ref varchar(20),
item_type char(10),
item_no varchar(35),
inv_qty int
)
insert into inv_tab
(
company_no,
invoice_no,
model_ref,
item_type,
item_no,
inv_qty
)
values
(
'x',
'tr/002/13-14',
'##',
'K',
'6000352',
'50'
)
values
(
'x',
'tr/002/13-14',
'6000352',
'X',
'5000350',
'65'
)
values
(
'x',
'tr/002/13-14',
'6000352',
'x',
'5000342',
'5'
)
values
(
'x',
'tr/004/13-14',
'##',
'K',
'6000350',
'25'
)
values
(
'x',
'tr/004/13-14',
'6000350',
'X',
'5000050',
'35'
)
now i wanna display the o/p like
company invoice_no model_ref item_no sales free
x tr/002/13-14 6000352 5000350 50 15
x tr/002/13-14 6000352 5000342 0 05
x tr/004/13-14 6000350 5000050 25 35
Here the conditions are
1) item_type = 'K' is that product group
2)item_type='x' is referenced item in that group
when i show the sales & free in my o/p wrote query
select
a.company_no,
a.invoice_no,
a.item_no as model_ref,
b.item_no
a.invoice_qty as sales,
b.invoice_qty - a.invoice_qty as free
from
inv_tab a
inner join
inv_tab b
where
a.company_no=b.company_no
and
a.item_no=b.model_ref
and
a.invoice_no=b.invoice_no
its giving the o/p:
company invoice_no model_ref tem_no sales free
x tr/002/13-14 6000352 5000350 50 15
x tr/002/13-14 6000352 5000342 50 -45
x tr/004/13-14 6000350 5000050 25 35
how to change my code as my expecting o/p.....