Sample Header Ad - 728x90

Set variable on multiple case within each group

0 votes
1 answer
153 views
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 enter image description here **Note** that I don't want to split the statements, need to do above in a single statement.
Asked by Himanshuman (197 rep)
Jul 13, 2023, 10:24 AM
Last activity: Jul 13, 2023, 11:05 AM