Query: daily percentage change

  • Thread starter Thread starter Gil Lopes
  • Start date Start date
G

Gil Lopes

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
 
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 <[email protected]>
Please feel free to quote anything I say here.
 
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


Vincent said:
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.

Hi all!
Click to expand...
[quoted text clipped - 21 lines]
Gil
Click to expand...
Click to expand...
 
Gil said:
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
Click to expand...

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 <[email protected]>
Please feel free to quote anything I say here.
 
Back
Top