none
unable to get output with CTE

    Question

  • Scenario - Retrieves totals of all top 5 selling departments for all sites, departments and groups selected] 

    fnStringsSplit- is a udf andwill concert a comma seperated string to SQL sting as '10,02,03', to '10','02','03' 

    if I execute query as follows(Except past 13 week Quantitiesfunctionality) I'm getting output  

    selecttop100 *fromDem.dbo.invoices whereDate<CONVERT(datetime,'12/03/2012',103)  

    andDate>Dateadd(day,-8,CONVERT(DATETIME,'12/03/2012',103))andSite=1 andProductDept ='02'andProductGroup ='AC' 

    , but where as with the following code it is returning nothing and took about 17min to complete execution 

     

    To achieve 13 week quantities functionality, I choose CTE methodology

    link to code can edit there too

     

    Length limit is crossed, so gave link to code

    Please help me  

    Thanking you in advance

     

     

     

     

    Wednesday, July 10, 2013 7:01 PM

Answers

  • Hi,

    You don't have to create so many CTEs for this. You can use pivoting to accomplish this.

    A sample using AdventureWorks

    Declare @startdate date;
    Declare @enddate date;
    
    set @enddate='2001-10-01 00:00:00.000';
    set @startdate = dateadd(day, -7 * 13, @enddate);
    
    SELECT SalesPersonID,
     [1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12]
    FROM
    (SELECT inv.SalesPersonID, COUNT(1) AS ProdCount, -Datediff(Wk,@enddate,inv.OrderDate) W
    FROM AdventureWorks.Sales.SalesOrderHeader AS inv 
    WHERE inv.[OrderDate] between @startdate and @enddate and inv.SalesPersonID is not null
    GROUP BY inv.SalesPersonID,Datediff(Wk,@enddate,inv.OrderDate)) AS SourceTable
    PIVOT
    (
    sum(Prodcount)
    FOR W IN ([1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS PivotTable;


    Satheesh


    Friday, July 12, 2013 3:58 PM

All replies

  • codes are too long and we have no codes, use execution plan to improve the performance

    Friday, July 12, 2013 2:34 AM
  • Try to check if you have proper indices on the columns of the table.

    Many Thanks & Best Regards, Hua Min

    Friday, July 12, 2013 3:56 AM

  • Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Learn how to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect. 

    This is minimal polite behavior on SQL forums. 

    >> fnStringsSplit- is a udf and will convert a comma separated string to SQL sting as '10,02,03', to '10','02','03' <<

    Good SQL programers do not do this! Did you know that using the prefix “FN-” is called “tibling” and it is a design flaw? It come from FORTRAN II. You have no idea what first normal form means; SQL programmers use only scalar values. 

    You are doing the wrong thing and doing it with bad code. 

    We do not use the old Sybase/UNIX CONVERT() string function with dates. The word DATE is a reserved word in SQL; it cannot be a column name. We do not use the proprietary TOP(n) dialect. The only format allowed in ANSI/ISO Standard SQL is ISO-8601.

    Again, you are doing the wrong thing and doing it with bad code. First, let's correct the SQL to ISO-11179, ISO-8601 and modern T-SQL.

    SELECT * --- never use * in production code
      FROM Invoices 
    WHERE invoice_date BETWEEN CAST ('2012-12-03' AS DATE)
              AND DATEDIFF (DAY, -8, CAST ('2012-12-03' AS DATE))
      AND site_something = 1 
      AND product_dept = '02'
      AND product_group = 'AC';

    >> link to code can edit there too <<

    Do you log into websites from strangers?  Open emails from Bill Gates? This is dangerous for us and very rude on your part. Read the basic Netiquette at the front of the forum and follow it. 

     

    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Friday, July 12, 2013 2:40 PM
  • Hi,

    You don't have to create so many CTEs for this. You can use pivoting to accomplish this.

    A sample using AdventureWorks

    Declare @startdate date;
    Declare @enddate date;
    
    set @enddate='2001-10-01 00:00:00.000';
    set @startdate = dateadd(day, -7 * 13, @enddate);
    
    SELECT SalesPersonID,
     [1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12]
    FROM
    (SELECT inv.SalesPersonID, COUNT(1) AS ProdCount, -Datediff(Wk,@enddate,inv.OrderDate) W
    FROM AdventureWorks.Sales.SalesOrderHeader AS inv 
    WHERE inv.[OrderDate] between @startdate and @enddate and inv.SalesPersonID is not null
    GROUP BY inv.SalesPersonID,Datediff(Wk,@enddate,inv.OrderDate)) AS SourceTable
    PIVOT
    (
    sum(Prodcount)
    FOR W IN ([1], [2], [3], [4],[5],[6],[7],[8],[9],[10],[11],[12])
    ) AS PivotTable;


    Satheesh


    Friday, July 12, 2013 3:58 PM