Sql-server – Exclude rows when a column has matches

sql server

How would I exclude duplicate 'Clients' who are enrolled in other 'Programs'? Example- Client A is enrolled in Program 36 and 37. I only need them in the list for one of those programs.

Here is the query I am running-

SELECT DISTINCT 
  LastFirstName AS Client, 
  DOB, 
  LastFirst AS Worker, 
  DATEADD(DD,839,DOB), 
  DATEADD (DD,1058,dob), 
  Name AS Program, 
  EndDate

FROM Intakes INNER JOIN
        Client ON Intakes.ClientId = Client.ClientId INNER JOIN
        ProgramLkp ON Intakes.Program = ProgramLkp.Code INNER JOIN
        Employees ON Intakes.CaseMgr = Employees.StaffId INNER JOIN
        CaseWorkerHist ON Intakes.KeyId = CaseWorkerHist.IntakeKey

WHERE (Program = 36 OR 
      Program = 37 OR 
      Program = 9) AND 
      EndDate = '2079-12-31'

Thanks.

Best Answer

You can achive this situation using ROW_NUMBER() function. In the example below it will return rows with minimal Program number.

;WITH cte AS (
    SELECT 
      LastFirstName AS Client, 
      DOB, 
      LastFirst AS Worker, 
      DATEADD(DD,839,DOB) AS [Date1], 
      DATEADD (DD,1058,dob) AS [Date2], 
      Name AS Program, 
      EndDate,
      [rn]=ROW_NUMBER() OVER(PARTITION BY Intakes.ClientId ORDER BY Intakes.Program)
    FROM Intakes INNER JOIN
            Client ON Intakes.ClientId = Client.ClientId INNER JOIN
            ProgramLkp ON Intakes.Program = ProgramLkp.Code INNER JOIN
            Employees ON Intakes.CaseMgr = Employees.StaffId INNER JOIN
            CaseWorkerHist ON Intakes.KeyId = CaseWorkerHist.IntakeKey

    WHERE (Program = 36 OR 
          Program = 37 OR 
          Program = 9) AND 
          EndDate = '2079-12-31'
)
SELECT  Client, 
        DOB, 
        Worker, 
        [Date1], 
        [Date2], 
        Program, 
        EndDate
FROM cte
WHERE rn=1

Of course it depends, which verion of SQL Server you are using.

Related Question