How to query database to find date where only one employee worked

[Dylan Design]

Hi,

here is as example of the data I'm working with..

AdminDB.Roster

Date Name
03/10/2010 Bob
03/10/2010 Bob
03/10/2010 Dan
03/10/2010 Bob

04/10/2010 Bob
04/10/2010 Bob
04/10/2010 Bob
04/10/2010 Bob
04/10/2010 Bob
04/10/2010 Bob

05/10/2010 Bob
05/10/2010 Bob
05/10/2010 Dan
05/10/2010 Dan

Basically there are numerous entries per day which represent shifts and who worked those shifts.

What I want to do is know any day where only one person worked ALL shifts, keeping in mind there could be between 3 and 10 entries of '04/10/2010- Bob' (E.g)

E.g. Count the rows for distinct date; count the rows for distinct date AND name; show me the date where both counts are the same result.

So the result should be '04/10/2010' because only one person worked the total amount of shifts that day.

Does this make sense? can anyone please help?

ALSO. 'date' and 'name' are seperate fields. And I only want to know the date if it was 'Bob' who worked all shifts.

THANK YOU

[ck9663]

Yes it does...

Try this:

select [date], count(name) from YourTable
group by [date]
having count(name) = 1

Happy Coding!!!

~~ CK

[Dylan Design]

ck9663,

thank you very much for your response, but this will only work if there is only one entry for 'bob' on any date - see my data, there are 6 entries for 'bob' on the one date...

[ck9663]

Because you said you're looking for dates with only 1 employee worked. Try removing the where clause. It'll give you the count of employee per date.


Good Luck!!!

~~ CK

[NeoPa]

Dylan:
but this will only work if there is only one entry for 'bob' on any date

Perhaps you should try it Dylan. This will work with your data (unless of course there's something which you haven't told us about it).

[Dylan Design]

Hi,

just letting you know I've figured it out and this is what I was after:

select name, date from table
group by name, date
having COUNT(distinct name) = 1

It's the 'distinct' inside of the count which helped, I didn't realise this could be done. Thanks!

[NeoPa]

Bizarre. This doesn't look to me like it could possibly work reliably, as you're GROUPing by the name itself.

Perhaps you wouldn't mind posting the exact results you get from running this on your posted example data.

[Dylan Design]

Certainly,

the result is 'Bob, 04/10/2010'

It's grouping by the name and date, then giving me the name and date where the distinct count of name is '1'.

This is exactly what I wanted to know: any date where only one distinct employee (name) worked. This does not mean there is only ONE entry for the date; as per my example, there could be six entries on the one date, but still only ONE DISTINCT name...

Hope this makes sense?

Thanks!

[Dylan Design]

Hello again one last time.

Sorry to have misinformed you guys! NeoPa, having a closer look at the example I've provided, I can understand your confusion!

Basically I am working with some very sensitive data and so I had to use 'make-believe' information in my example... In translating the example I have not made an exact representation of what I was trying to do...

Here is a CORRECT example, the query I used, and the output.. thanks!


Problem: find any date where the employee worked in only ONE department

DATA:
Date-------Name--Department
03/10/2010-Bob---A
03/10/2010-Bob---A
03/10/2010-Dan---A
03/10/2010-Bob---B

04/10/2010-Bob---A
04/10/2010-Bob---A
04/10/2010-Bob---A
04/10/2010-Bob---A
04/10/2010-Bob---A
04/10/2010-Bob---B

05/10/2010-Bob---A
05/10/2010-Bob---B
05/10/2010-Dan---B
05/10/2010-Dan---B

QUERY:

Select Name, Date from table
group by Name, Date
having COUNT(distinct department) = 1 
order by 2 desc, 1

OUTPUT:
Date-------Name--Department
05/10/2010-Dan---B
03/10/2010-Dan---A


Thank you! (and apologies)

[NeoPa]

Hi Dylan.

I'm afraid there are still inconsistencies with what you're posting. I'm in a position where it's hard to judge how significant this is, but your output includes the field [Department] which is not included in your SQL SELECT clause, as well as the fields [Date] & [Name] in reverse order from the SQL.

That said, I think I now see why the DISTINCT predicate within the COUNT() call makes sense and actually has the desired effect. This is a workable solution clearly.

An alternative, that is possibly easier to understand and work with (as well as possibly more efficient too), would be to include the DISTINCT predicate in the more usual place after the SELECT of the SELECT clause. I've included SQL below to illustrate how it may be done :

SELECT DISTINCT
         [Date]
       , [Name]
       , [Department]
FROM     [Table]
GROUP BY [Date]
       , [Name]
HAVING   COUNT([Department]) = 1
ORDER BY [Date] DESC
       , [Name]

[Dylan Design]

Hey NeoPa,

I've tried your solution, I receive the following error...

Msg 8120, Level 16, State 1, Line 4
Column 'table.departme nt' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

[NeoPa]

Ah, I missed that. That makes absolute sense of course. It looks like the DISTINCT predicate within the COUNT() call is the only viable solution after all (certainly that I can see).