How to Make code For These Query?
Dear Friends,
i ve the table like
create table invoice
(
invoic_no varchar(25),
item_no int,
invoic_qty int,
inv_date datetime
)
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0002/13-14','A12','25','03-04-2013')
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0002/13-14','A13','04','03-04-2013')
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0004/13-14','A13','25','03-04-2013')
insert into invoice(invoic_no,item_no,invoic_qty,inv_date)
values('tn/0004/13-14','A15','04','03-04-2013')
Here I need The O/P like:
Invoic_no Item_no Sales Free
tn/0004/13-14 A13 25 0
tn/0004/13-14 A15 0 4
tn/0002/13-14 A12 25 0
tn/0002/13-14 A13 0 4
MY expecting O/p is both item are billed in same invoice where the max qty is to display on'SALES'(I.e tn/0004/13-14 A13 25 )
Minimum Value is to display on Free?