Calculating Daily Change

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a straightforward table that is populated each day. I would like to
write a simple SQL query that calculates the daily change for one of the
variables.

The table has 3 fields/columns Date, Name & Price.

Date Name Price
21/08 ABC 95
18/08 ABC 80
17/08 ABC 70
16/08 ABC 65
15/08 ABC 50
14/08 ABC 40
11/08 ABC 30

I would like for the output to be:

Date Name Change
21/08 ABC 15
18/08 ABC 10
17/08 ABC 5
16/08 ABC 15
15/08 ABC 10
14/08 ABC 10
11/08 ABC

It is simple the current day's observation minus the previous day's
observation.

I would appreciate any help.
 
I used two queries --
Day-to-Day Change_1 --
SELECT Date, Name, Price, (SELECT COUNT(*)
FROM [soflahe] T1
WHERE T1.Name = T.Name
AND T1.Date <= T.Date) AS Rank
FROM soflahe AS T
ORDER BY Date;

SELECT [Day-to-Day Change_1_1].Date, [Day-to-Day Change_1].Name, [Day-to-Day
Change_1].Price AS [Old Price], [Day-to-Day Change_1_1].Price AS [New Price],
[Day-to-Day Change_1_1].[Price]-[Day-to-Day Change_1].[Price] AS [Price
Change]
FROM [Day-to-Day Change_1] INNER JOIN [Day-to-Day Change_1] AS [Day-to-Day
Change_1_1] ON [Day-to-Day Change_1].Name = [Day-to-Day Change_1_1].Name
WHERE ((([Day-to-Day Change_1_1].Rank)=[Day-to-Day Change_1].[Rank]+1))
ORDER BY [Day-to-Day Change_1].Date;

You should not use fields named "Date" and "Name" in Access.
 
Many thanks. Does exactly what I wanted.

KARL DEWEY said:
I used two queries --
Day-to-Day Change_1 --
SELECT Date, Name, Price, (SELECT COUNT(*)
FROM [soflahe] T1
WHERE T1.Name = T.Name
AND T1.Date <= T.Date) AS Rank
FROM soflahe AS T
ORDER BY Date;

SELECT [Day-to-Day Change_1_1].Date, [Day-to-Day Change_1].Name, [Day-to-Day
Change_1].Price AS [Old Price], [Day-to-Day Change_1_1].Price AS [New Price],
[Day-to-Day Change_1_1].[Price]-[Day-to-Day Change_1].[Price] AS [Price
Change]
FROM [Day-to-Day Change_1] INNER JOIN [Day-to-Day Change_1] AS [Day-to-Day
Change_1_1] ON [Day-to-Day Change_1].Name = [Day-to-Day Change_1_1].Name
WHERE ((([Day-to-Day Change_1_1].Rank)=[Day-to-Day Change_1].[Rank]+1))
ORDER BY [Day-to-Day Change_1].Date;

You should not use fields named "Date" and "Name" in Access.

soflahe said:
I have a straightforward table that is populated each day. I would like to
write a simple SQL query that calculates the daily change for one of the
variables.

The table has 3 fields/columns Date, Name & Price.

Date Name Price
21/08 ABC 95
18/08 ABC 80
17/08 ABC 70
16/08 ABC 65
15/08 ABC 50
14/08 ABC 40
11/08 ABC 30

I would like for the output to be:

Date Name Change
21/08 ABC 15
18/08 ABC 10
17/08 ABC 5
16/08 ABC 15
15/08 ABC 10
14/08 ABC 10
11/08 ABC

It is simple the current day's observation minus the previous day's
observation.

I would appreciate any help.
Click to expand...
Click to expand...
 
Back
Top