AnsweredAssumed Answered

ExecuteSQL SUM issue

Question asked by AlexE on Aug 1, 2014
Latest reply on Aug 1, 2014 by AlexE

Title

ExecuteSQL SUM issue

Post

     Hi, 

     I'm building the following query: (e.g.: $$week = 3, $$quarter = 4, $$year = 14)

     ExecuteSQL (
     "
     SELECT SUM ( r.UnitsSold )
     FROM RawDataWTD AS r
     WHERE r.StoreID = ?
     AND r.FiscalQuarter = ?
     AND r.FiscalYear = ?
     AND r.FiscalWeekInFiscalQuarter <= ?
     AND r.ProductCategory = ?
     AND r.ProductType = ?
     "
     ; "" ; "" ; StoreInfo::StoreID  ; $$quarter ; $$year -1 ; $$week ; "PAD" ; "FG" )
      
     The source table has sales data. 1 week per row, so for Q4 FY13 I have 13 rows. The field (FiscalWeekInFiscalQuarter) has it's data stored as a plain integer number (1, 2, 3, 4, 5,..., 12, 13). 
      
     Looking up a single week works fine (e.g.: AND r.FiscalWeekInFiscalQuarter = ?) however, if I try and do <= then it will SUM weeks 1, 2, 3, 10, 11, 12, 13. Although the argument is technically r.FiscalWeekInFiscalQuarter <= 3, it correctly sums up the values for weeks 1, 2 and 3, it also for some reason adds 10, 11, 12 and 13 which I've verified by manually adding the data on the source file. The issue is the same if add Revenue instead of UnitsSold. 
      
     Am I missing anything? Why would it do this? 

Outcomes