I am struggling a bit on a SQL statement that deals with range of numbers in fields in the database. It goes to $10,000,000. Sales price and LoanAmont will be in a form and posted to the handler page.
If you look at my graphic, let’s take this example:
$65,000 - $70,000 has the same Escrow fee. So what if a user picks $68,000 which is not in the database?
So I am not sure if using between is best and then not really sure how to structure that statement. Bottom like, I have SalesPrice in a form input to equal the related fields.
Also not sure if 6 sql statements is best to use (Escrow through ComboLoan)
I am thinking I need 2 sqls because Escrow, ALTAowners and StandardOwners is based on the SalesPrice and LoanAmount uses StandardLoan, AltaLoan and ComboLoan.
I have never done a between so I am not grasping the concept of it. Can you give me a sample sql for my scenario so I can make some sense out of it? And do you think I really then would only need 2 because I would be dealing with working off of Sales Price and Loan Amount from the form input.
Yes, thank you, I want to be sure that I explain it correctly:
I have a form for SalesPrice and LoanAmount.
Those 2 will be posted to the handler page where I want SalesPrice to equal the corresponding Escrow and ALTAowners columns They can be done in separate sql statements if necessary.
Then I need the LoanAmount to equal the corresponding ComboLoan rate.
So when I do an estimate of costs I would have a formula that gives the following for a math formula: Escrow AltaOwners ComboLoan
From there I know how to do my adds and subtracts, etc.
I just don’t know how to pull those number from the charts by using the between statement in sql.
I just need to know how to pull them out of the table.
Hope I am clear with my explanation and thank you for your time, it’s appreciated.
if[quote=“javascript7, post:7, topic:285677”]
The comboloan is based on the loanamount so if it’s zero down the loan amount would be 73000 than the result would be 475
[/quote]
It would be 480 and 803 - always go to the higher of the two.
Sorry I didn’t see this. The email notification didn’t come in.
Yes the Salesprice of $73,000 would be 480 and 803. I corrected that, must been at the same time you wrote back. On a $23,000 loanamount the minimum would be based on minimum of $475 which is off the $60,000 SalesPrice number. (just the way it’s done)
So therefore, the SalesPrice column is going to be used for the $amount of either the SalesPrice or LoanAmount. This is just the way the companies publish their charts. Nothing to do with me.
But let’s take a SalesPrice of $140,000, the escrow is $540 and $942 for ALTAowners. On that transaction, if the buyer put $40,000 down, then the comboloan would be $475 (off the salesprice figure of $100,000).
So in other words, the lookup price for the loanamount would be based on the salesprice number. Again, just the way they publish there charts.
No, I meant that the SalesPrice number is what the number is to make up the loan combloan rate. Odd, but that is how the companies makes up their charts. Therefore, the Salesprice number becomes the LoanAmount number in actuality as it references the ComboLoan.
maybe someone else can step in to do your second query, because none of your explanations make any sense to me
i can do the first query fine
SELECT higher.escrow
, higher.altaowners
FROM javascript7 AS higher
LEFT OUTER
JOIN javascript7 AS lower
ON lower.salesprice =
( SELECT MAX(salesprice)
FROM javascript7
WHERE salesprice < higher.salesprice)
WHERE higher.salesprice >= $form_salesprice
AND COALESCE(lower.salesprice,0) < $form_salesprice
i just have no idea which columns to use for finding the comboloan, despite you trying to explain it eleventy times
The comboloan can be determined by the LoanAmount input from the form, because the form posts both salesprice and loanamount, but the loanamount becomes the salesprice in the table for the comboloan rate.
That’s why I wasn’t sure going back to the beginning if 2 sql statements was necessary.
One for the Salesprice and one for the loanamount.
I notice that all the “< 100K” rows have mostly the same values.
However, using the escrow equal rows and comparing the lower sale price row with the higher sale price row, I did not find any pattern that I could use for putting together what I call “rough pseudocode”. That is, the comboloan values seem to be arbitrary, not derived, calculated from, or referencing any other column in any way.
The LoanAmount does use StandardLoan, AltaLoan and ComboLoan but I am only going to use the ComboLoan but you can only get that ComboLoan number from the SalesPrice number that is really a substitute for the inputted value of loan amount based of the SalesPrice. There is no separate column for loan amount in the table that comes directly from a chart provided by the company.
