Set variable on multiple case within each group

Question

Say I have below table

cis	aid	cid	apid	ntime	buid	flg	jid
30	1208	229067737	1026	9	DDDD400C	0	0
30	1209	229067737	1026	11	DDDD400C	0	0
30	0	229067737	1026	12	DDDD400C	1	100
30	1210	229067737	1026	13	DDDD400C	0	0
30	0	229067737	1026	14	DDDD400C	1	101

CREATE TABLE Aiarcle
(cis INT, aid INT, cid INT, apid INT, ntime INT, buid VARCHAR(MAX), flg INT, jid INT) 
INSERT INTO Aiarcle
(cis, aid, cid, apid, ntime, buid, flg, jid) 
VALUES (30, 1208, 229067737, 1026, 9, 'DDDD400C', 0, 0)
      ,(30, 1209, 229067737, 1026, 11, 'DDDD400C', 0, 0)
      ,(30,    0, 229067737, 1026, 12, 'DDDD400C', 1, 100)
      ,(30, 1210, 229067737, 1026, 13, 'DDDD400C', 0, 0)
      ,(30,    0, 229067737, 1026, 14, 'DDDD400C', 1, 101);

Now I need to do following here. First filter out rows WHERE ntime > 10.

SELECT * FROM Aiarcle WHERE ntime > 10

Then all I need to do is check in a single statement if flg 0 and flg 1 esists in my table and set both variables.

This is what I did, but this does not works.

DECLARE @flg0Exists BIT = 1
DECLARE @flg1Exists BIT  = 1


SELECT @flg0Exists = CASE WHEN flg = 0 THEN 1 ELSE 0 END,
       @flg1Exists = CASE WHEN flg = 1 THEN 1 ELSE 0 END
       FROM Aiarcle WITH (NOLOCK) WHERE nTime > 10
       
SELECT @flg0Exists, @flg1Exists

The output of which was

Note that I don't want to split the statements, need to do above in a single statement.

Answer 1

You can use MIN and MAX to check if flag 0 and 1 exists.
IF MIN(flg) = 0 this means that flag 0 exists
IF MAX(flg) = 1 this means that flag 1 exists.

DECLARE @flg0Exists BIT = 1;
DECLARE @flg1Exists BIT  = 1;

SELECT @flg0Exists = CASE WHEN src.min_flg = 0 THEN 1 ELSE 0 END,
       @flg1Exists = CASE WHEN src.max_flg = 1 THEN 1 ELSE 0 END
FROM
(
SELECT MIN(Flg) as min_flg,
      MAX(flg) as max_flg
FROM Aiarcle
WHERE nTime > 10
)as src;

SELECT @flg0Exists as flg0Exists, @flg1Exists as flg1Exists;

flg0Exists	flg1Exists
1	1

dbfiddle

IF flg can have other values besides 0 and 1 , you can filter out the records to 0 and 1 , like this:

SELECT MIN(Flg) as min_flg,
      MAX(flg) as max_flg
FROM Aiarcle
WHERE nTime > 10
  and flg >=0
  and flg <=1