AnsweredAssumed Answered

Help with SQL Calc fields

Question asked by user28897 on Oct 6, 2018
Latest reply on Oct 7, 2018 by user28897

Howdy everyone,

 

I am trying to tackle a new Execute SQL calc field and getting the love ?.   Things look right to me but the data viewer says I have an error in the syntax of my query.  I thought maybe some trained eyes could spot this really quickly.

Two tables are involved here.  The first where the calculation field resides is Properties.   The table being queried is AIRGMS.  In both Properties and AIRGMS I have a matching field pCode.   The other fields involved in AIRGMS are From Date and Number Of Nights. 

 

What I am trying to do is run a 30/60/90 day occupancy rate both forward and back from any given day.   So to do occupancy rate not on a set month I needed to sum Number of Nights rented and divide that by  the number of days between what ever start end dates.   I used a custom function to set variables for those two dates.

 

This is what my query looks like for the Plus 30 calc field, so today to one month from today.

 

Let (

   [  $pCode = Properties::pCode;

      $vtoday = Get ( CurrentDate );

      $vplus30 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate )  ; Get ( CurrentDate )  ; 1 );   ///Custom Fxn

      $vplus60 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate )  ; Get ( CurrentDate )  ; 2 );

      $vplus90 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate )  ; Get ( CurrentDate )  ; 3 );

      $vminus30 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate )  ; Get ( CurrentDate )  ; -1 );

      $vminus60 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate )  ; Get ( CurrentDate )  ; -2 );

      $vminus90 = AddMonths ( Get ( CurrentDate ) ; Get ( CurrentDate )  ; Get ( CurrentDate )  ; -3 );

      $vdays = $vplus30- $vtoday;

 

      $query =

              "SELECT Sum(\”Number Of Nights\”) FROM AIRGMS

               WHERE (pCode = ?)

                 And

              (\”From Date\” >= ?)

                 And

              (\”From Date\” <= ?)";

  

    $result = ExecuteSQL ( $query; "" ; "" ; $pCode ; $vtoday ; $vplus30)

     

   ];  $result

)

 

Now once this is fixed to return the sum of nights rented l need to divide by $vdays.   I assume I can just add /$vdays right after $result for the calculation to work?

 

Thanks for any help you can provide!

Outcomes