Comparing current field data with last entry for field

[ccnorris]

Hi. I have a form that is inserting sales data on a daily basis. I have a
field named "RoundTOTAL" which is the total sales for the day rounded to the
nearest 100. I need to compare the "RoundTOTAL" that I am inserting for the
current day with the "RoundTOTAL" for the previous day. Then, I need to input
a value for a field representing whether the current day's "RoundTOTAL" is
higher or lower than the previous day.

My question is:
How do I pull a field from the last entry in the database so that I can
compare it to today's value?

Thanks in advance for any help!

[dempster]

There are a couple of ways to do this. If you know that there will be a record
for the previous day, you can use ColdFusion to calculate the previous day and
then query the database for this record. Another way is to query the database
and order by the date field in descending order. The first record returned in
this query would be the previous day.

Once you get the record, you can compare the previous day's total with the
current day and then insert the current day record.

-Paul

[Alen]

why dont you use a date column in the same table to know each RoundTotal
belongs to what date, the no mater how many RoundTotal you have and when you
insert it in the Database you can always get two different dates and compare
them.
Alen

[ccnorris]

I do have a date column in the same table.

Is there any way to compare the "RoundTOTAL" values BEFORE I insert the
current "RoundTOTAL"? The result from the comparison needs to be inserted into
the database at the same time that I insert the date, daily sales, total, and
"RoundTOTAL".

Sorry, for not completely comprehending your replies. I am still fairly new
to CF and very new to comparing values from the database.

[Alen]

Why do you need to compare them before inserting into Database?
Insert today's RoundTOTAL for what ever it is. Then make a select SQL command
and get all records from yesterday and after.

SELECT RoundTOTAL, sale_date
FROM sales_Table
WHERE sale_date >= '05-31-2005'


you should get two records , one for yesterday and one for today. then compare
them or do what ever you want with them.

ps. check you database documentation to see what date format it expects. What
I posted works on MySQL

[ccnorris]

I am creating a table that outputs the results. The compared RoundTOTAL values
will determine the color of the row for that date. If the RoundTOTAL value for
today is higher than the value from yesterday it will be assigned a value of
"G" for green. If lower a value of "R" for red will be assigned. This is why
I need to do the comparison before the Insert (if possible).

[dempster]

As I explained earlier, you can query the database in order to get the previous
day's record and rounded total. Do this before you add the record for the
current day. Once you get that total, you can figure out if it is higher or
lower and then add your new record.

If you are just doing this in order to display a row color, you could just
handle that in the ColdFusion program that displays your output. Query the
database for your records in order by date, ascending. Start your output. For
each record you output, save the roundtotal in a variable such as
prevroundtotal. Compare this variable with the roundtotal from the current
record in order to show the row as green or red.

That would be an easier way to handle it.

-Paul

[ccnorris]

Paul,

Thank you so much for your help and explanation! It works great!

Carolyn

[ccnorris]

Alen,

Thank you also for you help!

Carolyn