469,951 Members | 2,681 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,951 developers. It's quick & easy.

quey help


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
Jul 23 '05 #1
2 920
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)
Jul 23 '05 #2
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'

Jul 23 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by wukexin | last post: by
3 posts views Thread by Colin J. Williams | last post: by
7 posts views Thread by Corepaul | last post: by
3 posts views Thread by touf | last post: by
5 posts views Thread by Steve | last post: by
1 post views Thread by ravi | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.