Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 124
  • Last Modified:

query question

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
  ( 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

Open in new window


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?
0
soozh
Asked:
soozh
  • 9
  • 6
  • 5
  • +3
2 Solutions
 
Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
Change

select * from cte

to

select distinct * from cte
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
 
ste5anSenior DeveloperCommented:
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;

Open 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;

Open in new window


Caveat: Your describtion results in a non-deterministic result.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
 
Akilandeshwari NCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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.
0
 
Akilandeshwari NCommented:
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.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
Sure. Visit date is the most important here.
0
 
soozhAuthor Commented:
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?
0
 
Akilandeshwari NCommented:
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?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
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

Open in new window

0
 
PortletPaulCommented:
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.
0
 
soozhAuthor Commented:
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.
0
 
PortletPaulCommented:
>>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
0
 
PortletPaulCommented:
PLEASE provide sample data and expected result...
0
 
PortletPaulCommented:
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

Open in new window

0
 
PortletPaulCommented:
Maybe something like this?
select
      F.*
    , E.*
from Patient F
inner join (
            select
                  OriginalId
                , VisitDate
                , max(case when Öga = 2 then 'Left' end) EL
                , max(case when Öga = 1 then 'Right' end) ER
            from eye
            group by
                OriginalId
              , VisitDate
           ) E on F.id = E.OriginalId

Open in new window

0
 
soozhAuthor Commented:
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.
0
 
PortletPaulCommented:
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?
0
 
ste5anSenior DeveloperCommented:
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;

Open in new window

0
 
PortletPaulCommented:
I agree, that looks correct ste5an

Note how quickly one can progress with data :)
0
 
soozhAuthor Commented:
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')

Open in new window

0
 
ste5anSenior DeveloperCommented:
It's the same as my first post.. but take the caveat seriously.
0
 
soozhAuthor Commented:
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' );

Open 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.
0
 
ste5anSenior DeveloperCommented:
Easy.

 
SELECT	E.OriginalId,
	E.VisitDate,
	CASE WHEN MIN(E.Öga) = 1 THEN MIN(E.x) ELSE NULL END,
	CASE WHEN MAX(E.Öga) = 2 THEN MAX(E.x) ELSE NULL END
FROM	@Eye E
GROUP BY E.OriginalId,
		E.VisitDate;    

Open in new window

0
 
soozhAuthor Commented:
yes you seem to have found the solution... i'll just do some testing...
0
 
PortletPaulCommented:
is that not the same group by I proposed? ID: 40327856
0
 
PortletPaulCommented:
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

Open 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

Open in new window

0
 
ste5anSenior DeveloperCommented:
Yup, it is.
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 9
  • 6
  • 5
  • +3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now