Tring to return a group of data

Transact-SQL
1

Hi,
I often have to create a query that re-turns a group of accounts or IDs; or something like this.
This can be in many different forms, but may look something like this:
ID, Name , account number, Paid date

Now lets say it returns three records and one of them has a Paid date that is not null , like this:
ID, Name , Account number, Paid date
1, Bill, 123, NULL
1, Bill, 122, 4/02/2016
1, Bill, 128, 5/02/2016

But then in the table there are records like this:
ID, Name , Account number, Paid date
3, Jim, 1253, 01/06/2016
3, Jim, 1262, 5/02/2016
3, Jim, 1258, 7/02/2016

What we want to return is only the groups of IDs where all the paid date are not null for the records in that group.
I would like to find a set based operation to do this.
Thank you

2

Something like:

select id, name, ... etc..
from table t
where id in (select id from table where [paid date] is not null)
3

Thank you for the reply, but what this does is give me only the ones in a group that have a date
So for example:
If I have
ID, Name , Account number, Paid date
1, Bill, 123, NULL
1, Bill, 122, 4/02/2016
1, Bill, 128, 5/02/2016

It is returning
1, Bill, 122, 4/02/2016
1, Bill, 128, 5/02/2016
I am looking for it to skip this group, because Cust ID 1 has one NULL
and only return customers, that for all their records, has no Paid date that is NULL.
As in the example that I have above.

Thank you

4

ok then:

select id, name, ... etc..
from table t
where id in (select id from table group by id having count(*) = count([paid date]))
5

Sorry I am not sure how this would give me groups that had no NULLs in the Paid date. Am I supposed to combine this with the other one some how?
Remember if I have this group I do not want it:
ID, Name , Account number, Paid date
1, Bill, 123, NULL
1, Bill, 122, 4/02/2016
1, Bill, 128, 5/02/2016

But I do want this one
But then in the table there are records like this:
ID, Name , Account number, Paid date
3, Jim, 1253, 01/06/2016
3, Jim, 1262, 5/02/2016
3, Jim, 1258, 7/02/2016

6

did you try it? The point is, count(*) counts all rows (grouped by id) but count([paid date]) only counts rows where [paid date] date is not null. if both counts are equal, you have an id with no null paid dates.

here's a little test:

declare @table table (id int, pdate date)
insert into @table (id, pdate) values
(1, '20160920'),
(1, '20160921'),
(2, '20160920'),
(2, null)

select id from @table
group by id
having count(*) = count(pdate)
7

Sorrry, no I did not try it because I was not sure if I understood how to apply it, I will do it now and see what I get.
Thanks

8

Hi,
Sorry did not get back sooner, but I did run this and it worked, I just did not understand it at first. But after looking at it a bit and more coffee I see what you have here, it is great another way to look at it.
Thank you

9

I believe this is somewhat clearer as to what you are actually doing, and it is more flexible to allow other checks:

HAVING SUM(CASE WHEN pdate IS NULL THEN 1 ELSE 0 END) = 0