Rocky Rocky

Rocky Rocky

  • NA
  • 317
  • 151.8k

How to update the particular line in procedure of sql server

Jan 23 2014 4:48 AM
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.....