Re: Calculating a time

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: TC (no_at_email.here)
Date: 04/08/04 

Date: Thu, 8 Apr 2004 14:03:08 +0930

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

"Jason" <anonymous@discussions.microsoft.com> wrote in message
news:19c2d01c41d09$2faa0800$a501280a@phx.gbl...
> 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
>

Relevant Pages