SQL Server - Exclude all rows with value A only if Value B exists for that userID from same column a-CodePudding

I need a sample query for the following if possible.

I can't seem to get anything to work (I tried if exists, not in, etc..). All I have right now is a basic select statement, I'm just stuck, and my SQL is terrible:

Select
UserID,
Date,
Type,
Step
from TableA

If a userID has rows with "Value A" AND rows with "Value B" (at least one row of Value A) in the "Step" column, then only show rows that have "Value B" (exclude all rows that contain "Value A") by userID.

For example:
Original Table Source:
UserID | Date      | Type | Step
------- ----------- ------ ---------
24261  | 12/4/2021 | Doc  | Value B
24261  | 12/4/2021 | Sig  | Value A
24315  | 12/4/2021 | Sig  | Value A
24331  | 12/4/2021 | Doc  | Value B
24331  | 12/4/2021 | Sig  | Value A
24328  | 12/4/2021 | Text | Value C
24341  | 12/4/2021 | Doc  | Value B
24341  | 12/4/2021 | Sig  | Value A
24341  | 12/4/2021 | Doc  | Value B
24341  | 12/4/2021 | Sig  | Value A
24357  | 12/4/2021 | Text | Value C
24357  | 12/4/2021 | Sig  | Value A
24357  | 12/4/2021 | text | Value C

Expected Output:

UserID | Date      | Type | Step
------- ----------- ------ ---------
24261  | 12/4/2021 | Doc  | Value B
24315  | 12/4/2021 | Sig  | Value A
24331  | 12/4/2021 | Doc  | Value B
24328  | 12/4/2021 | Text | Value C
24341  | 12/4/2021 | Doc  | Value B
24341  | 12/4/2021 | Doc  | Value B
24357  | 12/4/2021 | Text | Value C
24357  | 12/4/2021 | Sig  | Value A
24357  | 12/4/2021 | Text | Value C

Any help would be appreciated.

CodePudding user response：

This option may work - it joins Value B to all User IDs/Date that have it, and then keeps all records that don't have a Value A and Value B in the A.Step and B.Step columns.

Select
 A.UserID,
 A.Date,
 A.Type,
 A.Step
from t as A
LEFT JOIN (
     SELECT DISTINCT
       UserID,
       Date,
       Type,
       Step
     FROM t
     WHERE Step = 'Value B') as B
     ON B.UserID = A.UserID AND B.Date = A.Date
WHERE NOT (A.Step = 'Value A' AND B.Step IS NOT NULL)

Included my query on the fiddle from Stu -

https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=11115c225929e9bd0a46ff3071c00948

CodePudding user response：

you can try using the below query

with stepB AS(
select * from tableA where userID in( 
select distinct userID from tableA where  step ='Value A'
intersect
select distinct userID from tableA where  step ='Value B')
and step ='Value B')
select * from stepB b
union
select * from tableA  a where not exists(select * from stepB where a.step=b.step)

CodePudding user response：

We can use a CTE to determine which records have both Value A and Value B, and only select their non-Value A records, otherwise pull everything:

with cte as 
(
    select t.UserID
    from TableA t
    inner join TableA t2
        on t.UserID = t2.UserID
        and t.Step = 'Value B'
        and t2.Step = 'Value A'
)
select *
from TableA
where (TableA.UserID not in (select UserID from cte))
    or (TableA.UserID in (select UserID from cte) and TableA.Step <> 'Value A')

CodePudding user response：

I originally tried analytic functions but realised it's just more reliable using exists:

with x as (
    select * ,
    case when exists (select * from t t2 where t.userid=t2.userid and t2.step='Value A') then 1 end  
    case when exists (select * from t t2 where t.userid=t2.userid and t2.step='Value B') then 1 end Both
    from t
)
select UserId, date, type, Step
from x
where (Both>0 and step='value b') or both is null
order by UserId

Demo Fiddle

CodePudding user response：

You can do a UNION ALL from the same table SELECT first row with Value B and then userID not existing in user id with Value B

SELECT 
 UserID, 
 Date, 
 Type, 
 Step 
FROM Table WHERE Step = 'Value B'

UNION ALL

SELECT 
 UserID, 
 Date, 
 Type, 
 Step 
 FROM Table t1 
 WHERE Step <> 'Value B' 
    AND NOT EXISTS (
       SELECT 1 
       FROM Table t2 
       WHERE t2.Step = 'Value B' AND t2.UserId = t1.UserId)

CodePudding user response：

… fiddle …

create table t(userid int, step varchar(10));
insert into t(userid, step)
values
(24261, 'Value B'),
(24261, 'Value A'),
(24315, 'Value A'),
(24331, 'Value B'),
(24331, 'Value A'),
(24328, 'Value C'),
(24341, 'Value B'),
(24341, 'Value A'),
(24341, 'Value B'),
(24341, 'Value A'),
(24357, 'Value C'),
(24357, 'Value A'),
(24357, 'Value C');

select *
from t as a
where step <> 'value A'
or (step = 'value A' and not exists(select * from t as b where b.userid=a.userid and b.step='value B'))
order by userid,step;

select *
from
(
select *, max(case when step='value B' then 1 else 0 end) over(partition by userid) as userhasB
from t
) as d
where step <> 'value A' or userhasB = 0;

CodePudding user response：

TESTED: Demo: dbfiddle

STEP Summary:

Get a set of distinct users who have both A and B Steps in a "CTE"
Take tableA master data set and left join to this CTE
limit by one of two possibilities.
the CTE user value is NULL in which case they didn't have both A/B Steps
return only users in the CTE excluding those with Step Value A.

.. laid out

 With TableA As (
SELECT '24261' USERID,'12/4/2021' DateA,'Doc' Type,'Value B' Step UNION ALL
SELECT '24261','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24315','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24331','12/4/2021','Doc ','Value B' UNION ALL
SELECT '24331','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24328','12/4/2021','Text','Value C' UNION ALL
SELECT '24341','12/4/2021','Doc ','Value B' UNION ALL
SELECT '24341','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24341','12/4/2021','Doc ','Value B' UNION ALL
SELECT '24341','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24357','12/4/2021','Text','Value C' UNION ALL
SELECT '24357','12/4/2021','Sig ','Value A' UNION ALL
SELECT '24357','12/4/2021','text','Value C'),

CTE AS (SELECT USERID, count(*) cnt       
        FROM (SELECT distinct userID, Step from TableA WHERE Step in ('Value A', 'Value B')) a
        GROUP BY USERID
        HAVING  Count(*) > 1)
SELECT A.* 
FROM TableA A
LEFT JOIN CTE 
 on A.UserID = CTE.USERID
WHERE (CTE.USERID is not null AND Step <> 'Value A')
   OR (CTE.USERID is null)

Giving us:

 -------- ----------- ------ --------- 
| USERID |   DateA   | Type |  Step   |
 -------- ----------- ------ --------- 
|  24261 | 12/4/2021 | Doc  | Value B |
|  24315 | 12/4/2021 | Sig  | Value A |
|  24331 | 12/4/2021 | Doc  | Value B |
|  24328 | 12/4/2021 | Text | Value C |
|  24341 | 12/4/2021 | Doc  | Value B |
|  24341 | 12/4/2021 | Doc  | Value B |
|  24357 | 12/4/2021 | Text | Value C |
|  24357 | 12/4/2021 | Sig  | Value A |
|  24357 | 12/4/2021 | text | Value C |
 -------- ----------- ------ ---------