February 14, 2012 at 5:55 pm
Hi,
I have table Student with these columns names
student_code , stud_act_id , fee , discount_rate
A 52165 200 60
A 54902 300 50
A 54167 100 30
B 54165 60
B 54167 30
B 54902 50
I want query to display the values like:
Here Discount Fee value takes from the student_code fee and discount_rate .
Discount Fee: (200-(60+(60*10)/100)).
Discount Fee: (300-(50+(50*10)/100)).
Discount Fee: (100-(30+(30*10)/100)).
The discount Fee value will taking form the student_code of A of discount_rate and fee and result append to the Student_code B
The out put should like this:
student_code , stud_act_id , fee , discount_rate
A 52165 200 60
A 54902 300 50
A 54167 100 30
B(Discount Fee:134) 52165 60
B(Discount Fee:245) 54902 50
B(Discount Fee:67) 54902 50
Please give me solution for this query....
Thanks
February 14, 2012 at 6:40 pm
create table #Student (
student_code char(1) not null,
stud_act_id int null,
fee decimal(19,4) null,
discount_rate decimal(19,4) null
)
insert into #Student
select 'A', 52165, 200, 60 union all
select 'A', 54902, 300, 50 union all
select 'A', 54167, 100, 30 union all
select 'B', 54165, null, 60 union all
select 'B', 54167, null, 30 union all
select 'B', 54902, null, 50
select
b.student_code
,b.stud_act_id
,DiscountFee=a.fee-(a.discount_rate+(a.discount_rate*10)/100)
,b.discount_rate
from #Student b
inner join #Student a on a.discount_rate = b.discount_rate
and a.student_code = 'a' and b.student_code = 'b'
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply