I have a two tables in a master detail realtionship. The tables are patients and Eyes. For every one row in the Patient table there can be two rows in the Eyes table (Work that one out!)
I use a CTE to select the data. Here it is:
with cte AS
null as FormId,
F.PersonId as PersonId,
F.id as RecordId,
ER.id as HögerId,
0 as HögerTreatmentRunningCount,
EL.id as VänsterId,
0 as VänsterTreatmentRunningCount,
case F.Approved when 1 then 'Signerad' else '' end as [Status],
case F.Approved when 1 then CONVERT(nvarchar(16), F.ApprovedDateTime, 120) else '' end as Signeratdatum
from Patient F
left outer join Eye ER on F.id = ER.OriginalId and ER.Öga = 1
left outer join Eye EL on F.id = EL.OriginalId and EL.Öga = 2
where F.RegisteringUnitId = @unitId
and F.PersonId = @personId
select * from cte
in the Eyes table there is a date column called VisitDate.
You can see that the CTE returns data based on the personid and unitid parameters.
I know there there is only one row in the Patients table for each patient, and one or two rows in the Eyes table for each patient.
If the dates of the visits (VisitDate) are the same for both rows in the Eyes table then i want to return one row, if they are different i want to return two rows.
Is this possible?