Question :
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.
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.