locked
sql server r2 Order By class not working RRS feed

  • Question

  • User-1011865089 posted
    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX),@sum nvarchar(max),@fromdate datetime ,@todate Datetime,@date nvarchar(max);
    set @sum =N'';
    SET @columns = N'';
    --set @fromdate =N'';
    --set @todate=N'';
    select @fromdate ='2013-04-01';
    select @todate ='2014-03-31';
    
    select @sum += N' +isnull( p.'+QUOTENAME(plan_name)+',0)' from (select plan_name from tbl_planTypes) as y;
    set @sum = stuff(@sum,1,1,'');
    
    SELECT @columns += N', p.' + QUOTENAME(plan_name)
      FROM (select plan_name from tbl_plantypes) AS x;
    SET @sql = N'
    SELECT advisor_id,advisor_code,advisor_name,promotion_name,promotion_targets,intro_code,intro_name,a.promotion_id,' + STUFF(@columns, 1, 2, '') + ','+@sum+' as totalbusiness
    FROM
    (
    select a.advisor_id,a.advisor_Code,a.advisor_name,g.promotion_name,g.promotion_targets,f.Plan_name,case when c.plan_id=f.plan_id then isnull(sum(b.premium_amount+b.carry_amount),0) end as plan1,e.advisor_Code as intro_code,e.advisor_name as Intro_name from tbl_advisor_registration as a   inner join tbl_payment_master as b on  b.advisor_id=a.advisor_id inner join tbl_user_registration as c on  b.user_id=c.user_id inner join tbl_advisor_registration as e on a.create_by_user_id=e.advisor_id  inner join tbl_planTypes as f on c.plan_id=f.plan_id  inner join tbl_promotion as g on a.promotion_Id=g.promotion_id where convert(nvarchar(max),b.paid_date,102)  between '''+convert(nvarchar, @fromdate, 102)+''' and '''+convert(nvarchar, @todate, 102)+'''  group by a.advisor_id,a.advisor_Code,a.advisor_name,g.promotion_name,g.promotion_targets,f.Plan_name,c.plan_id,f.plan_id,e.advisor_Code,e.advisor_name,a.promotion_id order by a.promotion_id desc
    ) AS j  
    PIVOT
    (
      SUM(plan1) FOR plan_name IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ')
    ) AS p;';
    print @sql
    
    EXEC sp_executesql @sql;

    i want to order by promotion id , but it is not working,give some idea.

    Wednesday, June 25, 2014 3:17 AM

Answers

  • User-1011865089 posted

    solution:

    create proc [dbo].[sp_autopromotionmain]
    @fromd datetime ,
    @tod Datetime
    as
    begin
    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX),@sum nvarchar(max),@fromdate datetime ,@todate Datetime,@date nvarchar(max);
    set @sum =N'';
    SET @columns = N'';
    --set @fromdate =N'';
    --set @todate=N'';
    select @fromdate =@fromd;
    select @todate =@tod;
    
    select @sum += N' +isnull( p.'+QUOTENAME(plan_name)+',0)' from (select plan_name from tbl_planTypes) as y;
    set @sum = stuff(@sum,1,1,'');
    
    SELECT @columns += N', p.' + QUOTENAME(plan_name)
      FROM (select plan_name from tbl_plantypes) AS x;
    SET @sql = N'
    SELECT advisor_id,advisor_code,advisor_name,promotion_name,promotion_targets,intro_code,intro_name,promotion_id,' + STUFF(@columns, 1, 2, '') + ','+@sum+' as totalbusiness
    FROM
    (
    select a.advisor_id,a.advisor_Code,a.advisor_name,g.promotion_name,g.promotion_targets,f.Plan_name,a.promotion_id,case when c.plan_id=f.plan_id then isnull(sum(b.premium_amount+b.carry_amount),0) end as plan1,e.advisor_Code as intro_code,e.advisor_name as Intro_name from tbl_advisor_registration as a   inner join tbl_payment_master as b on  b.advisor_id=a.advisor_id inner join tbl_user_registration as c on  b.user_id=c.user_id inner join tbl_advisor_registration as e on a.create_by_user_id=e.advisor_id  inner join tbl_planTypes as f on c.plan_id=f.plan_id  inner join tbl_promotion as g on a.promotion_Id=g.promotion_id where convert(nvarchar(max),b.paid_date,102)  between '''+convert(nvarchar, @fromdate, 102)+''' and '''+convert(nvarchar, @todate, 102)+'''  group by a.advisor_id,a.advisor_Code,a.advisor_name,g.promotion_name,g.promotion_targets,f.Plan_name,c.plan_id,f.plan_id,e.advisor_Code,e.advisor_name,a.promotion_id
    ) AS j  
    PIVOT
    (
      SUM(plan1) FOR plan_name IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ')
    ) AS p order by promotion_id desc;';
    print @sql
    
    EXEC sp_executesql @sql;
    end

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 25, 2014 9:05 AM

