ansaurus

Question

T-Sql, How to get these results ?

Answer 1

A:

Take the PA.datee out of the group by clause

Sparky 2009-11-20 11:50:58

It did not work.

uzay95 2009-11-20 11:52:45

Answer 2

+3 A:

Try something like this

SELECT  PA.refPatient_id,
     PA.datee,
     PR.temporary,
     PA.statue
FROM    PatientClinicActs AS PA INNER JOIN
     (
      SELECT PA.refPatient_id,
        MAX(PA.datee) AS datee
      FROM PatientClinicActs AS PA
      WHERE PA.refClinic_id = 25          
      GROUP BY PA.refPatient_id,PA.statue,PA.datee,
     ) AS MaxDates ON PA.refPatient_id = MaxDates.refPatient_id AND PA.datee = MaxDates.datee INNER JOIN
     PatientStatueReasons AS PR ON PA.refreason_id = PR.reason_id

You need to get the max date per patient first, then join to that.

astander 2009-11-20 11:51:32

It still has more than one row for some patients.

uzay95 2009-11-20 11:56:45

Tha might be due to either the MAX date occuring more than once in PatientClinicActs or the link to PatientStatueReasons containing more than one entry for a patient, in which case you must decide how to choose the appropriate record. What will make it the one you need?

astander 2009-11-20 12:01:16

I voted you down because I was misreading sth... Cannot undo it apparently, so I voted you up on 'Retrieving rows with the highest value'for fairness

Peter 2009-11-20 12:07:04

Ok. if i retrive refPatient_id, refReason_id and datee, i could have more than 1 actions for some patients. Because they might be passive (statue=0) and then active(statue=1) with some reasons.

uzay95 2009-11-20 12:16:21

Then change it to select only the statue you require 0 or 1

astander 2009-11-20 12:21:11

Good, but i need to know what this patient did (leave/come) with his/her last movement...

uzay95 2009-11-20 12:25:07

Provide the table structure you have and some data, and that will make things easier.

astander 2009-11-20 12:27:08

@Peter: You can't reverse a downvote until after the answer has been editted.

OMG Ponies 2009-11-23 04:58:15

Answer 3

+1 A:

Instead of a cross join

FROM PatientClinicActs AS PA
                ,PatientStatueReasons AS PR

can you try with inner join

FROM PatientClinicActs AS PA

INNER JOIN PatientStatueReasons AS PR 

ON PA.refreason_id = PR.reason_id

WHERE PA.refClinic_id = 25

priyanka.sarkar 2009-11-21 02:45:50

+1 for good practice even if it doesn't solve it...

gbn 2009-11-21 09:20:50

Thank you very much sir. I have learnt a lot from you and is still learning. Your comments are always valuable to me. Thanks again

priyanka.sarkar 2009-11-21 10:41:32

It's not a cross join - it's an inner join using non ANSI JOIN syntax.

OMG Ponies 2009-11-23 05:00:45

Answer 4

A:

For this kind of cases, there is another way to get the latest record by using SQL Server Ranking functions.

I have used DENSE_RANK() for my answer, but you can use RANK(), instead for your particular problem.
(note: code below is not tested. If you were to provide schema for tables, I would have tested with sample data)

;with RankedResult as (
 SELECT 
 PA.refPatient_id
 , PA.datee
 , PR.temporary,
 , PA.statue
 --; Last datee has the lowest rank value of 1,
 , dense_rank() over 
  (partition by PA.refPatient_id order by PA.datee desc) as [Rank]
 FROM PatientClinicActs AS PA
   join PatientStatueReasons AS PR on PA.refreason_id = PR.reason_id
 WHERE PA.refClinic_id = 25 
)
select distinct *
from RankedResult
--; Get only the last result.
where [Rank] = 1

Sung Meister 2009-11-23 04:38:07

ansaurus

tags:

views:

answers:

T-Sql, How to get these results ?

related questions