I have a requirement which needs to fetch all the distinct records for key which has a particular value for all rows, and not if it has some other value in other rows.
Select TOP (1000) [Old_Branch_Code] ,[Computer_Name] ,[Migrated] ,[Migration_Date] FROM [TestDB].[dbo].[ABS_Computer_Details]
So, if the table ABS_Computer_Details contains the records like
1, 'X', 'SUCCESS','12/12/12' 1, 'Y', 'SUCCESS','12/12/12' 1, 'Z', 'SUCCESS','12/12/12' 1, 'W', 'Failed','12/12/12' 2, 'Y', 'SUCCESS','12/12/12' 2, 'Z', 'SUCCESS','12/12/12' 3, 'Z', 'Failed','12/12/12' 4, 'Z', 'SUCCESS','12/12/12'
So, it should only select branch_code = 2 and branch_code = 4; because one of the column for branch_code = 1 and branch_code = 3 has status = 'Failed'.
select Old_Branch_Code from dbo.ABS_Computer_Details group by Old_Branch_Code,Migrated having Migrated='SUCCESS'
But, my query is returning values for branch_code = 1, branch_code = 2 and branch_code = 4. As per my requirement, I should've got branch_code = 2 and branch_code = 4 only.
Off-topic: I know this is very trivial, but I'm poor with DB queries as I'm not a DBA guy.
One approach here is to use conditional aggregation in the
HAVING clause which checks for the presence of any migration other than
SUCCESS. If we can't find anything other than
SUCCESS then we retain that branch code.
SELECT Old_Branch_Code FROM dbo.ABS_Computer_Details GROUP BY Old_Branch_Code HAVING SUM(CASE WHEN migrated != 'SUCCESS' THEN 1 ELSE 0 END) = 0