query question

Change

select * from cte

to

select distinct * from cte

I don't know if you are taking here the best approach. I would create a table only for Patient Visits.
Anyway I worked in your query since you only need a left outer join if you use the case in the select:

with cte AS
  ( select 
      null as FormId,
      F.PersonId as PersonId,
      F.id as RecordId, 
      case E.Ög 
		when 1 then E.id
		else null
	  end as HögerId,
      0 as HögerTreatmentRunningCount,
      case E.Ög 
		when 2 then E.id
		else null
	  end 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 E on F.id = E.OriginalId 
  where F.RegisteringUnitId = @unitId and F.PersonId = @personId
  )

  select * from cte

Select allOpen in new window

Why that second JOIN?

SELECT  F.PersonId ,
        F.id AS RecordId ,
        ER.id AS HögerId ,
        EL.id AS VänsterId ,
        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 ,
        ER.Öga
FROM    Patient F
        LEFT JOIN Eye ER ON F.id = ER.OriginalId
WHERE   F.RegisteringUnitId = @unitId
        AND F.PersonId = @personId;

Select allOpen in new window

There is no VisitDate..

E.g. like

DECLARE @Master TABLE ( ID INT );

INSERT  INTO @Master
VALUES  ( 1 ) ,
        ( 2 );

DECLARE @Child TABLE
    (
      MasterID INT ,
      Value INT ,
      VisitDate DATE
    );

INSERT  INTO @Child
VALUES  ( 1, 11, '20140101' ),
        ( 1, 12, '20140101' ),
        ( 2, 21, '20140101' ),
        ( 2, 22, '20140102' );

WITH    Ordered
          AS ( SELECT   C.* ,
                        ROW_NUMBER() OVER ( PARTITION BY C.MasterID, C.VisitDate ORDER BY NEWID() ) AS RN
               FROM     @Master M
                        INNER JOIN @Child C ON M.ID = C.MasterID
             )
    SELECT  O.MasterID ,
            O.Value ,
            O.VisitDate
    FROM    Ordered O
    WHERE   O.RN = 1;

Select allOpen in new window

Caveat: Your describtion results in a non-deterministic result.

But working on your question, check if this query returns what you want:

select 
      null as FormId,
      F.PersonId as PersonId,
      F.id as RecordId, 
      (select Eye.id
		from Eye
		where F.id = Eye.OriginalId and Ög = 1) as HögerId,
      0 as HögerTreatmentRunningCount,
      (select Eye.id
		from Eye
		where F.id = Eye.OriginalId and Ög = 2) 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
  where F.RegisteringUnitId = @unitId and F.PersonId = @personId

Select allOpen in new window

You are not selecting the VisitDate in your SELECT clause. If 2 rows having the same information with different VisitDate, how will you able to distinguish the records then?

Include VisitDate in your SELECT clause and use the DISTINCT keyword in the final SELECT statement as mentioned by Vitor.

Philip's suggested the DISTINCT keyword, not me.
I don't think distinct will help here since he's returning a row for each eye unless he remove from the select the information from the eyes.

Else, he will have to add the VisiDate to the SELECT clause in CTE and a GROUP BY clause. He will not be able to differentiate the records then.

Sure. Visit date is the most important here.

Thanks for all the response!

Yes VisitDate should be in the CTE.  I was thinking along the lines of testing the value for right eye.  If it was not null then i would use it, and if it was null i would use the value for left eye.

The thinking here is that if right eye was not null then the left eye could only be null or the same value.

Does that clear up matters a little?

How do you differentiate whether the row is Right eye or Left eye? By the row value/text?

Do you have a column something like eye_type?

That's why my suggestion to have a PatientVisit table instead of a column in Eye table (you need to repeat the data for the other eye).
Anyway, do you really need to bring the data from the eyes to get the visit date? Because if you don't, then this query can be a solution:

select 
      null as FormId,
      F.PersonId as PersonId,
      F.id as RecordId, 
      (select MAX(VisitDate)
		from Eye
		where F.id = Eye.OriginalId) as VisitDate,
     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
  where F.RegisteringUnitId = @unitId and F.PersonId = @personId

Select allOpen in new window

soozh,

I suggest:

excluding names or data that could identify a person... please provide a few sample records from both tables
and from those records, provide an "expected result"

words alone are not adequately defining the problem as there are many possible interpretations.

------
For general consumption:
Eye specialists prefer to operate on a single eye at each visit as "risk mitigation"
(i.e. so a person can have one working eye while the other heals)
but this is not always possible

so the EYE table can have 1 record per visit, or it can have 2 records for a visit

hence these are possible records from a single visit:

Patient & Left Eye
Patient, & Right Eye
Patient & Left Eye & Right Eye

The EYE table looks something lie this:

