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

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)

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

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)

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')

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

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 |
+--------+-----------+------+---------+

… 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;