How to get net sales from these table?
Hi Friends,
I ve the table like
Create table nh
(
invoice_no varchar(20),
model_ref varchar(20),
item_no int,
item_type varchar(20),
invoice_qty int
inv_date datetime
)
insert into nh(invoice_no,model_ref,item_no,item_type,invoice_qty,inv_date)
values('tn/002/13-14','#','600032','K','150','01-04-2013')
insert into nh(invoice_no,model_ref,item_no,item_type,invoice_qty,inv_date)
values('tn/002/13-14','600032','5000399','X','180','01-04-2013')
in above these example
invoice_no='tn/002/13-14' type='K' means kit it holds some item value with free.
Model_ref is item_no referred to that KIT (I.E ITEM_TYPE='X') Holds some original value
now my expecting o/p:
invoice_no item_no sales free
tn/002/13-14 5000399 150 30
(i.e kit value has been taken sales model_ref item value total value - kit value=free)
how to that?