Find the lowest score after the first Highest Score

user_6420985 0
Viewed 144

I am looking for a SQL query.

Table 1: Source Table

enter image description here

Table 2: Result Table

enter image description here

Attached the patient table. Here I want to find the Highest Score, Highest Score Date, Lowest Score and Lowest Score Date of each patient. The tricky part is that if a patient has same highest score (here it is 9) on two different dates (10/5/2018 and 8/4/2020), we need to take earliest high score (10/5/2018). Similarly, if the patient has same lowest score (6 here) on two different dates (3/1/2019 and 4/2/2020) we should take the latest low score (4/2/2020)

Table 1: Source table contains all scores of a single patient. Patient ID is the primary key of that table. I want a result table that look like table 2.

I have tried this

SELECT distinct
    pat.PAT_NAME 'Patient Name'
    , pat.PAT_ID
    , CAST(pat.CONTACT_DATE AS DATE) 'Service Date'
    , pat.MEAS_VALUE 'Score'
    , [Row Number] = rank() OVER (PARTITION BY pat.PAT_ID ORDER BY CAST(pat.MEAS_VALUE AS int) DESC, CONTACT_DATE asc)
FROM Patient pat            
WHERE pat.PAT_ID = 'A112233'

But this code can show me highest or the lowest score. But it does not meet all my requirement.

sql-server sql
4 Answers

If there is table t with columns: PatientName, PatientID, ServiceDate, Score. Something like this:

;with high_low_cte(PatientID, high_rn, low_rn) as(
    select
      PatientID,
      row_number() over (partition by PatientID order by Score, ServiceDate asc),
      row_number() over (partition by PatientID order by Score, ServiceDate desc)
    from
      t)
select * from high_low_cte where high_rn=1 and low_rn=1;

After update to question:

;with high_low_cte([Patient Name], PAT_ID, [Service Date], Score, high_rn, low_rn) as (
    SELECT distinct
      pat.PAT_NAME 'Patient Name'
      ,pat.PAT_ID
      ,CAST(pat.CONTACT_DATE AS DATE) 'Service Date'
      ,pat.MEAS_VALUE 'Score'
      ,high_rn=row_number() OVER (PARTITION BY pat.PAT_ID ORDER BY CAST(pat.MEAS_VALUE AS int) DESC, CONTACT_DATE asc) 
      ,low_rn=row_number() OVER (PARTITION BY pat.PAT_ID ORDER BY CAST(pat.MEAS_VALUE AS int) asc, CONTACT_DATE asc) 
    FROM  
      Patient pat                                 
    WHERE                  
    pat.PAT_ID='A112233')
select hld1.*, hld2.Score [Low_Score], hld2.[Service Date] [Low Service Date]
from high_low_cte hld1 join high_low_cte hld2 on hld1.PAT_ID=hld2.PAT_ID
where
  hld1.high_rn=1
  and hld2.low_rn=1;
user_67487580

You can use conditional aggregation with row_number():

select PatientName, PatientID, 
       min(case when ;
_hi = 1 then ServiceDate end) as high_date,
       max(score) as high_score,
       min(case when seqnum_lo = 1 then ServiceDate end) as low_date,
       min(score) as low_score
from (select t.*
             row_number() over (partition by PatientID order by Score desc, ServiceDate asc) as seqnum_hi,
             row_number() over (partition by PatientID order by Score, ServiceDate desc) as seqnum_lo
      from t
     ) t
group by PatientName, PatientID;
user_11440350

First, get highest and lowest score, then find on which date it's occurred

;with scores as (  -- get highest/lowest score per patientId
   select patientID, max(score) hScore, min(score) lScore 
   from table
   group by patientID
), dates as (
   -- get first date a patient had the highest score
   select patientID, min(ServiceDate) dateHScore, score
   from table t
   inner join scores s on s.patientid = t.patientid and s.hScore= t.score
   group by patientID, score
   union all
   -- get the last date a patient had the lowest score
   select patientID, max(ServiceDate) dateLScore, score
   from table t
   inner join scores s on s.patientid = t.patientid and s.lScore = t.score
   group by patientID, score
)
select t.patientName, scores.*
from table t
inner join scores s on s.patientid= t.patientid

If you want highest/lowest score on separate columns on the same row, just split the second cte and join them appropriately in the select

user_10127950

I haven't tested, but maybe something like this could do the job:

select x.PatientName, x.PatientId,
  case when x.HSD=1 then 'Highest' else 'Lowest' as ScoreType,
  Score, ScoreDate
from (
  select
    p.PAT_NAME as PatientName,
    p.PAT_ID as PatientId,
    p.MEAS_VALUE as Score,
    p.CONTACT_DATE as ScoreDate,
    row_number() over (partition by p.PatientId order by p.MEAS_VALUE desc, p.CONTACT_DATE desc) as HSD,
    row_number() over (partition by p.PatientId order by p.MEAS_VALUE asc, p.CONTACT_DATE asc) as LSD
  from Patient p
  ) x
where 
  x.HighestScore=1 or
  x.LowestScoreDate=1

You should receive two rows. You would need to pivot the data in order to get your expected output.

user_68264790
Related