SELECT COUNT up to maximum of...

[sxp04rs]

I have a table of shifts which are entered into a rota application. Let's call them rotaItems. a section of the rotaItems table looks like something like this:

itemId personId weekId dayId hours
=============================================
1 1234 10 2 3
1 1234 10 2 6
1 1234 10 2 7
2 2222 10 2 9
3 5432 10 2 12
4 7890 10 2 9

I need to count how many personId's are working each day, which could be achieved by:

SELECT COUNT(*) FROM rotaItems WHERE weekId=10 AND dayId=2 GROUP BY personId

However....as you can see there might be more than one entry per person per day (if they say work a morning and evening shift). So for personId=1234, 3 would be returned but I only want 1, since it is a person count I want not a shift count. I'm going round in circles trying to work out how, but basically I think I want:

SELECT COUNT(*) ###UP TO MAX OF ONE PER dayId#### FROM rotaItems WHERE weekId=10 AND dayId=2 GROUP BY personId

Thanks in advance!

 

[SkipVought]

Did you not get an answer to this in your previous thread?

Skip,

_{Just traded in my old subtlety...
for a NUANCE!}

 

[sxp04rs]

No I don't think so...It's possible I can use something similar but I can't work out how. Is it something obvious?

 

[PHV]

No I don't think so
Isn't a COUNT(DISTINCT personId) that you want ?
If so, I've already gave you the answer.

 

[sxp04rs]

Hmm on second thoughts I don't think I've worded the question properly...I'll come back in a few minutes and add more detail. I see what you're getting at though SkipVought from what I've written it does look like the same issue.

 

[sxp04rs]

SkipVought and PHV,

You're both right (again, pattern developing here!). I was trying to make it too complicated, after your comments I went back and had a fresh look at it and realised it could be solved with what PHV gave me previously.

Thanks!