How to Calculate Sales & Free For these Query?

Posted by Raghuldrag under Sql Server on 1/21/2014 | Points: 10 | Views : 629 | Status : [Member] | Replies : 0
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?




Responses

(No response found.)

Login to post response