AnsweredAssumed Answered

ExecuteSQL - Nested Select

Question asked by kennethpeace on Jul 2, 2013
Latest reply on Jul 10, 2013 by philmodjunk

Title

ExecuteSQL - Nested Select

Post

     I've been successful in using ExecuteSQL to do charting for Monthly Revenue. That's kind of straight forward. But lately when i attempt to do Average Monthly Revenue, it hit a roadblock. To do that, i need nested select. But i can't seem to get it to work. Pls see below statements. I hope someone can give me some advise. 

     Let([
     $Today = Get(CurrentDate);
     $TargetYear = Year($Today);
      
     $query = "SELECT * FROM
         (SELECT SUM(r.\"Final Price\") ListTotal
         FROM \"Class Schedule\" cs  JOIN Registration r 
              ON YEAR(cs.\"Class Date\") = ?
              AND cs.\"Class Schedule id\" = r.\"Class Schedule id\"
              AND r.\"Payment Status\" = 'Paid' AND r.\"Registration Status\" = 'Registered'
        GROUP BY cs.\"Month in number\"
        ORDER BY cs.\"Month in number\"
        )
     ";
      
     $result = ExecuteSQL( $query; "" ; "" ; $TargetYear )
     ]; $result )
      
      
     ========== 
     Below is the scripts that works when i just display monthly SUM.
          Let([
          $Today = Get(CurrentDate);
          $TargetYear = Year($Today);
           
          $query = "SELECT SUM(r.\"Final Price\") ListTotal
              FROM \"Class Schedule\" cs  JOIN Registration r 
                   ON YEAR(cs.\"Class Date\") = ?
                   AND cs.\"Class Schedule id\" = r.\"Class Schedule id\"
                   AND r.\"Payment Status\" = 'Paid' AND r.\"Registration Status\" = 'Registered'
             GROUP BY cs.\"Month in number\"
             ORDER BY cs.\"Month in number\"
          ";
           
          $result = ExecuteSQL( $query; "" ; "" ; $TargetYear )
          ]; $result )

      

Outcomes