Hi Friends,
My Table looks:
===========
create table billed
(
InVoice_no varchar(20),
model_ref varchar(20),
item_no int,
item_type char(20),
bill_qty int
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'-',
'5000320',
'I',
'250'
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'-',
'6000350',
'K',
'10'
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'6000350'',
'5000420',
'X',
'12'
)
insert into billed
(
InVoice_no,
model_ref,
item_no,
item_type,
bill_qty
)
values
(
'TN/0002/13-14',
'6000350'',
'5000450',
'X',
'3'
)
(like i ve 1000 records are there in my table)
here ITem_Type='I' Directly billed prodcuct,
ITem_Type='K' Group Product billed,
ITem_Type='X' item are available in that group
Now My Expecting o/p:
=================
invoice_no model_ref item_no item_type sales free
TN/0002/13-14 - 5000320 I 250 0
TN/0002/13-14 6000350 5000420 X 10 2
TN/0002/13-14 6000350 5000450 X 0 3
conditions for O/P:
-----------------
1)here example of 6000350 has billed for 10 items .but in that 5000420=12,5000450=3 already ve in that group.
the highest value in group has been show (like line no 2 of my expecting o/p.) sales =10(group of billed) remaining=2(free)
lowest value has shown (like line no 3 of my expecting o/p.)
sales =0(group of billed) Actual = 3.
How to Make code in sql server 2000?