SQL Query: Calculating the deltas in a time series

Question

For a development aid project I am helping a small town in Nicaragua improving their water-network-administration.

There are about 150 households and every month a person checks the meter and charges the houshold according to the consumed water (reading from this month minus reading from last month). Today all is done on paper and I would like to digitalize the administration to avoid calculation-errors.

I have an MS Access Table in mind - e.g.:

*HousholdID*  *Date*     *Meter*
0             1/1/2013   100
1             1/1/2013   130
0             1/2/2013   120
1             1/2/2013   140
...

From this data I would like to create a query that calculates the consumed water (the meter-difference of one household between two months)

*HouseholdID*  *Date*     *Consumption*
0              1/2/2013   20
1              1/2/2013   10
...

Please, how would I approach this problem?

Answer 1

This query returns every date with previous date, even if there are missing months:

SELECT TabPrev.*, Tab.Meter as PrevMeter, TabPrev.Meter-Tab.Meter as Diff
FROM (
  SELECT
    Tab.HousholdID,
    Tab.Data,
    Max(Tab_1.Data) AS PrevData,
    Tab.Meter
  FROM
    Tab INNER JOIN Tab AS Tab_1 ON Tab.HousholdID = Tab_1.HousholdID
                                 AND Tab.Data > Tab_1.Data
  GROUP BY Tab.HousholdID, Tab.Data, Tab.Meter) As TabPrev
  INNER JOIN Tab
  ON TabPrev.HousholdID = Tab.HousholdID
     AND TabPrev.PrevData=Tab.Data

Here's the result:

HousholdID  Data        PrevData    Meter  PrevMeter  Diff
----------------------------------------------------------
0           01/02/2013  01/01/2013  120    100        20
1           01/02/2013  01/01/2012  140    130        10

The query above will return every delta, for every households, for every month (or for every interval). If you are just interested in the last delta, you could use this query:

SELECT
  MaxTab.*,
  TabCurr.Meter as CurrMeter,
  TabPrev.Meter as PrevMeter,
  TabCurr.Meter-TabPrev.Meter as Diff
FROM ((
  SELECT
    Tab.HousholdID,
    Max(Tab.Data) AS CurrData,
    Max(Tab_1.Data) AS PrevData
  FROM
    Tab INNER JOIN Tab AS Tab_1
        ON Tab.HousholdID = Tab_1.HousholdID
           AND Tab.Data > Tab_1.Data
  GROUP BY Tab.HousholdID) As MaxTab
  INNER JOIN Tab TabPrev
  ON TabPrev.HousholdID = MaxTab.HousholdID
     AND TabPrev.Data=MaxTab.PrevData)
  INNER JOIN Tab TabCurr
  ON TabCurr.HousholdID = MaxTab.HousholdID
     AND TabCurr.Data=MaxTab.CurrData

and (depending on what you are after) you could only filter current month:

WHERE
  DateSerial(Year(CurrData), Month(CurrData), 1)=
  DateSerial(Year(DATE()), Month(DATE()), 1)

this way if you miss a check for a particular household, it won't show. Or you might be interested in showing last month present in the table (which can be different than current month):

WHERE
  DateSerial(Year(CurrData), Month(CurrData), 1)=
  (SELECT MAX(DateSerial(Year(Data), Month(Data), 1))
  FROM Tab)

(here I am taking in consideration the fact that checks might be on different days)

Answer 2

I think the best approach is to use a correlated subquery to get the previous date and join back to the original table. This ensures that you get the previous record, even if there is more or less than a 1 month lag.

So the right query looks like:

select t.*, tprev.date, tprev.meter
from (select t.*,
             (select top 1 date from t t2 where t2.date < t.date order by date desc
             ) prevDate
      from t
     ) join
     t tprev
     on tprev.date = t.prevdate

In an environment such as the one you describe, it is very important not to make assumptions about the frequency of reading the meter. Although they may be read on average once per month, there will always be exceptions.

Answer 3

Testing with the following data:

HousholdID  Date        Meter
0           01/12/2012  100
1           01/12/2012  130
0           01/01/2013  120
1           01/01/2013  140
0           01/02/2013  120
1           01/02/2013  140

The following query:

SELECT a.housholdid, 
   a.date, 
   b.date, 
   a.meter, 
   b.meter, 
   a.meter - b.meter AS Consumption
FROM   (SELECT * 
    FROM   water 
    WHERE  Month([date]) = Month(Date()) 
           AND Year([date])=year(Date())) a 
   LEFT JOIN (SELECT *
              FROM water
              WHERE DateSerial(Year([date]),Month([date]),Day([date]))
               =DateSerial(Year(Date()),Month(Date())-1,Day([date])) ) b 
   ON a.housholdid = b.housholdid 

The above query selects the records for this month Month([date]) = Month(Date()) and compares them to records for last month ([date]) = Month(Date()) - 1)

Please do not use Date as a field name.

Returns the following result.

housholdid  a.date      b.date      a.meter b.meter Consumption
0           01/02/2013  01/01/2013  120     100     20
1           01/02/2013  01/01/2013  140     130     10

Answer 4

Try

select  t.householdID
       , max(s.theDate) as billingMonth
       , max(s.meter)-max(t.meter) as waterUsed
from    myTbl t join (
    select  householdID, max(theDate) as theDate, max(meter) as meter
    from    myTbl 
    group by householdID ) s 
        on t.householdID = s.householdID and t.theDate <> s.theDate
group by t.householdID

This works in SQL not sure about access