Query Q

[stuckness]

Im tring to categorise some records based on a rule which says;

we have a 2 hour grace period, then every thing is categorised different.
we have a bunch of duration values, ie 2hr, .25hr, 1 hr, whatever.
If the event is over 2 hours- then it is category 2.
if we have 1.5hr first, then we have .5hr left of grace- anything longer
will be cat2.

ie, this is what i want the query to return....
..5hr -grace period (first .5hr grace used)
3hrs - cat2
1hr -grace period (now total grace is 1.5hrs)
2hrs -cat2
1hr - cat2 (because now we have only .5hr grace left)
..5hr -grace (now all 2hrs grace is used everything needs to be cat2)

this is driving me nuts- any help appreciated...

 

[John W. Vinson]

Im tring to categorise some records based on a rule which says;

we have a 2 hour grace period, then every thing is categorised different.
we have a bunch of duration values, ie 2hr, .25hr, 1 hr, whatever.
If the event is over 2 hours- then it is category 2.
if we have 1.5hr first, then we have .5hr left of grace- anything longer
will be cat2.

ie, this is what i want the query to return....
.5hr -grace period (first .5hr grace used)
3hrs - cat2
1hr -grace period (now total grace is 1.5hrs)
2hrs -cat2
1hr - cat2 (because now we have only .5hr grace left)
.5hr -grace (now all 2hrs grace is used everything needs to be cat2)

this is driving me nuts- any help appreciated...

What is the structure of your table? How is the "duration" stored? What do the
records that you cite have to do with one another?

Remember - YOU can see your database; you know the meaning of a "period" and a
"grace" in your business. We do not; we can only see what you have posted. So
far that does not include any description of your table.

Give us a bit of help, please!

John W. Vinson [MVP]

 

[stuckness]

yeh sorry. the query is just based on another query with a further filter for
the day in it. The table is very simple, Duration is just stored as a number.
The records are just timed events for the day....

A grace period is name i made to describe the 2 hour limit which im tring to
filter the records apart from the rest of the records which dont fit this
criteria, (over 2hrs long, or the 2hrs had been used).

We need to treat these latter records (non-grace...) different, so any way
of me tagging them somehow is what im looking for.

I hope thats enough relevant info for a real-database mind....?