All replies

  • User753101303 posted

    Hi,

    What if you move the ORDER BY clause at the very end to sort your final result ? Don't you have an error message telling you that you can't do that inside a subquery ? (ALWAYS tell what happens and mention a possible error message rather than just telling "it is not working". It helps to check assumptions on  your issue and to quickly confirm or reject possible causes).

    This is disallowed because some operations are best done by sorting the data implicitely so your data would be sorted again anyway. Basically do the ORDER BY as late as possible.

    Wednesday, June 25, 2014 4:53 AM
  • User-1011865089 posted

    solution:

    create proc [dbo].[sp_autopromotionmain]
    @fromd datetime ,
    @tod Datetime
    as
    begin
    DECLARE @columns NVARCHAR(MAX), @sql NVARCHAR(MAX),@sum nvarchar(max),@fromdate datetime ,@todate Datetime,@date nvarchar(max);
    set @sum =N'';
    SET @columns = N'';
    --set @fromdate =N'';
    --set @todate=N'';
    select @fromdate =@fromd;
    select @todate =@tod;
    
    select @sum += N' +isnull( p.'+QUOTENAME(plan_name)+',0)' from (select plan_name from tbl_planTypes) as y;
    set @sum = stuff(@sum,1,1,'');
    
    SELECT @columns += N', p.' + QUOTENAME(plan_name)
      FROM (select plan_name from tbl_plantypes) AS x;
    SET @sql = N'
    SELECT advisor_id,advisor_code,advisor_name,promotion_name,promotion_targets,intro_code,intro_name,promotion_id,' + STUFF(@columns, 1, 2, '') + ','+@sum+' as totalbusiness
    FROM
    (
    select a.advisor_id,a.advisor_Code,a.advisor_name,g.promotion_name,g.promotion_targets,f.Plan_name,a.promotion_id,case when c.plan_id=f.plan_id then isnull(sum(b.premium_amount+b.carry_amount),0) end as plan1,e.advisor_Code as intro_code,e.advisor_name as Intro_name from tbl_advisor_registration as a   inner join tbl_payment_master as b on  b.advisor_id=a.advisor_id inner join tbl_user_registration as c on  b.user_id=c.user_id inner join tbl_advisor_registration as e on a.create_by_user_id=e.advisor_id  inner join tbl_planTypes as f on c.plan_id=f.plan_id  inner join tbl_promotion as g on a.promotion_Id=g.promotion_id where convert(nvarchar(max),b.paid_date,102)  between '''+convert(nvarchar, @fromdate, 102)+''' and '''+convert(nvarchar, @todate, 102)+'''  group by a.advisor_id,a.advisor_Code,a.advisor_name,g.promotion_name,g.promotion_targets,f.Plan_name,c.plan_id,f.plan_id,e.advisor_Code,e.advisor_name,a.promotion_id
    ) AS j  
    PIVOT
    (
      SUM(plan1) FOR plan_name IN ('
      + STUFF(REPLACE(@columns, ', p.[', ',['), 1, 1, '')
      + ')
    ) AS p order by promotion_id desc;';
    print @sql
    
    EXEC sp_executesql @sql;
    end

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, June 25, 2014 9:05 AM