How to get data with different status on SQL Server [closed]-CodePudding

I have a table as follow:
all row table is here


Id	AccountId	Type	Amount	Status	Created_at	Updated_at
2	C464E5A9-CEF9-4577-9FC3-183403AAADEB	1	10000.00	2	2021-09-13 14:03:52.7780671 07:00	NULL
3	3088452E-B230-4B61-9128-11DD9286FA26	1	10000.00	2	2021-09-13 14:05:52.5501061 07:00	NULL
4	B91C6366-191E-48F5-BAA1-3697A8416083	1	10000.00	2	2021-09-13 14:05:53.1281797 07:00	NULL
5	8D97DC8D-EB28-4EE6-A61C-631E897F4BD3	1	10000.00	1	2021-09-13 14:05:53.6752030 07:00	NULL
6	7B0F2AE8-51BB-4DF1-9F9E-865D367B1A0F	1	10000.00	1	2021-09-13 14:05:54.1752709 07:00	NULL
7	E30CB16F-0A9B-4FC1-B4A1-0C39F6819823	1	10000.00	2	2021-09-13 14:05:54.5555605 07:00	NULL
8	D3E7A826-10C5-4747-8CF5-CA878C2DF9BE	1	10000.00	1	2021-09-13 14:05:54.9322973 07:00	NULL
9	FD9247EF-A000-49C3-9BD9-DF5E57BEC012	1	10000.00	1	2021-09-13 14:05:55.3237830 07:00	NULL
10	4181A51D-022D-4E71-97F5-6916C0B7ED49	1	10000.00	1	2021-09-13 14:05:56.1680657 07:00	NULL
11	1857DD27-8DE5-44B7-A9EB-A87306E0A061	1	10000.00	1	2021-09-13 14:05:56.4931063 07:00	NULL

I want to query all unique AccountId where Type is 1 and don't have Status = 1 as opposite the data below.


Id	AccountId	Type	Amount	Status	Created_at	Updated_at
5	8D97DC8D-EB28-4EE6-A61C-631E897F4BD3	1	10000.00	1	2021-09-13 14:05:53.6752030 07:00	NULL
6	7B0F2AE8-51BB-4DF1-9F9E-865D367B1A0F	1	10000.00	1	2021-09-13 14:05:54.1752709 07:00	NULL
8	D3E7A826-10C5-4747-8CF5-CA878C2DF9BE	1	10000.00	1	2021-09-13 14:05:54.9322973 07:00	NULL
9	FD9247EF-A000-49C3-9BD9-DF5E57BEC012	1	10000.00	1	2021-09-13 14:05:55.3237830 07:00	NULL
10	4181A51D-022D-4E71-97F5-6916C0B7ED49	1	10000.00	1	2021-09-13 14:05:56.1680657 07:00	NULL
11	1857DD27-8DE5-44B7-A9EB-A87306E0A061	1	10000.00	1	2021-09-13 14:05:56.4931063 07:00	NULL

I have try this query :

Select AccountId From 
( select AccountId, 
  ROW_NUMBER() OVER(PARTITION BY AccountId ORDER BY AccountId) AS RowNumber 
  from TempData1 
  where [Status] = 1
  and [Type] =1
) as a 
Where a.RowNumber is null or a.RowNumber <= 0 
group by AccountId

but the result is null, anyone can help

Updated

here some data while I select all AccountId where the status is 2


AccountId	Type	Status
E30CB16F-0A9B-4FC1-B4A1-0C39F6819823	1	2
3088452E-B230-4B61-9128-11DD9286FA26	1	2
2BCDB566-D5F6-4966-95B8-12457466F74D	1	2
C464E5A9-CEF9-4577-9FC3-183403AAADEB	1	2
B91C6366-191E-48F5-BAA1-3697A8416083	1	2
8D97DC8D-EB28-4EE6-A61C-631E897F4BD3	1	2
4181A51D-022D-4E71-97F5-6916C0B7ED49	1	2
7B0F2AE8-51BB-4DF1-9F9E-865D367B1A0F	1	2
1857DD27-8DE5-44B7-A9EB-A87306E0A061	1	2
D3E7A826-10C5-4747-8CF5-CA878C2DF9BE	1	2
FD9247EF-A000-49C3-9BD9-DF5E57BEC012	1	2

How to get all AccountId where don't have status = 1 this the expected result:


AccountId	Type	Status
E30CB16F-0A9B-4FC1-B4A1-0C39F6819823	1	2
3088452E-B230-4B61-9128-11DD9286FA26	1	2
2BCDB566-D5F6-4966-95B8-12457466F74D	1	2
C464E5A9-CEF9-4577-9FC3-183403AAADEB	1	2
B91C6366-191E-48F5-BAA1-3697A8416083	1	2

CodePudding user response：

You want all accounts where Type is 1 and there is no record exists with the same AccountId and Status = 1.

Checking the existence of a correlated record can be achieved by EXISTS.

SELECT DISTINCT AccountId 
FROM TempData1 SRC
WHERE [Type] = 1
  AND [Status] <> 1
  AND NOT EXISTS (SELECT * FROM TempData1 X WHERE X.[Status] = 1 AND X.AccountId = SRC.AccountId)

CodePudding user response：

I want to query all unique AccountId where Type is 1 and don't have Status = 1

You can use aggregation and having:

select accountid
from tempdata1
where type = 1
group by accountid
having sum(case when status = 1 then 1 else 0 end) = 0;

If 1 is the minimum status, you can simplify the code to:

having min(status) > 1

CodePudding user response：

SELECT DISTINCT AccountId 
FROM TempData1 
WHERE [Status] = 1
  AND [Type] <> 1

If you want to count it:

SELECT AccountId, COUNT(*)
FROM TempData1 
WHERE [Status] = 1
  AND [Type] <> 1
GROUP BY AccountId