PIVOT table with more than one aggregate and for only weeks with data.

  • kat35601

    Right there with Babe

    Points: 792

    I need help to pivot my sql output for Class with aggregates of QTY and CUBES for WEEKS. Some rows will only have one of the classes.

    sql_output

    I would like to end up with something like this.

    mysqlout

     

    SELECT Datepart(wk, ompcreateddate + 84)          AS week, 
    CONVERT(VARCHAR, ompcreateddate + 84, 101) AS date,
    cmopostcode AS Zip,
    cmostate AS State,
    cmocity AS City,
    cmoname AS NAME,
    ompsalesorderid AS OrderID,
    imppartclassid AS Class,
    Sum(omlorderquantity) AS Qty,
    Sum(uomlbasevolume * omlorderquantity) AS Cubes,
    CASE
    WHEN uompfurnhold = 2 THEN 'Product Hold'
    WHEN uompfurnhold = 3 THEN 'Minimal Hold'
    WHEN uompfurnhold = 4 THEN 'Customer Hold'
    ELSE 'GO'
    END AS Hold,
    ompshippingmethodid
    FROM salesorders
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN organizations
    ON cmoorganizationid = ompcustomerorganizationid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE ompclosed !=- 1
    AND imppartclassid NOT IN( 'OBS', 'DC', 'KT' )
    GROUP BY Datepart(wk, ompcreateddate + 84),
    CONVERT(VARCHAR, ompcreateddate + 84, 101),
    cmopostcode,
    cmostate,
    cmocity,
    cmoname,
    ompsalesorderid,
    imppartclassid,
    uompfurnhold,
    ompshippingmethodid
    ORDER BY CONVERT(VARCHAR, ompcreateddate + 84, 101),
    ompsalesorderid
  • Phil Parkin

    SSC Guru

    Points: 244753

    It's going to be difficult to suggest modifications to such a complex piece of SQL without having some DDL and sample data to test it on.

    Some comments:

    1. Tables should be qualified with their schema names
    2. Tables should be aliased
    3. Column names should be qualified with table aliases
    4. VARCHAR should always be used with an explicit width, otherwise the default width is likely to catch you out at some point.
    5. That's an unusual looking statement in the ORDER BY (CONVERT(VARCHAR, ompcreateddate + 84, 101)). Does it achieve something which ORDER BY ompcreateddate does not?

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kat35601

    Right there with Babe

    Points: 792

    I understand  your comments on 1 thru 4 and will update that before I move it over.

    number 5 > just add 84 days to the date the order was created which is our average number of days to the  first possible ship date.

    I can supply some data how is that done best on here?

     

    Thanks

  • pietlinden

    SSC Guru

    Points: 62898

    Jeff explains how to post consumable data in this article. Absolutely worth reading. If you read it, and follow the instructions in your next post, someone can write a tested query to answer your question

  • Jeffrey Williams

    SSC Guru

    Points: 88666

    Phil Parkin wrote:

      <li style="list-style-type: none;">

    1. That's an unusual looking statement in the ORDER BY (CONVERT(VARCHAR, ompcreateddate + 84, 101)). Does it achieve something which ORDER BY ompcreateddate does not?

    It isn't unusual - he is ordering by the second column in the select (named 'date' which is a really bad name because it is a reserved word).  This is the one area where you can use a column alias and I would recommend using it here instead of restating the function.

    ORDER BY [Date], ompsalesorderid

     

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Phil Parkin

    SSC Guru

    Points: 244753

    Jeffrey Williams wrote:

    Phil Parkin wrote:

    1. That's an unusual looking statement in the ORDER BY (CONVERT(VARCHAR, ompcreateddate + 84, 101)). Does it achieve something which ORDER BY ompcreateddate does not?

    It isn't unusual - he is ordering by the second column in the select (named 'date' which is a really bad name because it is a reserved word).  This is the one area where you can use a column alias and I would recommend using it here instead of restating the function.

    ORDER BY [Date], ompsalesorderid

    The thinking behind my comment was that there might be an index on ompcreateddate which the ORDER BY could use, and which would definitely not be used with the CONVERT() version.

    • This reply was modified 1 month, 2 weeks ago by  Phil Parkin.
    • This reply was modified 1 month, 2 weeks ago by  Phil Parkin.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

  • kat35601

    Right there with Babe

    Points: 792

    I could do it with subqueries.

     

    SELECT Datepart(wk, ompcreateddate + 84)                   AS week, 
    CONVERT(VARCHAR, ompcreateddate + 84, 101) AS date,
    cmopostcode AS Zip,
    cmostate AS State,
    cmocity AS City,
    cmoname AS NAME,
    ompsalesorderid AS OrderID,

    (SELECT Isnull(Sum(omlorderquantity), 0)
    FROM salesorders sl1
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE sl1.ompsalesorderid = sl.ompsalesorderid
    AND imppartclassid = 'FGM') AS FGMqty,
    (SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
    FROM salesorders sl1
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE sl1.ompsalesorderid = sl.ompsalesorderid
    AND imppartclassid = 'FGM') AS FGMcubes,
    (SELECT Isnull(Sum(omlorderquantity), 0)
    FROM salesorders sl1
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE sl1.ompsalesorderid = sl.ompsalesorderid
    AND imppartclassid = 'FGI') AS FGIqty,
    (SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
    FROM salesorders sl1
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE sl1.ompsalesorderid = sl.ompsalesorderid
    AND imppartclassid = 'FGI') AS FGIcubes,
    (SELECT Isnull(Sum(omlorderquantity), 0)
    FROM salesorders sl1
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE sl1.ompsalesorderid = sl.ompsalesorderid
    AND imppartclassid NOT IN ( 'FGI', 'FGM' )) AS OTHqty,
    (SELECT Isnull(Sum(uomlbasevolume * omlorderquantity), 0)
    FROM salesorders sl1
    LEFT OUTER JOIN salesorderlines
    ON omlsalesorderid = ompsalesorderid
    LEFT OUTER JOIN partrevisions
    ON imrpartid = omlpartid
    LEFT OUTER JOIN parts
    ON imppartid = omlpartid
    WHERE sl1.ompsalesorderid = sl.ompsalesorderid
    AND imppartclassid NOT IN ( 'FGI', 'FGM' )) AS OTHcubes,
    uompvolumetotal,
    uomptotalboxcount,
    CASE
    WHEN uompfurnhold = 2 THEN 'Product Hold'
    WHEN uompfurnhold = 3 THEN 'Minimal Hold'
    WHEN uompfurnhold = 4 THEN 'Customer Hold'
    ELSE 'GO'
    END AS Hold,
    ompshippingmethodid
    FROM salesorders sl
    LEFT OUTER JOIN organizations
    ON cmoorganizationid = ompcustomerorganizationid
    WHERE ompclosed !=- 1
    AND ompshippingmethodid != 'DC'
    ORDER BY Datepart(wk, ompcreateddate + 84),
    CONVERT(VARCHAR, ompcreateddate + 84, 101),
    cmopostcode,
    cmostate,
    cmocity,
    cmoname,
    ompsalesorderid
  • ScottPletcher

    SSC Guru

    Points: 98571

    Jeffrey Williams wrote:

    (named 'date' which is a really bad name because it is a reserved word).

    It's actually not a reserved SQL Server keyword.  [It is reserved in ODBC, but that's a different thing.]

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Phil Parkin

    SSC Guru

    Points: 244753

    ScottPletcher wrote:

    Jeffrey Williams wrote:

    (named 'date' which is a really bad name because it is a reserved word).

    It's actually not a reserved SQL Server keyword.  [It is reserved in ODBC, but that's a different thing.]

    Not yet. But 'Date' does appear in the list of keywords that could become reserved in future SQL Server releases, so still best avoided.

    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.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

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

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