Querying changes over time

  • Thread starter Thread starter GPO
  • Start date Start date
G

GPO

I have a table of data that looks like this:

Value Date_Of_Value_Change
A 31/12/2003
B 14/1/2004
A 16/1/2004
B 19/1/2004

What it means is that a thing had a value of A starting on 31/12/2003 and
going up to (but not including) 14/1/2004. 14 days all up for that period.
Then it went to B for two days and so on. I need to work out a query that
will give me (for the period 31/12/2003 to 20 /1/2004):

Time as A: 17 days.
Time as B: 4 days.

Any suggestions gratefully accepted. I need a fairly efficient solution as
the real table will be several hundred thousand records long.

Regards

GPO
 
Hi,


SELECT a.Value,
SUM( DateDiff("d", a.DateOfValueChange, MIN(b.DateOfValueChange)))
AS daysOn

FROM myTable As a INNER JOIN myTable As b
ON a.DateOfValueChange < b.DateOfValueChange

GROUP BY a.Value, a.DateOfValueChange




Hoping it may help,
Vanderghast, Access MVP
 
HI,


I should have remember better that we cannot aggregate on an
aggregate: SUM ( ... MIN( ... ) ). The following would work :




SELECT Value,
SUM( DateDiff("d",DateOfValueChange,LastDate)) AS daysOn

FROM (SELECT a.Value,
a.DateOfValueChange,
MIN(b.DateOfValueChange) As LastDate


FROM myTable As a INNER JOIN myTable As b
ON a.DateOfValueChange < b.DateOfValueChange

GROUP BY a.Value, a.DateOfValueChange ) As z

GROUP BY Value





but note that since the "next" change is unknown, time on B would be
returned as 2 days, not 4. How can we get 4? We know it is on B since 19th
of January, but "up to when" ? If it means "up to now", then change the
INNER JOIN for a LEFT JOIN, and change MIN(b.DateOfValueChange) to
MIN( Nz( b.DateOfValueChange, Now )).




Hoping it may help,
Vanderghast, Access MVP
 
Sorry, ignore my last question :-)
Michel Walsh said:
HI,


I should have remember better that we cannot aggregate on an
aggregate: SUM ( ... MIN( ... ) ). The following would work :




SELECT Value,
SUM( DateDiff("d",DateOfValueChange,LastDate)) AS daysOn

FROM (SELECT a.Value,
a.DateOfValueChange,
MIN(b.DateOfValueChange) As LastDate


FROM myTable As a INNER JOIN myTable As b
ON a.DateOfValueChange < b.DateOfValueChange

GROUP BY a.Value, a.DateOfValueChange ) As z

GROUP BY Value





but note that since the "next" change is unknown, time on B would be
returned as 2 days, not 4. How can we get 4? We know it is on B since 19th
of January, but "up to when" ? If it means "up to now", then change the
INNER JOIN for a LEFT JOIN, and change MIN(b.DateOfValueChange) to
MIN( Nz( b.DateOfValueChange, Now )).




Hoping it may help,
Vanderghast, Access MVP



solution
Click to expand...
 
Back
Top