Calculating a 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
 
So you want:

container material from to
1 A 4/1 4/6 (5 days)
1 B 4/7 4/9 (3 days)
1 - 4/10 4/13 (4 days)
1 A 4/14 4/20 (7 days)

Right?

I don't have Access on this pc, so I'm reluctant to propose the full sql,
without being able to test it. So let's take it bit by bit.

The next sql should give you the first three columns of output desired:

select a.container, a.material, a.date as "from"
from TheTable as a
where not exists
( select 1 from TheTable as x
where x.container = a.container
and x.material = a.material
and x.date = a.date - 1 )

If that works, the next version will hopefully add the final column:

select a.container, a.material, a.date as "from", max(b.date) as "to"
from TheTable as a, TheTable as b
where not exists
( select 1 from TheTable as x
where x.container = a.container
and x.material = a.material
and x.date = a.date - 1 )
and b.container = a.container
and b.material = a.material
and not exists
( select 1 from TheTable as x
where x.container = b.container
and x.material = b.material
and x.date = b.date + 1 )

If the last version gets a syntax error, try adding this line as the very
last line:

group by a.container, a.material, a.date


HTH!,
TC
 
Back
Top