I've spent the morning on this SQL calc for a chart, where

GLOBALS::DATE_START = 9/1/2015

GLOBALS::DATE_END = 10/1/2015

Calc for chart data:

Let (
[~sql = "
    
    SELECT ~party_to , SUM (~~amount), SUM (payment_application.amount_applied_invoices)
    FROM ~payment
    LEFT OUTER JOIN payment_application
    ON payment.id=payment_application.id_payment
    WHERE id_payment_purpose =?
    AND ~date_effective BETWEEN ? AND ?
    GROUP BY ~party_to";

$sqlQuery = Substitute ( ~sql;
    
    // ["~id_payment_purpose" ; SQLGetTableName ( payment::id_payment_purpose )];
    ["~party_to" ; SQLGetFieldName ( payment::party_to ) ];
    ["~~amount" ;  SQLGetFieldName ( payment::amount) ];
    ["~payment" ; SQLGetTableName ( payment::id )];
    ["~date_effective" ; SQLGetFieldName ( payment::effective_date )]
    
);
    
 $sqlResult = ExecuteSQL ( $sqlQuery ; "" ; "" ; "D1F0A29A-CFAA-4DF3-882B-865771B95DC2"; GLOBALS::DATE_START; GLOBALS::DATE_END)  

]; If ( $sqlResult = "?" ;
        False;
        Let ( $$CHART.DATA3 = $sqlResult ; False )
)
)

 

$sqlResult= MyCompany, 1000

This calc works fine, EXCEPT there is only one payment with an effective date between 9/1..10/1 and its amount = $500. It is not applied to any invoices, and I do not receive a third value back (which is correct).

Where is it getting $1000!!

Your variables within the quote TEXT query are not evaluated before the calculation is performed.

AND ~date_effective BETWEEN ? AND ?

this should work:

 

AND " & _date_effective & " BETWEEN ? AND ?

 

beverly

 

Hmm. I stripped all the substituting fancy stuff out, and wrote this:

Let (
[~sql = "
    
    SELECT p.party_to , SUM (p.amount), SUM (payment_application.amount_applied_invoices)
    FROM payment AS p
    LEFT OUTER JOIN payment_application
    ON p.id=payment_application.id_payment
    WHERE p.id_payment_purpose =?
    AND  p.effective_date  BETWEEN ? AND ?
    GROUP BY p.party_to"

    
;
    
 $sqlResult = ExecuteSQL ( ~sql ; "" ; "" ; "D1F0A29A-CFAA-4DF3-882B-865771B95DC2"; GLOBALS::DATE_START; GLOBALS::DATE_END)  

]; If ( $sqlResult = "?" ;
        False;
        Let ( $$CHART.DATA3 = $sqlResult ; False )
)
)

 

got same result, $1000. Some data issue, perhaps, that I'm not seeing. Thanks for chiming in, Bev. My telepathy worked, lol!

The other addition I've been adding to get the date in the proper format is:

GetAsDate( Quote( Globals::Date_Start ) ) 

For some reason GetAsDate( "1/1/2015" ) seems to work better in being converted correctly for the query.

 

BC, always picking up your radar. LOL!

beverly

 

P.S. I've heard problems with BETWEEN and some have resorted to:

dateTest >= ? AND dateTest <= ?

 

 

 

I'll try it all. Thanks, Bev! I'll report back.

Still getting $1000 rather than $500. Not sure what else to try.

If you have the same relationship and perform a find, what do you get? You are also asking for a SUM(amount), so could that be getting more than one record that is included with the SUM().

 

Beverly

I have been doing a find, date range, payment_type and I find one record that has $500 for the amount.

I'll build a relationship. Perhaps that'll help me see why it's "seeing" more than one record.

OK, Bev, here's more info. The relationship is one to many payment->payment_application. My goal is to create a chart that shows the sum (amount) of the payments of type retainer received for a given date range by operating company (the system has 4 operating companies). The second column of the chart is a sum of the amount applied. That sum is stored in payment_application.

Perhaps I am not understanding joins?

Let (
[~sql = "
    
    SELECT p.party_to , SUM (p.amount)    FROM payment AS p
            WHERE p.id_payment_purpose =?
    AND p.effective_date >= ? AND p.effective_date <=?
    GROUP BY p.party_to ";
    
 $sqlResult = ExecuteSQL ( ~sql  ; "" ; "" ; "D1F0A29A-CFAA-4DF3-882B-865771B95DC2"; GLOBALS::DATE_START;  GLOBALS::DATE_END  )

];  $sqlResult 
)

Returns data from the one payment record: CompanyA, 500.

However, when I add in that join, it returns: CompanyA, 1000, 1000.

Would you be available for a screen share?

It could be the LEFT OUTER JOIN when a JOIN may be sufficient. Yes, I can screen share. Can we do that on this forum, or do you need to send me a "go to meeting" or something?

 

Bev