Finding length of time

  • Thread starter Thread starter Jason
  • Start date Start date
J

Jason

I have a table that stores container contents on a daily
basis. The fields of the table are:
ID (autonumber)
Date
Shift (Day or Night)
Container
Material

I would like a query that will tell me how long a
particular container was filled with a particular material.

For example, if Container 1 contained material A from 4/1 -
4/6, I'd like to see a result of 5 days. The problem is
that a given container might contain material A for 5
days, then material B for 3 days, then be empty of 4 days,
then contain material A again for another 7 days. I tried
using a totals query with a min & max function, but then
the result of the above example is that container 1 held
material A for 12 days, B for 3 days, and empty for 4
days. I want the two separate instances for material A
separated.

Any ideas?

Thanks in advance,

Jason
 
Hi,

With:

Containers
ThisIs ContainerID DateTime MaterialID
1 1 2001-01-01 A
2 1 2001-02-02 B
3 1 2001-03-03 C
4 1 2001-04-04 A
5 1 2001-05-05 C
6 2 2001-01-02 B
7 2 2001-02-01 A
8 2 2001-03-04 A
9 1 2001-03-22 A
10 1 2001-04-28 C



then:

SELECT a.ContainerID,
LAST(a.MaterialID),
MIN(b.DateTime)-a.DateTime
FROM Containers As a INNER JOIN Containers AS b
ON a.ContainerID=b.ContainerID AND a.DateTime <b.DateTime
GROUP BY a.containerID, a.DateTime


return the amount of days each product is know to have been in a container:

ContainerID Expr1001 Expr1002
1 A 32
1 B 29
1 C 19
1 A 13
1 A 24
1 C 7
2 B 30
2 A 31


It is then just a matter to make another GROUP BY on each of the two fields,
SUM the third.


But. But observe that I have only 8 results, instead of 10. That can be
understand as the latest occupation for each container is not closed. As
example, what occur after 2001-05-05 for container 1? Is it that, as today,
material C is still trapped inside it? If so, change the INNER for LEFT,
you would get:



ContainerID Expr1001 Expr1002
1 A 32
1 B 29
1 C 19
1 A 13
1 A 24
1 C 7
1 C

2 B 30
2 A 31
2 A





and you can change the NULL for today date. Then

SELECT Container, Material, SUM(occupation)
FROM (SELECT a.ContainerID As Container,
LAST(a.MaterialID) as Material,
Nz(MIN(b.DateTime), Date())-a.DateTime As occupation
FROM Containers As a LEFT JOIN Containers AS b
ON a.ContainerID=b.ContainerID AND a.DateTime
<b.DateTime
GROUP BY a.containerID, a.DateTime ) As z
GROUP BY Container, Material


would produce:

Container Material SumOfoccupation
1 A 69
1 B 29
1 C 1095
2 A 1162
2 B 30




Hoping it may help,
Vanderdhast, Access MVP
 
Back
Top