2 Replies Latest reply on Aug 1, 2014 10:27 AM by AlexE

    ExecuteSQL SUM issue

    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? 

        • 1. Re: ExecuteSQL SUM issue
          philmodjunk
               

                    then it will SUM weeks . Although the argument is technically r.<= 3

               I suspect that FiscalWeekInFiscalQuarter is either a text field or a calculation field that returns a text result type.

               Using text based comparison rules, "1", "2", "3", "10", "11", "12", and "13" are all less than "3" for the same reason that "Abracadabra" is less than "Zebra".

               So if you can get that field type to be number, I think this will then work as you need it here.

          • 2. Re: ExecuteSQL SUM issue
            AlexE

                 You're a genius! Looking at it now it makes total sense why I'd be having that problem!

                  

                 Thanks for your help!