Help Fixing A Query

35 Views

Hello All -

We use the query below to find the qty sold per month of each of our styles. Our styles have multiple colors and sizes so this query consolidates all colors and sizes per style. Each style is indicated by the first 4 characters in our Item Code.

The only thing we would like changed on this query is so that it shows a complete list of all of our styles -- even those that have no sales for any months. Currently, it appears to only show items that have a sale.

Any help changing below so that it is a complete list -- i.e., showing all items whether there are sales or no sales.

Thanks,

Mike

SELECT LEFT(T0.ITEMCODE,4) AS 'Style', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 1 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JAN Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 2 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'FEB Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 3 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAR Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 4 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'APR Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 5 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'MAY Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 6 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUN Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 7 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'JUL Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 8 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'AUG Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 9 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'SEP Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 10 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'OCT Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 11 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'NOV Amt', (SELECT SUM(T1.QUANTITY) FROM INV1 T1 with (NOLOCK) WHERE MONTH(T1.DOCDATE) = 12 AND LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) AND YEAR(T1.DOCDATE) = YEAR(GETDATE())) AS 'DEC Amt'

FROM dbo.OITM T0 LEFT JOIN dbo.INV1 T1 ON LEFT(T1.ITEMCODE,4) = LEFT(T0.ITEMCODE,4) WHERE T0.SellItem = 'Y' GROUP BY LEFT(T0.ITEMCODE,4),YEAR(T1.DOCDATE) HAVING YEAR(T1.DOCDATE) = YEAR(GETDATE())

ORDER BY LEFT(T0.ITEMCODE,4)