Thanks John,
I've been busy on site for a couple of days so this is the first chance I have had to get back to this. When I cut and paste your SQL into a query, the query produces and empty dataset.
As you have noticed, the SiteHistoryID does not correctly reflect the correct chronological sequence. So I modified the query to this:
SELECT S1.SiteHistoryID, S1.SiteID, S1.SiteHistoryCount, S1.SiteHistoryCount-S2.SiteHistoryCount AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX(SiteHistoryID) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryAsOfDate < S2.SiteHistoryASOfDate)));
But the result is still an empty dataset.
Oddly, however, this query
SELECT S1.SiteHistoryAsOfDate, S1.SiteID, S1.SiteHistoryCount, [S1].[SiteHistoryCount]-[S2].[SiteHistoryCount] AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE (((S1.SiteID)=[S2].[SiteID]) AND ((S2.SiteHistoryAsOfDate)=(SELECT MAX([SiteHistoryAsOfDate]) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S2.SiteHistoryID)));
does produce a dataset with 134 record. I haven't tested to see if that is the correct number of records but the data produced is obviously incorrect. Here's a sample of the result
SiteHistoryAsOfDate | SiteID | SiteHistoryCount | Difference |
17-Apr-16 | 733 | 10142 | 0 |
28-Apr-16 | 733 | 9930 | -212 |
18-Apr-16 | 733 | 9560 | -582 |
17-Apr-16 | 733 | 9147 | -995 |
21-Apr-16 | 733 | 8893 | -1249 |
19-Apr-16 | 733 | 9879 | -263 |
16-Apr-16 | 733 | 9146 | -996 |
27-Apr-16 | 733 | 8654 | -1488 |
20-Apr-16 | 733 | 8607 | -1535 |
17-Apr-16 | 769 | 5411 | 582 |
16-Apr-16 | 769 | 6504 | 1675 |
28-Apr-16 | 769 | 5484 | 655 |
19-Apr-16 | 769 | 4952 | 123 |
18-Apr-16 | 769 | 6586 | 1757 |
17-Apr-16 | 769 | 4829 | 0 |
24-Apr-16 | 769 | 4718 | -111 |
23-Apr-16 | 769 | 6005 | 1176 |
22-Apr-16 | 769 | 4983 | 154 |
28-Apr-16 | 707 | 20872 | -155 |
25-Apr-16 | 707 | 20630 | -397 |
25-Apr-16 | 707 | 21027 | 0 |
23-Apr-16 | 707 | 20714 | -313 |
26-Apr-16 | 707 | 20953 | -74 |
18-Apr-16 | 707 | 21485 | 458 |
16-Apr-16 | 707 | 20474 | -553 |
21-Apr-16 | 707 | 20437 | -590 |
17-Apr-16 | 707 | 20132 | -895 |
22-Apr-16 | 707 | 20336 | -691 |
28-Apr-16 | 707 | 21375 | 348 |
17-Apr-16 | 707 | 21195 | 168 |
29-Apr-16 | 742 | 7595 | -435 |
Glenn
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com]
Sent: Sunday, May 1, 2016 10:02 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] calculating net change
Glenn-
You can do that by using two copies of your table and linking them on SiteID and either the previous SiteHistoryID or SiteHistoryAsOfDate. The reason I ask about those two columns is your data is a bit confusing. For SiteID 700, you have:
3467 | 17-Apr-16 | 32211 | 700 |
3473 | 16-Apr-16 | 32060 | 700 |
The lower SiteHistoryID has the higher date.
The SQL might look like:
SELECT S1.SiteHistoryID, S1.SiteID, S1.SiteHistoryCount, S1.SiteHistoryCount - S2.SiteHistoryCount AS Difference
FROM SiteHistory AS S1, SiteHistory AS S2
WHERE S1.SiteID = S2.SiteID
AND S2.SiteHistoryID =
(SELECT MAX(SiteHistoryID) FROM SiteHistory AS S3
WHERE S3.SiteID = S1.SiteID
AND S3.SiteHistoryID < S2.SiteHistoryID);
If it's the date that makes the difference, than use SiteHistoryAsOfDate instead of SiteHistoryID.
John Viescas, Author
Microsoft Access 2010 Inside Out
Microsoft Access 2007 Inside Out
Microsoft Access 2003 Inside Out
Building Microsoft Access Applications
SQL Queries for Mere Mortals
(Paris, France)
On May 1, 2016, at 3:09 PM, 'Glenn Lloyd' argeedblu@gmail.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:
I have data that I want to analyze in terms of daily net change. Data reflecting the current volume is collected on a daily basis from approx. 75 locations. The data provider is unable to collect daily changes but needs this value computed by subtracting the previous reading from the last reading. Some locations have frequently changing daily levels while other have only occasional levels.
I am able to produce a short-term history (7 days) using a crosstab to flatten the date. I use the crosstab as a subquery in which each column is subtracted from the column that represents its predecessor.
The situation is similar to electrical power data where meter readings are periodically collected and consumption is calculated by subtracting the previous reading from the current reading. My data is slightly different because the values may indicate decreases as well as increases but this shouldn't affect the calculation.
Setting up a crosstab to calculate over the long term (as opposed to just the past 7 days) is not really practical. So I am looking for an efficient way to calculate periodic changes for a large number of periods.
Sample Date:
SiteHistoryID | SiteHistoryAsOfDate | SiteHistoryCount | SiteID |
3455 | 17-Apr-16 | 67035 | 696 |
3461 | 16-Apr-16 | 67034 | 696 |
3463 | 17-Apr-16 | 40970 | 697 |
3465 | 17-Apr-16 | 39186 | 698 |
3466 | 17-Apr-16 | 33243 | 699 |
3467 | 17-Apr-16 | 32211 | 700 |
3473 | 16-Apr-16 | 32060 | 700 |
3475 | 15-Apr-16 | 27102 | 701 |
3481 | 16-Apr-16 | 28282 | 701 |
3482 | 17-Apr-16 | 28000 | 701 |
3484 | 17-Apr-16 | 25075 | 702 |
3487 | 17-Apr-16 | 24895 | 703 |
3492 | 16-Apr-16 | 20506 | 706 |
3497 | 16-Apr-16 | 20474 | 707 |
3519 | 16-Apr-16 | 15858 | 715 |
3531 | 16-Apr-16 | 13706 | 720 |
3543 | 16-Apr-16 | 10320 | 728 |
3560 | 16-Apr-16 | 9146 | 733 |
3570 | 16-Apr-16 | 9270 | 739 |
3580 | 16-Apr-16 | 7609 | 742 |
3629 | 16-Apr-16 | 6504 | 769 |
3766 | 16-Oct-12 | 16417 | 773 |
Glenn
Posted by: "Glenn Lloyd" <argeedblu@gmail.com>
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (3) |
Tidak ada komentar:
Posting Komentar