Excuse me for not putting a better name to this but I cannot think of a
suitable subject title!

anyway, here's what I am trying to do

At work we have a fair amount of data that is to do with people and what
jobs they have completed and at what date
so for example
Joe Bloggs did design work at 12/12/2004 and spent 5 hours on it.
Now, we have a lot of jobs that people haven't put any hours down for at
all,
and I want to find all those jobs which have had no hours put to them since
the 30/09/2004
I had written a query like so
SELECT name, job, date
FROM table
WHERE hours = 0 AND date >='30/09/2004'
But this doesn't work as some jobs may come up which have had zero hours on
some particular dates but on some dates that job does have hours on.
What I want is to show since 30/09/2004 any jobs which have had no hours at
all booked to them consistently.
So in effect all the jobs we know longer use and haven't since that date.

How would I do that?
I know you can probably only give me a vague idea as I have provided no
table structure etc.. but I only want a hint really...

Thanks

Jayne

On Tue, 26 Apr 2005 20:32:23 +0100, Little PussyCat wrote:

(snip)
>What I want is to show since 30/09/2004 any jobs which have had no hours at
>all booked to them consistently.
>So in effect all the jobs we know longer use and haven't since that date.
>
>How would I do that?

Hi Jayne,

Try if this works:

SELECT job
FROM table
WHERE date >= '20040930' -- Note the unambiguous date format!
GROUP BY job
HAVING SUM(hours) = 0

However, this requires that there are rows for the job in the table, with
a number of hours equal to 0. If the table has no rows in the specified
date range for the the job, then this query won't find them.

>I know you can probably only give me a vague idea as I have provided no
>table structure etc.. but I only want a hint really...

If the above doesn't work and you need better hints, then check out this
site before posting a followup: www.aspfaq.com/5006.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)

Hi Jayne,

Would something like this work?

SELECT
t.name,
t.job,
t.date
FROM
table t join
(select name, job, sum(hours) as hours from table where date
>='30/09/2004' group by name, job having sum(hours)=0)dt
on t.name=dt.name and t.job=dt.job
WHERE
t.date >='30/09/2004'