Checking if all values for user_id IS NOT NULL-CodePudding

I have dataset which looks like this:

UserID  AccountID   CloseDate
1        1000       14/3/2022
1        2000       16/3/2022
2        1000       NULL
2        2000       4/3/2022
2        3000       NULL

And I would like to check if within one user_id all of the close dates are not null. In other words if all accounts within user_id are closed. I was trying using MAX or MIN but it is not working as I expected, because it is simply avoiding NULL values. Is there any other function which can check it? Let's say that my output would be another column which will assign 1 when all CloseDates are not null and else 0.

Sample output:

UserID  AccountID   CloseDate   Check
1        1000       14/3/2022   1
1        2000       16/3/2022   1
2        1000       NULL        0
2        2000       4/3/2022    0
2        3000       NULL        0

CodePudding user response：

Use conditional aggregation to explicitly COUNT the rows where the column has the value NULL:

SELECT GroupedColumn,
       COUNT(CASE WHEN NullableColumn IS NULL THEN 1 END) AS NullCount
FROM dbo.YourTable
GROUP BY GroupedColumn;

If you want to just have a 1 or 0 just wrap the count in a CASE expression:

CASE COUNT(CASE WHEN NullableColumn IS NULL THEN 1 END) WHEN 0 THEN 1 ELSE 0 END

CodePudding user response：

You can try to use FIRST_VALUE condition window function

SELECT *,
       FIRST_VALUE(IIF(CloseDate IS NULL,0,1)) OVER(PARTITION BY UserID ORDER BY CloseDate )
FROM T

sqlfiddle

CodePudding user response：

with dataset as (select 1 as UserId, 1000 as AccountID, '14/3/2022' as CloseDate 
                 union all select 1, 2000, '16/3/2022' 
                 union all select 2, 1000, NULL 
                 union all select 2, 2000, '4/3/2022' 
                 union all select 2, 3000, NULL)


select userid from dataset 
group by userid 
having sum(case when closedate is null then 1 else 0 end) = 0;

CodePudding user response：

select d.*, iif(chk>0, 0, 1) chk
from d
outer apply (
    select UserId, COUNT(*) CHK
    from d dd
    WHERE d.UserId = dd.UserId
    and dd.CloseDate IS NULL
    group by UserId
) C