PIVOT table with more than one aggregate and for only weeks with data.
-
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.
I would like to end up with something like this.
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 -
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:
- Tables should be qualified with their schema names
- Tables should be aliased
- Column names should be qualified with table aliases
- VARCHAR should always be used with an explicit width, otherwise the default width is likely to catch you out at some point.
- 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?
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
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
-
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
- Phil Parkin wrote:
- <li style="list-style-type: none;">
- 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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs - Jeffrey Williams wrote:Phil Parkin wrote:
- 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.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
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 - 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) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
- 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.
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply