Post reply

table containing billing rates

  • April 4, 2007 at 1:08 pm

    #116369

    Greetings,

    I have a table that contains billing rates. These rates will be used to perform accounting functions in a few applications.

    In the table, I was wondering if I should use default values of '1' or NULL.

    I ask because if a column does not have a value, but the accounting functions still place that column in their formulas, I don't want to screw up the formula results.

    For example, if Col1 is '1' and Col2 is '3', but Col3 is blank, then what default value would be best to place in there?

    So if the accounting formula went:

    Col1 = objRs("Col1")

    Col2 = objRs("Col2")

    Col3 = objRs("Col3")

    Result = (Col1 * Col2) + Col3

    Obviously I wouldn't want zeros, because that might affect the results of the column when using multplication or division.

    1's would be OK for muliplications but would screw up when adding or subtracting.

    And I think just having NULL would cause an error.

    Any hints?

    Thanks!

  • April 4, 2007 at 1:13 pm

    #698631

    SELECT Col1, Col2, Col3, COALESCE(Col1, 1) * COALESCE(Col2, 1) + COALESCE(Col3, 0) AS Result

    FROM Table1

    Col1 = objRs("Col1")

    Col2 = objRs("Col2")

    Col3 = objRs("Col3")

    Result = objRs("Result")

     

    N 56°04'39.16"
    E 12°55'05.25"

  • April 4, 2007 at 1:16 pm

    #698633

    I think you have to post your eight business rules how to handle all cases with values vs NULL.

    Col1 Col2 Col3 Result

    ---- ---- ---- ------

       5    5    5    30

       5    5 NULL    25?

       5 NULL    5    10?

       5 NULL NULL     5?

    NULL    5    5    10?

    NULL    5 NULL     5?

    NULL NULL    5     5?

    NULL NULL NULL     0?

     

    N 56°04'39.16"
    E 12°55'05.25"

  • April 4, 2007 at 1:20 pm

    #698634

    Here is my full formula(in ASP):

    result = objRs("CCflatRate")

    result = result + (pages * objRs("CC1setRate"))

    result = result - (MaxVal(0, pages - objRs("CC1setpageEnd") * (objRs("CC1setRate") - objRs("CC2setRate")

    result = result - (MaxVal(0, pages - objRs("CC2setpageEnd") * (objRs("CC3setRate") - objRs("CC3setRate")

    Any of the database objects EXCEPT CCflatRate could be empty. So I need to fool-proof the table so the formula will work if any one of the columns happen to be empty.

  • April 4, 2007 at 9:05 pm

    #698700

    You need to use some CASE statements, at the very least.  Accounting functions are way too important to trust defaults.

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

  • April 5, 2007 at 7:12 am

    #698775

    You mean CASE statements in the SQL or the ASP code?

    Thanks!

  • April 5, 2007 at 7:32 am

    #698787

    Be sure you know your ANSI_NULLS setting.

    In accounting you need to know what to do with every value. There are times that you don't want a zero, so a null may work. However, as Peter mentioned, be sure your code is expecting nulls and knows how to work with them.

    1 + 2 + 0 = 3, avg 1

    1 + 2 + null = ???

    Is it unknown, meaning not valid or is it 3? Is the average 1 or 1.5?

  • April 5, 2007 at 8:03 am

    #698802

    I don't think that you can come up with a simple rule.  You need to decide what to do in every situation.

    If you actually do not know a value or it does not apply, then I think storing a null in the table is better than storing a fake value.

    I would rather that a calculation returned a null than an incorrect answer.  At least you would know that something is wrong.

    This is no diffferent than deciding what you want a calculation to return when it would result in division by zero.

     

  • April 5, 2007 at 12:13 pm

    #698891

    performing mathematical operations on anything with a NULL will result in a NULL? Correct?

    Thanks!

  • April 5, 2007 at 12:40 pm

    #698896

    Magy, it depends (don't you love that answer?), as some things, such as aggregate functions, can safely deal with Nulls by ignoring them. But yes, 2 + Null = Null.

  • April 5, 2007 at 4:15 pm

    #698936

    I'm thinking in the SQL side but could probably also be done on the ASP side.  In either case (sorry about the pun), financial calculations should probably not have general rules... as some have said, if you don't have enough information to come up with the true answer in all cases, store a NULL in the table as an "I dunno".

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply