In this post I would like to focus on a tsql script to select data from a table with unique account ID for accounts but different values for the other columns and the query should be able to select data only if certain column values exist. Lets take an example to give you a clear picture. I have a table whose DDL is:
CREATE TABLE [dbo].[AccountData]([AccName] [nvarchar](255) NULL,[AccID] [float] NULL,[Duration] [float] NULL,[Issue] [nvarchar](255) NULL) ON [PRIMARY]
and the resultset is as shown:
Now I want to select only those records where Issue column has values Phase1,Phase2,Phase3 and Phase4. So if I use the following query it returns me 0 results.
select * from accountdata where issue='Phase1' and issue='Phase2' and issue='Phase3' and issue='Phase4'
I want to get accounts test1 and test4 in the result with all the columns and records. Here is the script for that:
Select a.AccName, a.AccID, a.Duration, a.Issue From dbo.AccountData a Where a.AccName In (Select a1.AccName From dbo.AccountData a1 Where a1.Issue In ('Phase1', 'Phase2', 'Phase3', 'Phase4) Group By a1.AccName Having Count(Distinct a1.Issue) = 4)
Here is the expected result set.
Hope this helps!