Results do not match Find

[bcooney]

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!!

[beverly]

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

 

[bcooney]

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!

[beverly]

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 <= ?

 

 

 

[bcooney]

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

[bcooney]

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

[beverly]

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

[bcooney]

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.

[bcooney]

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?

[beverly]

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