Skip to Content
0
Former Member
Jun 06, 2011 at 05:13 PM

Showing Zero Quantities In Query

82 Views

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]