August 14, 2020 at 3:37 pm
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
August 14, 2020 at 3:48 pm
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:
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 14, 2020 at 4:25 pm
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
August 14, 2020 at 4:33 pm
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
August 14, 2020 at 4:48 pm
<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
Problems are opportunities brilliantly disguised as insurmountable obstacles.
How to post questions to get better answers faster
Managing Transaction Logs
August 14, 2020 at 4:54 pm
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.
If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
August 14, 2020 at 5:12 pm
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
August 14, 2020 at 7:01 pm
(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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
August 14, 2020 at 7:24 pm
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 you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply