Query: daily percentage change

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gil Lopes - 23 Nov 2005 17:16 GMT

Hi all!

I need to build a query that returns a daily change from a table.

I need something like this:

Date Value@date Value@date-1 (Value@date / Value@date-1) - 1

Or else, just the first and last field.
Now, I understand I must have a subquery, but I' m not sure how to do it.

SELECT MyTable.Date , [Value]/DLookUp("[value]","[MyTable]","[date]=([date]-1)
") AS [%Change],
FROM MyTable (...)

I tried this aproach, but it doesn' t retreive the desired values.
I also include some criteria on the date field and a product ID criteria, but
I guess the problem is somewhere in the SQL above. I looked for solutions in
previous posts around, but couldn' t solve this.

Any hints?

Thanks,

Gil

Reply to this Message

Vincent Johns - 24 Nov 2005 06:15 GMT

I didn't use a subquery, but I did use a self-join (kind of). For
example, suppose your Table looks like this:

[MyTable] Table Datasheet View:

MyTable_ID value date
---------- ----- ----------
822701467 80 11/18/2005
-33935806 83 11/21/2005
424831205 85 11/22/2005
483188468 88 11/23/2005

Then the following Query, in which I rounded the date/time field to the
nearest day (at midnight):

[Q_PercentChange] SQL:

SELECT MyTable.date, MyTable.value,
100*([MyTable]![value]-PreviousDay!value)
/[MyTable]![value] AS [%Change]
FROM MyTable, MyTable AS PreviousDay
WHERE (((CDate(Int([PreviousDay]![date]+0.5)))
=Int([MyTable]![date]+0.5)-1))
ORDER BY MyTable.date;

produces output looking like this (where I changed the format of the 3rd
column to Format = Fixed, Decimal Places = 2):

[Q_PercentChange]

date value %Change
---------- ----- -------
11/22/2005 85 2.35
11/23/2005 88 3.41

Dates with no immediate predecessor (such as 11/21/05 in my example) are
omitted.

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

> Hi all!
>
[quoted text clipped - 21 lines]
>
> Gil

Reply to this Message

Gil Lopes - 24 Nov 2005 17:41 GMT

Hi Vincent!

Thanks for the help.
Am I correct when thinking that your WHERE clause is mostly due to the "round
date" effect?

REGARDS,

Gil

>I didn't use a subquery, but I did use a self-join (kind of). For
>example, suppose your Table looks like this:
[quoted text clipped - 42 lines]
>>
>> Gil

Reply to this Message

Vincent Johns - 25 Nov 2005 06:39 GMT

> Hi Vincent!
>
[quoted text clipped - 5 lines]
>
> Gil

Yes, the only reason I put that in there was in case your dates included
times of day. But there are other ways to handle that, and maybe you
already know that you will never have fractional dates, so you can omit
that clause.

-- Vincent Johns <vjohns@alumni.caltech.edu>
Please feel free to quote anything I say here.

Reply to this Message