CODEWITHSUNDEEP
sqlsql-servergroup-by
Harambe Attack Helicopter
Harambe Attack Helicopter

Reputation: 780

Check next group where specified column is null

I have an approval flow which is grouped by Action (Requestor, Verifier and Approver). The logic is when one of the verifiers have taken action (ActionDate has value), it will display the users from the next group. Meaning, the Verifier group has already verified RequestNo R001 and the request should now be pending to Approver group. What I need is to display the next Action (Approver) and the users.

Approval table

RequestNo | UserName | Action    | Seq | ActionDate
R001      | JohnD    | Requestor | 1   | 01/01/2017
R001      | SamS     | Verifier  | 2   | NULL
R001      | TrishL   | Verifier  | 3   | 01/01/2017
R001      | GeorgeP  | Verifier  | 4   | NULL
R001      | JackF    | Approver  | 5   | NULL
R001      | RobertL  | Approver  | 6   | NULL

Upvotes: 0

Views: 35

Answers (2)

iamdave
iamdave

Reputation: 12243

This can be achieved with a derived table and some window functions which, in my opinion at least is a bit easier to read and understand than several sub-queries on top of one another:

declare @Approval table(RequestNo nvarchar(10),UserName nvarchar(10),Action nvarchar(10),Seq int,ActionDate date);
insert into @Approval values ('R001','JohnD','Requestor',1,'20170101'),('R001','SamS','Verifier',2,NULL),('R001','TrishL','Verifier',3,'20170102'),('R001','GeorgeP','Verifier',4,NULL),('R001','JackF','Approver',5,NULL),('R001','RobertL','Approver',6,NULL);

with c as
(
    select RequestNo
            ,min(Seq) as MinSeq
            ,max(Seq) as MaxSeq
            ,row_number() over (partition by RequestNo order by RequestNo, min(ActionDate), min(Seq)) as rn
    from @Approval
    group by RequestNo
            ,Action
)
select a.*
from @Approval a
    join c
        on(a.RequestNo = c.RequestNo
            and a.Seq between c.MinSeq and c.MaxSeq
            and c.rn = 1
            )
order by a.RequestNo
        ,a.Seq;

Output:

+-----------+----------+----------+-----+------------+
| RequestNo | UserName |  Action  | Seq | ActionDate |
+-----------+----------+----------+-----+------------+
| R001      | JackF    | Approver |   5 | NULL       |
| R001      | RobertL  | Approver |   6 | NULL       |
+-----------+----------+----------+-----+------------+

Adding in additional data to show how it works on the entire set:

declare @Approval table(RequestNo nvarchar(10),UserName nvarchar(10),Action nvarchar(10),Seq int,ActionDate date);
insert into @Approval values
 ('R001','JohnD','Requestor',1,'20170101'),('R001','SamS','Verifier',2,NULL),('R001','TrishL','Verifier',3,'20170102'),('R001','GeorgeP','Verifier',4,NULL),('R001','JackF','Approver',5,NULL),('R001','RobertL','Approver',6,NULL)
,('R002','JohnD','Requestor',1,'20170101'),('R002','SamS','Verifier',2,NULL),('R002','TrishL','Verifier',3,null),('R002','GeorgeP','Verifier',4,NULL),('R002','JackF','Approver',5,NULL),('R002','RobertL','Approver',6,NULL);

Output:

+-----------+----------+----------+-----+------------+
| RequestNo | UserName |  Action  | Seq | ActionDate |
+-----------+----------+----------+-----+------------+
| R001      | JackF    | Approver |   5 | NULL       |
| R001      | RobertL  | Approver |   6 | NULL       |
| R002      | SamS     | Verifier |   2 | NULL       |
| R002      | TrishL   | Verifier |   3 | NULL       |
| R002      | GeorgeP  | Verifier |   4 | NULL       |
+-----------+----------+----------+-----+------------+

Upvotes: 1

SqlZim
SqlZim

Reputation: 38023

Using a correlated subquery to only return those rows where the Action is equal to the "Next" action group (based on seq), eliminating those groups that have a non-null ActionDate with not exists()

select ap.*
from Approval ap 
where ap.Action = (
    select top 1 ac.Action 
    from Approval ac
    where not exists (
      select 1
      from Approval i
      where i.RequestNo = ap.RequestNo
        and i.Action = ac.Action
        and i.ActionDate is not null
      )
    order by ac.seq asc
    )

rextester demo: http://rextester.com/UPCZO55513

returns:

+-----------+----------+----------+-----+------------+
| RequestNo | UserName |  Action  | Seq | ActionDate |
+-----------+----------+----------+-----+------------+
| R001      | JackF    | Approver |   5 | NULL       |
| R001      | RobertL  | Approver |   6 | NULL       |
+-----------+----------+----------+-----+------------+

Upvotes: 1

Related Questions