PatientID, Eye,   Treament,    VisitDate ...
1              Left       ABC
1              Left       ABC
2              Right     DEF
2              Right     ABC
1              Right     ABC
1              Right     ABC

hence 2 left joins are necessary choosing right or left per join (or you could do one join and a group by based on visit date)

these background facts have been gleaned by answering a few similar questions at EE
----

I predict you will need VisitDate in the query logic, but I am not convinced distinct is the magic solution.

i am stuck with the stuctures of the Patient and Eyes tables.  A patient can make two different visits to look at his or her eyes so therefore the VisitDate is held in the Eyes table.

The requirement is the same that i need to return one row if both dates are on the same date, two rows otherwise.

I will look at the distict solution.

>>How do you differentiate whether the row is Right eye or Left eye?

by [Öga] = 1 or [Öga] = 2

that's in the given CTE

PLEASE provide sample data and expected result...

Here is a working model http://sqlfiddle.com/#!3/201cb/1

But it lacks representative data particularly with respect to VisitDate

If you could improve that sample data AND tell us what you are looking for as a result then we can do something concrete.

**MS SQL Server 2008 Schema Setup**:

    
    
    CREATE TABLE Patient 
    	([ID] int, [PersonId] int, [RegisteringUnitId] int, [Approved]int, [ApprovedDateTime] datetime)
    ;
    	
    INSERT INTO Patient 
    	([ID], [PersonId], [RegisteringUnitId], [Approved], [ApprovedDateTime])
    VALUES
    	(1, 1, 1, 9, '2014-01-01 00:00:00'),
    	(2, 2, 2, 9, '2014-02-02 00:00:00'),
    	(3, 3, 3, 9, '2014-03-03 00:00:00')
    ;
    
    CREATE TABLE Eye
    	([ID] int, [OriginalId] int, [Öga] int, [VisitDate] datetime)
    ;
    	
    INSERT INTO Eye
    	([ID], [OriginalId], [Öga], [VisitDate])
    VALUES
    	(1, 1, 1, '2014-01-01 00:00:00'),
    	(2, 1, 2, '2014-01-01 00:00:00'),
    	(3, 2, 1, '2014-02-02 00:00:00'),
    	(4, 3, 2, '2014-03-03 00:00:00'),
    	(5, 1, 1, '2014-04-01 00:00:00'),
    	(6, 1, 2, '2014-04-01 00:00:00'),
    	(7, 2, 1, '2014-05-03 00:00:00'),
    	(8, 3, 2, '2014-06-01 00:00:00')
    ;

**Query 1**:

    declare @unitId int = 1
    declare @personId int = 1
    
    ;with cte AS
      ( select 
          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

**[Results][2]**:
    
    | FORMID | PERSONID | RECORDID | HÖGERID | HÖGERTREATMENTRUNNINGCOUNT | VÄNSTERID | VÄNSTERTREATMENTRUNNINGCOUNT | STATUS | SIGNERATDATUM |
    |--------|----------|----------|---------|----------------------------|-----------|------------------------------|--------|---------------|
    | (null) |        1 |        1 |       1 |                          0 |         2 |                            0 |        |               |
    | (null) |        1 |        1 |       1 |                          0 |         6 |                            0 |        |               |
    | (null) |        1 |        1 |       5 |                          0 |         2 |                            0 |        |               |
    | (null) |        1 |        1 |       5 |                          0 |         6 |                            0 |        |               |



  [1]: http://sqlfiddle.com/#!3/201cb/1

Select allOpen in new window

PortletPaul your data is spot on with a slight modification:

INSERT INTO Eye
      ([ID], [OriginalId], [Öga], [VisitDate])
VALUES
      (1, 1, 1, '2014-01-01 00:00:00'),
      (2, 1, 2, '2014-01-01 00:00:00'),
      (3, 2, 1, '2014-02-02 00:00:00'),
      (4, 2, 2, '2014-03-03 00:00:00'),
      (5, 3, 1, '2014-04-01 00:00:00'),
      (6, 3, 2, '2014-04-01 00:00:00')

Max two rows per patient, and some may only have one.

I am looking for a result that gives me data from boths eyes in a single row if the visitdate is the same, and two rows when it is not.  Naturally the data belonging to the other eye should be null when two rows are returned.

So the data above i would expect

One row for patient 1,
two seperate rows for patient 2 as the visit date is different,
and one row for patient 3.

Thanks for the help.

Then I recommend you look at the suggestion at ID: 40327856

That will give one row if left and right are on same visit date.

But I think what you are saying in addition is that:

you want "the latest visit" for each eye, even if not on the same day

is that true?

Use my ROW_NUMBER() approach..

DECLARE @Eye TABLE
    (
      ID INT ,
      OriginalId INT ,
      [Öga] INT ,
      VisitDate DATE
    );

INSERT  INTO @Eye
        ( ID, OriginalId, [Öga], VisitDate )
VALUES  ( 1, 1, 1, '2014-01-01' ),
        ( 2, 1, 2, '2014-01-01' ),
        ( 3, 2, 1, '2014-02-02' ),
        ( 4, 2, 2, '2014-03-03' ),
        ( 5, 3, 1, '2014-04-01' ),
        ( 6, 3, 2, '2014-04-01' );

WITH    Ordered
          AS ( SELECT   E.ID ,
                        E.OriginalId ,
                        E.Öga ,
                        E.VisitDate ,
                        ROW_NUMBER() OVER ( PARTITION BY OriginalId, VisitDate ORDER BY NEWID() ) AS RN
               FROM     @Eye E
             )
    SELECT  O.ID ,
            O.OriginalId ,
            O.Öga ,
            O.VisitDate
    FROM    Ordered O
    WHERE   O.RN = 1;

Select allOpen in new window

I agree, that looks correct ste5an

Note how quickly one can progress with data :)

