Wayne,
I think you are going to want to use this view instead. I added a where clause at the bottom that should filter the dates as you are looking for.
victoriayudin.com/.../sql-view-for-inventory-items-and-dates
CREATE VIEW view_Inventory_with_Dates
AS
/********************************************************************
view_Inventory_with_Dates
Created on Dec 4, 2009 by Victoria Yudin - Flexible Solutions, Inc.
For updates see victoriayudin.com/gp-reports
All inventory items with quantity on hand and last sale
and receipt dates Functional amounts only
Tables used:
I - IV00101 - Item Master
S - IV30300 - Transaction Amounts History with DOCTYPE = 6 (sales)
Q - IV00102 - Item Quantity Master
U - IV40201 - U of M Schedule Header
Updated Dec 22, 2009 to add WHERE clause at end
Updated Jan 29, 2011 to change join type for IV30300 ********************************************************************/
SELECT I.ITEMNMBR Item_Number,
I.ITEMDESC Item_Description,
Q.QTYONHND Quantity_on_Hand,
U.BASEUOFM U_of_M,
CASE I.ITEMTYPE
WHEN 1 THEN 'Sales Inventory'
WHEN 2 THEN 'Discontinued'
WHEN 3 THEN 'Kit'
WHEN 4 THEN 'Misc Charges'
WHEN 5 THEN 'Services'
WHEN 6 THEN 'Flat Fee'
END Item_Type,
I.CURRCOST Current_Cost,
I.ITMCLSCD Item_Class,
coalesce(S.LastSale,'1/1/1900') Last_Sale_Date,
coalesce(Q.LSRCPTDT,'1/1/1900') Last_Receipt_Date,
coalesce(Q.LSORDVND,'') Last_Vendor
FROM IV00101 I
LEFT OUTER JOIN
(SELECT ITEMNMBR, MAX(DOCDATE) LastSale
FROM IV30300
WHERE DOCTYPE = 6
GROUP BY ITEMNMBR) S
ON I.ITEMNMBR = S.ITEMNMBR
INNER JOIN
IV00102 Q
ON I.ITEMNMBR = Q.ITEMNMBR
AND RCRDTYPE = 1
INNER JOIN
IV40201 U
ON U.UOMSCHDL = I.UOMSCHDL
WHERE Q.QTYONHND <> 0 AND S.LastSale > '12/31/2010'
/** the following will grant permissions to this view to DYNGRP,
leave this section off if you do not want to grant permissions **/
GO
GRANT SELECT ON view_Inventory_with_Dates TO DYNGRP
