Showing Zero Quantities In Query

[Former Member]

In the following Query, I get a list of the Quantities all items ordered and sold within a date range. I am trying to make the query show all quantities Ordered, event if nothing was sold. When I add another AND statement such as: AND T0.[OrderedQty] < '1' it will not show me where nothing was ordered. How do I get NULL values or Items that had no orderes during that same time period of the query?

SELECT T0.[ItemCode], T1.[ItemName], T2.[FirmName], T1.[CardCode], T1.[ItmsGrpCod], T1.[U_U_CMG_PROGRAM], T1.[U_U_CMG_CATEG], T1.[U_CMG_STATUS], T1.[U_CMG_STORES], T1.[U_CMG_COLOR], T1.[MinLevel] AS Min_Inv_Lvl, T1.[MinOrdrQty] AS Min_Ord_Lvl, SUM(T0.[OrderedQty]) AS Ord_Qty, SUM(T0.[PackQty]) AS Pack_Qty, SUM(T0.[Quantity]) AS Qty_Sold FROM DLN1 T0 INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode INNER JOIN OMRC T2 ON T1.FirmCode = T2.FirmCode

WHERE T0.[DocDate] >=[%0] AND T0.[DocDate] <=[%1]

GROUP BY T0.[ItemCode], T1.[ItemName], T2.[FirmName], T1.[CardCode], T1.[ItmsGrpCod], T1.[U_U_CMG_PROGRAM], T1.[U_U_CMG_CATEG], T1.[U_CMG_STATUS], T1.[U_CMG_STORES], T1.[U_CMG_COLOR], T1.[MinLevel], T1.[MinOrdrQty]

[Former Member · Accepted solution]

Hi,

Try this query.

SELECT T0.ItemCode, T1.ItemName, T2.FirmName, T1.CardCode,

T1.ItmsGrpCod, T1.U_U_CMG_PROGRAM, T1.U_U_CMG_CATEG, T1.U_CMG_STATUS,

T1.U_CMG_STORES, T1.U_CMG_COLOR, T1.MinLevel AS Min_Inv_Lvl,

T1.MinOrdrQty AS Min_Ord_Lvl, SUM(T0.OrderedQty) AS Ord_Qty,

SUM(T0.PackQty) AS Pack_Qty, SUM(T0.Quantity) AS Qty_SoldFROM OITM T1 LEFT JOIN DLN1 T0 ON T0.ItemCode T1.ItemCode

INNER JOIN OMRC T2 ON T1.FirmCode = T2.FirmCode WHERE T0.DocDate >=[%0] AND T0.DocDate <=[%1]

GROUP BY T0.ItemCode, T1.ItemName, T2.FirmName, T1.CardCode,

T1.ItmsGrpCod, T1.U_U_CMG_PROGRAM, T1.U_U_CMG_CATEG, T1.U_CMG_STATUS,

T1.U_CMG_STORES, T1.U_CMG_COLOR, T1.MinLevel, T1.MinOrdrQty

ORDER BY T0.ItemCode, T1.ItemName, T2.FirmName, T1.CardCode,

T1.ItmsGrpCod, T1.U_U_CMG_PROGRAM, T1.U_U_CMG_CATEG, T1.U_CMG_STATUS,

T1.U_CMG_STORES, T1.U_CMG_COLOR, T1.MinLevel, T1.MinOrdrQty

regards,

Vignesh

[Former Member]

Hi,

Try:

SELECT T0.ItemCode, T1.ItemName, T2.FirmName, T1.CardCode,

T1.ItmsGrpCod, T1.U_U_CMG_PROGRAM, T1.U_U_CMG_CATEG, T1.U_CMG_STATUS,

T1.U_CMG_STORES, T1.U_CMG_COLOR, T1.MinLevel AS Min_Inv_Lvl,

T1.MinOrdrQty AS Min_Ord_Lvl, SUM(T0.OrderedQty) AS Ord_Qty,

SUM(T0.PackQty) AS Pack_Qty, SUM(T0.Quantity) AS Qty_Sold

FROM OITM T1

LEFT JOIN DLN1 T0 ON T0.ItemCode = T1.ItemCode

INNER JOIN OMRC T2 ON T1.FirmCode = T2.FirmCode

WHERE T0.DocDate >=[%0\] AND T0.DocDate <=[%1\]

GROUP BY T0.ItemCode, T1.ItemName, T2.FirmName, T1.CardCode,

T1.ItmsGrpCod, T1.U_U_CMG_PROGRAM, T1.U_U_CMG_CATEG, T1.U_CMG_STATUS,

T1.U_CMG_STORES, T1.U_CMG_COLOR, T1.MinLevel, T1.MinOrdrQty

Thanks,

Gordon