How Do I Solve This Query

See more:

SQL-Server-2008R2

I have a table like this

create table #tbl_AdvisorCommissions(advisor_id int,commission_amount float,created_date datetime)
insert into #tbl_AdvisorCommissions values(143,200,'2014-10-13')
insert into #tbl_AdvisorCommissions values(143,650,'2014-10-14')
insert into #tbl_AdvisorCommissions values(143,200,'2014-10-28')
insert into #tbl_AdvisorCommissions values(143,300,'2014-11-03')
insert into #tbl_AdvisorCommissions values(143,350,'2014-11-05')

create table #tbl_extra_AdvisorCommissions(advisor_id int,commission_amount float,created_date datetime)
insert into #tbl_extra_AdvisorCommissions values(143,650,'2014-10-14')

create table #tbl_weeklyCommissions(advisor_id int,commission_amount float,bleft bigint,bright bigint,aleft bigint,aright bigint,from_date datetime,to_date datetime)
insert into #tbl_weeklyCommissions values(143,250,635750,2500,633250,0,'2014-10-08','2014-10-15')
insert into #tbl_weeklyCommissions values(143,100,635750,1000,655750,0,'2014-10-16','2014-10-22')
insert into #tbl_weeklyCommissions values(143,20,694750,250,694550,50,'2014-10-23','2014-10-29')
insert into #tbl_weeklyCommissions values(143,350,755300,3550,751800,50,'2014-10-30','2014-11-06')

select * from #tbl_AdvisorCommissions
select * from #tbl_extra_AdvisorCommissions
select * from #tbl_weeklyCommissions

I am using this query

Create proc [dbo].[sp_test]
@from_date nvarchar(max),
@to_date nvarchar(max),
@user_code nvarchar(max)
as
begin
declare @advid nvarchar(max);
set @advid = @user_code
begin
with cte  as 
(select advisor_id,case when coalesce(SUM(commission_amount),0)<=0 then 0 else SUM(commission_amount) end as comm 
from  #tbl_AdvisorCommissions where advisor_id=''+@advid+'' and 
created_date between @from_date and @to_date 
group by advisor_id union select advisor_id,case when coalesce(SUM(commission_Amount),0)<=0 
then 0 else SUM(commission_Amount) end as comm from  #tbl_extra_AdvisorCommissions where 
advisor_id=''+@advid+'' and created_date 
between @from_date and @to_date 
group by advisor_id),
re as
(
select advisor_id,case when bleft<=aleft then coalesce((aleft-bleft),0) 
when bleft>aleft then  coalesce((bleft-aleft),0) end as wleft,case when bright<=aright then coalesce((aright-bright),0)
when bright>aright then coalesce((bright-aright),0) end as wright,commission_amount as wcom  from #tbl_weeklyCommissions where advisor_id=''+@advid+''
and from_date>=@from_date and to_date<=@to_date
)
select cte.advisor_id,comm as rcomm,
sum(re.wleft) as a,sum(re.wright) as b,sum(re.wcom)as c
from cte right join re on cte.advisor_id=re.advisor_id
 group by cte.advisor_id,comm
 end
end

execute sp_test '2014-10-08','2014-11-06','143'

This my output when i am using this query:

advisor_id | rcomm | a | b | c
143 | 650 | 26200 | 7200 | 720
143 | 1700 | 26200 | 7200 | 720

I need a Output like this:

advisor_id |    rcomm  | a      |   b    |  c

143    |     2350|  26200 |       7200 |  720

when i sum the value of comm in cte it give wrong value,
what is the correction i want to do in this query .
give your suggestion!.
Thank you!...