Hello again.  

Looks correct as i get two rows when the vists are on seperate dates, but i also need to join with the Patients table so i can get the Id column there, and the PersonId.

See the Patients table below:

I

NSERT INTO Patient 
	([ID], [PersonId], [RegisteringUnitId], [Approved], [ApprovedDateTime])
VALUES
	(1, 1, 1, 9, '2014-01-01 00:00:00'),
	(2, 2, 2, 9, '2014-02-02 00:00:00'),
	(3, 3, 3, 9, '2014-03-03 00:00:00')

Select allOpen in new window

It's the same as my first post.. but take the caveat seriously.

Ok so ofcourse your queries work but i realised i missed an important bit of information.

I have modified the tables slightly and added an extra column in the Eyes table.  

declare @Patient table 
    	([ID] int, [PersonId] int, [RegisteringUnitId] int, [Approved]int, [ApprovedDateTime] datetime)
    ;
    	
    INSERT INTO @Patient 
    	([ID], [PersonId], [RegisteringUnitId], [Approved], [ApprovedDateTime])
    VALUES
    	(1, 1, 1, 9, '2014-01-01 00:00:00'),
    	(2, 2, 2, 9, '2014-02-02 00:00:00'),
    	(3, 3, 3, 9, '2014-03-03 00:00:00')
    ;
    


DECLARE @Eye TABLE
    (
      ID INT ,
      OriginalId INT ,
      [Öga] INT ,
      VisitDate DATE,
	  x varchar(10)

    );

INSERT  INTO @Eye
        ( ID, OriginalId, [Öga], VisitDate, x )
VALUES  ( 1, 1, 1, '2014-01-01', 'A' ),
        ( 2, 1, 2, '2014-01-01', 'B'),
        ( 3, 2, 1, '2014-02-02', 'C' ),
        ( 4, 2, 2, '2014-03-03', 'D' ),
        ( 5, 3, 1, '2014-04-01', 'E' ),
        ( 6, 3, 2, '2014-05-01', 'F' );

Select allOpen in new window

The result i REALLY want is:

Person id.     Date               Right eye X      Left eye X
1                    2014-01-01    A                       B
2                    2014-02-02    C                    
2                    2014-03-03                              D
3                    2014-04-01   E
3                    2014-05-01                               F

Also really i know the personid i am interested in so i know there will be a maximum of two rows.

yes you seem to have found the solution... i'll just do some testing...

is that not the same group by I proposed? ID: 40327856

select
      E.*
    , F.*
from Patient F
inner join (
            select
                  OriginalId
                , VisitDate
                , max(case when Öga = 2 then x else '' end) Left_Eye_X
                , max(case when Öga = 1 then x else '' end) Right_Eye_X
            from eye
            group by
                OriginalId
              , VisitDate
           ) E on F.id = E.OriginalId

Select allOpen in new window

| ORIGINALID |  VISITDATE | LEFT_EYE_X | RIGHT_EYE_X | ID | PERSONID | REGISTERINGUNITID | APPROVED |                APPROVEDDATETIME |
|------------|------------|------------|-------------|----|----------|-------------------|----------|---------------------------------|
|          1 | 2014-01-01 |          B |           A |  1 |        1 |                 1 |        9 |  January, 01 2014 00:00:00+0000 |
|          2 | 2014-02-02 |            |           C |  2 |        2 |                 2 |        9 | February, 02 2014 00:00:00+0000 |
|          2 | 2014-03-03 |          D |             |  2 |        2 |                 2 |        9 | February, 02 2014 00:00:00+0000 |
|          3 | 2014-04-01 |            |           E |  3 |        3 |                 3 |        9 |    March, 03 2014 00:00:00+0000 |
|          3 | 2014-05-01 |          F |             |  3 |        3 |                 3 |        9 |    March, 03 2014 00:00:00+0000 |

http://sqlfiddle.com/#!3/2a7a3/3

Select allOpen in new window

Yup, it is.