AnsweredAssumed Answered

ExecuteSQL Between Dates filtering

Question asked by braighvarrt on May 5, 2013
Latest reply on May 7, 2013 by braighvarrt

Title

ExecuteSQL Between Dates filtering

Post

     The following calculation (A) produces the total quantity of a specific product ordered but when I change the SQL statement per the second example (B) to filter for just orders between two global date fields my calculation field is blank. Can anyone advise on the correct way to write the SQL statement WHERE clause to give the desired result.

     (A)

     SumOrderQtyInPeriod = ExecuteSQL ("select sum(l.OrderQty) from Products p join OrderlineItem l on p. \"__pkProductItemID\" = l.\"_fkProductID\" where l.\"_fkProductID\" =? " ; ""; "" ;__pkProductItemID )

      

     (B)

     ExecuteSQL ("select sum(l.OrderQty) from Products p join OrderlineItem l on p. \"__pkProductItemID\" = l.\"_fkProductID\" where l.\"_fkProductID\" =? AND o.OrderDate Between ? AND ? " ; ""; "" ;__pkProductItemID; GlobalResources::z_OpeningStockDate; GlobalResources::z_ClosingStockDate )

Outcomes