如何解决此查询 [英] How Do I Solve This Query

问题描述

我有一张这样的桌子

 创建  table  #tbl_AdvisorCommissions（advisor_id  int ，commission_amount  float ， created_date  datetime ）
  insert   into  #tbl_AdvisorCommissions 值（ 143 ， 200  ，'  2014-10-13'）
 将插入 #tbl_AdvisorCommissions 值（ 143 ， 650 ，'  2014-10- 14'）
  insert   into  #tbl_AdvisorCommissions  values （ 143 ， 200 ，'  2014-10-28'）
 插入 进入 #tbl_AdvisorCommissions 值（ 143 ， 300 ，'  2014-11-03'）
  insert   into  #tbl_AdvisorCommissions 值（ 143 ， 350 ， '  2014-11-05'）
 
  create   table  #tbl_extra_AdvisorCommissions（advisor_id  int ，commission_amount  float ，created_date  datetime ）
 插入 进入 #tbl_extra_AdvisorCommissions 值（ 143 ， 650 ，'  2014-10-14'）
 
 创建 表 #tbl_weeklyCommissions（advisor_id  int ，commission_amount  float ，bleft  bigint ，明亮 bigint ，aleft  bigint ，aright  bigint ，from_date  datetime ，to_date  datetime ）
 插入 进入 #tbl_weeklyCommissions 值（ 143  ， 250 ， 635750 ， 2500 ，< span class =code-digit> 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'）
 插入 进入 #tbl_weeklyCommissions 值（ 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'）
 
 选择 * 来自 #tbl_AdvisorCommissions 
 选择 * 来自 #tbl_extra_AdvisorCommissions 
 选择 * 来自 #tbl_weeklyCommissions 

我正在使用此查询

 创建  proc  [dbo]。[sp_test] 
  @ from_date   nvarchar （max），
  @ to_date   nvarchar （max），
  @ user_code   nvarchar （max）
  as  
 开始 
  declare   @ advid   nvarchar （max）; 
  set   @ advid  =  @ user_code  
 开始 
   cte  as  
（ select  advisor_id， case  何时  coalesce （SUM（commission_amount）， 0 ）< = 0 < span class =code-keyword>   0   else  SUM（commission_amount） end   as  comm 
 来自 #tbl_AdvisorCommissions 其中 advisor_id = ' ' + @ advid + ' ' 和 
 created_date    @ from_date  < span class =code-keyword>和  @ to_date  
  group  < span class =code-keyword> by  advisor_id  union   select  advisor_id， case     coalesce （SUM（commission_Amount）， 0 ）< = 0 
 然后  0   else  SUM（commission_Amount） end   as  comm 来自 #tbl_extra_AdvisorCommissions 其中 
 advisor_id =  ' ' + @ advid + ' ' 和 created_date 
    @ from_date  和  @ to_date  
  group   by  advisor_id），
 re  as  
（
  select  advisor_id，案例 何时 bleft< = aleft 然后  coalesce （（aleft-bleft）， 0 ）
 当 bleft> aleft 然后  coalesce （（bleft-aleft ）， 0 ） end   as  wleft， case  当明亮< = aright 然后  coalesce （（aright-bright）， 0 ）
 当明亮>正确然后  coalesce （ （明亮的）， 0 ） end   as  wright，commission_amount  as  wcom 来自 #tbl_weeklyCommissions 其中 advisor_id = ' ' + @ advid + ' ' 
 和 from_date> = @ from_date 和 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 
 来自 cte 正确 加入重新  cte.advisor_id = re.advisor_id 
  group   by  cte.advisor_id，comm 
  end  
  end  
 
 执行 sp_test '  2014-10-08'，'  2014-11-06'， '  143' 

当我使用此查询时，这是我的输出y：



advisor_id | rcomm | a | b | c

143 | 650 | 26200 | 7200 | 720

143 | 1700 | 26200 | 7200 | 720



我需要这样的输出：

 advisor_id | rcomm | a | b | c 
 
  143  | 2350 |  26200  |  7200  |  720  

当我在cte中汇总comm的值时它会给出错误价值，

我想在此查询中做什么修正。

给出你的建议！

谢谢！...

解决方案

试试这个SP：

  alter   proc  [dbo]。[sp_test] 
  @ from_date   nvarchar （max），
  @ to_date   nvarchar （max），
  @ user_code   nvarchar （max）
  as  
 开始 
 声明  @ advid   nvarchar （max）; 
  set   @ advid  =  @ user_code  
 开始 
   cte  as  
（ select  advisor_id， case  何时  coalesce （SUM（commission_amount）， 0 ）< = 0 < span class =code-keyword>   0   else  SUM（commission_amount） end   as  comm 
 来自 #tbl_AdvisorCommissions 其中 advisor_id = ' ' + @ advid + ' ' 和 
 created_date    @ from_date  < span class =code-keyword>和  @ to_date  
  group  < span class =code-keyword> by  advisor_id  union   select  advisor_id， case     coalesce （SUM（commission_Amount）， 0 ）< = 0 
 然后  0   else  SUM（commission_Amount） end   as  comm 来自 #tbl_extra_AdvisorCommissions 其中 
 advisor_id =  ' ' + @ advid + ' ' 和 created_date 
    @ from_date  和  @ to_date  
  group   by  advisor_id），
 re  as  
（
  select  advisor_id，案例 何时 bleft< = aleft 然后  coalesce （（aleft-bleft）， 0 ）
 当 bleft> aleft 然后  coalesce （（bleft-aleft ）， 0 ） end   as  wleft， case  当明亮< = aright 然后  coalesce （（aright-bright）， 0 ）
 当明亮>正确然后  coalesce （ （明亮的）， 0 ） end   as  wright，commission_amount  as  wcom 来自 #tbl_weeklyCommissions 其中 advisor_id = ' ' + @ advid + ' ' 
 和 from_date> = @ from_date 和 to_date< = @ to_date 
），cte4  as （
  select  cte.advisor_id，comm  as  rcomm，
 sum（re.wleft） as  a，sum（re.wright） as  b，sum（re.wcom） as  c 
 来自 cte 正确  join  re  on  cte.advisor_id = re.advisor_id 
  group   by  cte.advisor_id，comm 
）
  select  sum（rcomm），a，b，c 来自 cte4  group  按 a，b，c 
  end  
 端 

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!...

解决方案

Try this SP:

alter 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
),cte4 as(
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
 )
 select sum(rcomm),a,b,c From cte4 group by a,b,c
 end
end

这篇关于如何解决此查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！