Minggu, 01 Mei 2016

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: John Viescas <johnv@msn.com>
Reply via web post Reply to sender Reply to group Start a New Topic Messages in this topic (2)

Upgrade your account with the latest Yahoo Mail app
Get organized with the fast and easy-to-use Yahoo Mail app. Upgrade today!


.

__,_._,___

Tidak ada komentar:

Poskan Komentar