CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2012
Kyrive
Kyrive

Reputation: 77

TSQL - Filter group if it contains all null

I would like to filter out the group which contains all null values for TASK_START & TASK_END which will be B and D.

Sample Table Data

+----+-------+-------------------------+-------------------------+-------------------------+-------------------------+
| ID | STATE |       ENTER_STATE       |       LEAVE_STATE       |       TASK_START        |        TASK_END         |
+----+-------+-------------------------+-------------------------+-------------------------+-------------------------+
| A  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 08:30:00.000 | 2018-11-11 08:00:00.000 | 2018-11-11 08:10:00.000 |
| A  | UP    | 2018-11-11 09:00:00.000 | 2018-11-11 09:30:00.000 | NULL                    | NULL                    |
| A  | UP    | 2018-11-11 10:00:00.000 | 2018-11-11 10:30:00.000 | 2018-11-11 08:20:00.000 | 2018-11-11 08:30:00.000 |
| B  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 09:00:00.000 | NULL                    | NULL                    |
| B  | UP    | 2018-11-11 09:00:00.000 | 2018-11-11 10:00:00.000 | NULL                    | NULL                    |
| B  | UP    | 2018-11-11 10:20:00.000 | 2018-11-11 11:00:00.000 | NULL                    | NULL                    |
| B  | UP    | 2018-11-11 11:00:00.000 | 2018-11-11 12:00:00.000 | NULL                    | NULL                    |
| C  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 08:20:00.000 | 2018-11-11 08:15:00.000 | 2018-11-11 08:30:00.000 |
| C  | UP    | 2018-11-11 08:20:00.000 | 2018-11-11 08:30:00.000 | 2018-11-11 08:20:00.000 | 2018-11-11 08:35:00.000 |
| D  | UP    | 2018-11-11 08:00:00.000 | 2018-11-11 08:10:00.000 | NULL                    | NULL                    |
| D  | UP    | 2018-11-11 08:10:00.000 | 2018-11-11 09:10:00.000 | NULL                    | NULL                    |
+----+-------+-------------------------+-------------------------+-------------------------+-------------------------+

Upvotes: 1

Views: 345

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269713

I would simply use count() with having:

select id
from table t
group by id
having count(task_start) = 0 and
       count(task_end) = 0;

Upvotes: 0

gotqn
gotqn

Reputation: 43636

Try this:

DECLARE @DataSource TABLE
(
    [ID] CHAR(1)
   ,[STATE] CHAR(2)
   ,[ENTER_STATE] DATETIME2(0)
   ,[LEAVE_STATE] DATETIME2(0)
   ,[TASK_START] DATETIME2(0)
   ,[TASK_END] DATETIME2(0)
);

INSERT INTO @DataSource
VALUES ('A', 'UP', '2018-11-11 08:00:00.000', '2018-11-11 08:30:00.000', '2018-11-11 08:00:00.000', '2018-11-11 08:10:00.000')
      ,('A', 'UP', '2018-11-11 09:00:00.000', '2018-11-11 09:30:00.000', NULL, NULL)
      ,('A', 'UP', '2018-11-11 10:00:00.000', '2018-11-11 10:30:00.000', '2018-11-11 08:20:00.000', '2018-11-11 08:30:00.000')
      ,('B', 'UP', '2018-11-11 08:00:00.000', '2018-11-11 09:00:00.000', NULL, NULL)
      ,('B', 'UP', '2018-11-11 09:00:00.000', '2018-11-11 10:00:00.000', NULL, NULL)
      ,('B', 'UP', '2018-11-11 10:20:00.000', '2018-11-11 11:00:00.000', NULL, NULL)
      ,('B', 'UP', '2018-11-11 11:00:00.000', '2018-11-11 12:00:00.000', NULL, NULL)
      ,('C', 'UP', '2018-11-11 08:00:00.000', '2018-11-11 08:20:00.000', '2018-11-11 08:15:00.000', '2018-11-11 08:30:00.000')
      ,('C', 'UP', '2018-11-11 08:20:00.000', '2018-11-11 08:30:00.000', '2018-11-11 08:20:00.000', '2018-11-11 08:35:00.000')
      ,('D', 'UP', '2018-11-11 08:00:00.000', '2018-11-11 08:10:00.000', NULL, NULL)
      ,('D', 'UP', '2018-11-11 08:10:00.000', '2018-11-11 09:10:00.000', NULL, NULL);

WITH DataSource AS
(
    SELECT *
         ,MAX([TASK_START]) OVER (PARTITION BY [ID]) AS [DateStart]
         ,MAX([TASK_END]) OVER (PARTITION BY [ID]) AS [DateEnd]
    FROM @DataSource
)
SELECT *
FROM DataSource
WHERE NOT ([DateStart] IS NULL AND [DateEnd] IS NULL);

enter image description here

The idea is to get the maximum(or minimum if you like) date for each group and then, if there are rows where this value is NULL to exclude them.

Upvotes: 2

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You can use not exists :

select t.*
from table t
where not exists (select 1 
                  from table t1 
                  where t1.id = t.id and t1.task_start is not null and t1.task_end is not null
                 );

Other option would to use GROUP BY :

select id
from table t
group by id
having sum(case when task_start is not null then 1 else 0  end) = 0 and
       sum(case when task_end is not null then 1 else 0  end) = 0;

Upvotes: 2

Related Questions