CTE Example with Pivot operator

  • KeithDB

    SSC Enthusiast

    Points: 180

    Comments posted to this topic are about the item CTE Example with Pivot operator

  • Lynn Pettis

    SSC Guru

    Points: 442098

    My only complaint about your code is the semicolon preceding the WITH for your CTE.  When creating a CTE it is required that the PREVIOUS statement be terminated with a semicolon, not the CTE begin with a CTE.  It is obvious that people doing this leads others not familiar with writing CTEs to think that the semicolon is required before the WITH when declaring a CTE.

     

  • Phil Parkin

    SSC Guru

    Points: 243363

    Being picky, I do have a few other minor comments about the code:

    1. The created table has no schema name
    2. Literal dates are not in ISO format ('YYYYMMDD')
    3. Inconsistent use of square brackets (look at order_dt, for example)
    4. Coalesce() with two arguments is equivalent to IsNull() ... might as well save yourself some typing

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

  • Thom A

    SSC Guru

    Points: 98090

    wrote:

    Coalesce() with two arguments is equivalent to IsNull() ... might as well save yourself some typing

    Actually, Phil, I feel I need to correct you on that one. ISNULL and COALESCE do behave differently. COALESCE is a shortcut function for a CASE expression and therefore uses Data Type Precedence to determine the return value. ISNULL, however, returns the data type of the first parameter.

    I certainly agree on the dates though. Most of those would fail to convert to a date on my instance, or would provide the wrong value.

    Thom~

    Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.

  • Luis Cazares

    SSC Guru

    Points: 183516

    If we're going to be picky, I have some other things to note:

    1. The keywords are not case consistent, some are written in upper case and some in lower case.
    2. The format is not consistent either, sometimes the FROM will be on the same line and sometimes on the next one.
    3. There are multiple columns in the same line which reduces readability.

    Those were just formatting issues, but my main problems are:

    1. The orders table is called twice when we only need to call it once.
    2. The use of pivot instead of using a cross tabs query.

    My version would turn out like this:

    WITH preAggregate AS(
    SELECT YEAR(order_dt) as [Year],
    MONTH( [order_dt]) AS [month],
    SUM( [amount]) AS [amount]
    FROM orders
    GROUP BY YEAR(order_dt),
    MONTH( [order_dt])
    )
    SELECT [Year],
    SUM( [amount]) AS [Annual Total],
    SUM( CASE WHEN [month] = 1 THEN [amount] ELSE 0 END) AS [Jan],
    SUM( CASE WHEN [month] = 2 THEN [amount] ELSE 0 END) AS [Feb],
    SUM( CASE WHEN [month] = 3 THEN [amount] ELSE 0 END) AS [Mar],
    SUM( CASE WHEN [month] = 4 THEN [amount] ELSE 0 END) AS [Apr],
    SUM( CASE WHEN [month] = 5 THEN [amount] ELSE 0 END) AS [May],
    SUM( CASE WHEN [month] = 6 THEN [amount] ELSE 0 END) AS [Jun],
    SUM( CASE WHEN [month] = 7 THEN [amount] ELSE 0 END) AS [Jul],
    SUM( CASE WHEN [month] = 8 THEN [amount] ELSE 0 END) AS [Aug],
    SUM( CASE WHEN [month] = 9 THEN [amount] ELSE 0 END) AS [Sep],
    SUM( CASE WHEN [month] = 10 THEN [amount] ELSE 0 END) AS [Oct],
    SUM( CASE WHEN [month] = 11 THEN [amount] ELSE 0 END) AS [Nov],
    SUM( CASE WHEN [month] = 12 THEN [amount] ELSE 0 END) AS [Dec]
    FROM preAggregate
    GROUP BY [Year];

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • Phil Parkin

    SSC Guru

    Points: 243363

    Thanks Thom, quite right! I should have added the text "in this case".

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply