Jump to content

Results do not match Find


This topic is 3072 days old. Please don't post here. Open a new topic instead.

Recommended Posts

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

Link to comment
Share on other sites

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

 

Link to comment
Share on other sites

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!

Link to comment
Share on other sites

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

 

 

 

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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

Link to comment
Share on other sites

This topic is 3072 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.