StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>Here you go. This should do it. </p> <ul> <li>Since you always want all items in Top300 regardless of their Sales data, you always have to keep the results of each subquery on the LEFT side of the LEFT JOINs. </li> <li>You can resolve the NULL SUM aggregates to zero </li> <li>The date filters need to be in the JOINs since you don't want to limit the Top300 items that do not join up. If you put them in the WHERE clause, then it will exclude the Top300 items that do not join up with the sales data since the dates for those records would be NULL.</li> </ul> <p>First setup the test tables and data</p> <pre><code> IF OBJECT_ID('Top300', 'U') IS NOT NULL DROP TABLE Top300; GO IF OBJECT_ID('SalesData', 'U') IS NOT NULL DROP TABLE SalesData; GO CREATE TABLE Top300 ( Part varchar(10) ) GO CREATE TABLE SalesData ( SBITEM varchar(10) ,SBQSHP int ,SBINV int ,SBINDT int ) GO INSERT Top300 SELECT A.* FROM ( SELECT * FROM Top300 WHERE 1=2 UNION ALL SELECT 'Widget1' UNION ALL SELECT 'Widget2' UNION ALL SELECT 'Widget3' UNION ALL SELECT 'Widget4' UNION ALL SELECT 'Widget5' UNION ALL SELECT 'Widget6' ) A INSERT SalesData SELECT A.* FROM ( SELECT * FROM SalesData WHERE 1=2 UNION ALL SELECT 'Widget1', 100, 1000, '20120316' UNION ALL SELECT 'Widget2', 100, 1000, '20120316' UNION ALL SELECT 'Widget3', 100, 1000, '20121016' UNION ALL SELECT 'Widget4', 100, 1000, '20121016' UNION ALL SELECT 'Widget5', 100, 1000, '20121016' ) A </code></pre> <p>Now lets run a corrected version of the original query</p> <pre><code> SELECT b.Part, b.Last_12_Mo, b.Last_6_Mo, COUNT(sd.SBINV) AS Order_Count FROM ( SELECT a.Part, a.Last_12_Mo, ISNULL(SUM(sd.SBQSHP), 0) AS Last_6_Mo FROM ( SELECT t3.Part, ISNULL(SUM(sd.SBQSHP),0) AS Last_12_Mo FROM Top300 AS t3 LEFT JOIN SalesData AS sd ON t3.Part = sd.SBITEM AND sd.SBINDT > '20120315' GROUP BY t3.Part ) AS a LEFT JOIN SalesData AS sd ON a.Part = sd.SBITEM AND sd.SBINDT > '20121015' GROUP BY a.Part, a.Last_12_Mo ) AS b LEFT JOIN SalesData AS sd ON b.Part = sd.SBITEM AND sd.SBINDT > '20130110' GROUP BY b.Part, b.Last_12_Mo, b.Last_6_Mo ORDER BY b.Part </code></pre> <p>Finally, let's run a simpler, and more optimized query that produces the same results</p> <pre><code> SELECT b.Part ,SUM(CASE WHEN sd.SBINDT > '20120315' THEN sd.SBQSHP ELSE 0 END) as [Last_12_Mo] ,SUM(CASE WHEN sd.SBINDT > '20121015' THEN sd.SBQSHP ELSE 0 END) as [Last_6_Mo] ,SUM(CASE WHEN sd.SBINDT > '20130110' THEN 1 ELSE 0 END) as [Order_Count] FROM Top300 b LEFT JOIN SalesData AS sd ON b.Part = sd.SBITEM AND sd.SBINDT > '20120315' GROUP BY b.Part ORDER BY b.Part </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload