Selecting only "finished" groups

[wbodger]

OK, let's say that I have a table with 5 columns: ID, Name, ProviderId, Date, Amount

Now, let's say that I fill in 4 of the fields and the client has to fill in the fifth field (in this case, ProviderId)

Is there a nice way to ONLY select the Name (they will be grouped by name and date) for those that have filled in ProviderId for ALL records for a given day?

1, Joe, 123, 3/6/2017, 20
2, Joe, 234, 3/6/2017, 38
3, Joe, , 3/6/2017, 25
4, Jill, 156, 3/6/2017, 40
5, Jill, 321, 3/6/2017, 12

So, I want to select Jill because she did her job and has given me the ProviderId for her transactions, but not Joe, because he did not finish all of them.

Thanks,
Willie

 

[Olaf Doschke]

Untested:

SELECT Name, Date FROM yourtable GROUP BY Name, Date Having COUNT(*)=COUNT(ProviderID)

Bye, Olaf.

 

[wbodger]

Hmm... Thx, I'll give it a shot

 

[Olaf Doschke]

I'm pretty sure it works, it just depends on the gaps being NULL values. An empty field is something COUNT(field) will count, too.

Bye, Olaf.