SQL between Statement - I think?


#1

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.

Your thoughts would be appreciated.

Thanks!


#2

yeah, go ahead and use BETWEEN


#3

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.

Thanks!


#4

sorry, not until i understand it better

describe the fields that the user will input, then describe, in words, which rows of your table should be returned, specifically which column values


#5

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.

Thanks!


#6

i'm thick

user enters 73000 for salesprice, you want the query to return 470 and 803... or 480 and 803?

user enters comboloan 537, this is unconneted to the 73000, yes?

so 537 will return 984 or 999?


#7

Answers is YES.

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

If the loanamount is 60000 then the comboloan would be 475.

The comboloan would always be using the loanamount.


#8

why wouldn't it be 480 and 803?

okay so the user enters salesprice 73000 and loanamount 25000, please say again what happens next to get the comboloan


#9

Sorry my contacts are drying!

if

It would be 480 and 803 - always go to the higher of the two.


#10

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.


#11

you are confusing me with all these separate examples

i just want to concentrate on the logic which will be used in the queries

okay so the user enters salesprice 73000 and loanamount 25000, i understand that you want the query to return escrow 480 and ALTAowners 803

please say again what happens next to get the comboloan for this example where the loanamount is 25000


#12

The companies charts don't go that low so it would have to be based on the min of 60,000 which the comboloan would be $475.


#13

AHA!!! that statement above is what was throwing me off

what you actually meant was, SalesPrice minus LoanAmount is used to look up the "SalesPrice" value for the comboloan!

please confirm


#14

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.


#15

oh, man, am i ever confused

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


#16

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.


#17

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.

If you can provide the steps mathematically eg.

(foo.col_val + max(bar.col_val))/baz.col_val = qux.col_val

it might be a help.

If the values are relationally arbitrary, then you're going to need to explicitly get the columns.

r937's looks close if not correct to me. What is happening differently for you?


#18

my dude, i tested it


#19

see post #13 -- that's what i concluded but you said no


#20

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.