cancel
Showing results for 
Search instead for 
Did you mean: 

Help Fixing A Query

Former Member
0 Kudos
55

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)

Accepted Solutions (1)

Accepted Solutions (1)

Former Member
0 Kudos

Mike,

Try this:


SELECT P.[Style],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT LEFT(T0.ITEMCODE,4) AS 'Style', T1.QUANTITY,  MONTH(T1.Shipdate) as [month] 
FROM dbo.oitm T0
LEFT JOIN INV1 T1 ON T1.ItemCode=T0.ItemCode AND YEAR(T1.ShipDate) = YEAR(GETDATE())) S
  PIVOT  (SUM(QUANTITY) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[Style]

Thanks,

Gordon

Former Member
0 Kudos

HI Gordon -

Thanks! It looks like this new query will pull all our styles. However, when I compared monthly sales numbers for a few items it looks like they are different...why is that?

Thanks,

Mike

Former Member
0 Kudos

Both are working but different date selection. Try this for the same:


SELECT P.[Style],
 [1] as [Jan],
 [2] as [Feb],
 [3] as [Mar],
 [4] as [Apr],
 [5] as [May],
 [6] as [Jun],
 [7] as [Jul],
 [8] as [Aug],
 [9] as [Sep],
 [10] as [Oct],
 [11] as [Nov],
 [12] as [Dec]
FROM (SELECT LEFT(T0.ITEMCODE,4) AS 'Style', T1.QUANTITY,  MONTH(T1.Docdate) as [month] 
FROM dbo.oitm T0
LEFT JOIN INV1 T1 ON T1.ItemCode=T0.ItemCode AND YEAR(T1.DocDate) = YEAR(GETDATE())
) S
  PIVOT  (SUM(QUANTITY) FOR [month] IN 
([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])) P
ORDER BY P.[Style]

The original query uses Docdate. New one uses Shipdate. Now go back to docdate by this newest.

Answers (0)