Capture a Value based on Times

[cothmer]

I am trying to capture a number value based on when two times occurred. So I need the value that was recorded for the LAST meas.RECORDED_TIME that occurred before the Administration Time of a Medication. I am using the function 'Max' for my meas.RECORDED_TIME but I am not sure how to add in my Medication Time to pull the number I need ???

--Pain Score FLO ID = 18

LEFT OUTER JOIN

(

select distinct ifr.pat_id,ifmwdt.MEAS_VALUE as Pain_Score

from IP_FLWSHT_REC ifr

inner join (SELECT distinct rec.pat_id,max(meas.RECORDED_TIME) as Pain_Score

from ip_flwsht_rec rec

inner join [CLARITY].[dbo].[IP_FLWSHT_MEAS] meas on rec.fsd_id=meas.FSD_ID

where meas.flo_meas_id='18'

and meas.MEAS_VALUE is not null

group by rec.pat_id) mdt on ifr.pat_id=mdt.pat_id

inner join IP_FLWSHT_MEAS ifmwdt on ifr.FSD_ID=ifmwdt.FSD_ID and mdt.Pain_Score=ifmwdt.RECORDED_TIME

where ifmwdt.FLO_MEAS_ID='18' and ifmwdt.MEAS_VALUE is not null

)fmidwt on PEH.PAT_ID =fmidwt.PAT_ID

[DellSC]

You're only showing a little bit of your command, would you please post the whole SQL statement? If I understand you correctly, you're trying to get the max pain score time that is less than the administration time - is that correct?

Finally, please let me know what brand of database you're connecting to so that I know what syntax I need use to provide an answer.

Thanks!

-Dell

[cothmer]

Yes that is correct. It is a SQL database, is that what you need ?pain-query.txt

[DellSC]

I'm seeing a number of issues in your SQL, so it's going to take me a bit to fix them in order to simplify the query for you. If I understand you correctly, you're looking for the pain score from the record that has the max timestamp that is less than the administration time. Is that correct? Or is it the first pain score after the administratiof of pain med?

Also, are you showing only the most recent administration or are you showing a set of them over time and need the pain score that is related to each administration?

Is the pain scale where FLW_MEAS.FLO_MEAS_ID = 18? Or is it one of the list of ID's in the query? And which table.field is the administration time?

Thanks!

-Dell

[cothmer]

Thanks Christy.

I am looking for the pain score that was done (documented) right before the taken_time (this is located in the Mar_Admin_Info table).

Yes, the pain score is FLO_MEAS_ID of '18'.

It is a set of administrations and I need the pain score for each.

[DellSC]

Thanks Curt! I'll see what I can do with this.

-Dell

[cothmer]

I had support look at my query and he suggested some CTE's to capture what I was looking for. I am not sure how to use these in the main query to get what I need ? The first part of the query are the CTE's.ctes.txt

[DellSC]

You would use the CTE as you would any other table - just join them into your query instead of going out to the tables every time. The CTEs defined in the "with" clause will load that data into memory once and then the data in memory will be used in the larger query instead of going to the tables every time. Also, you would use a CTE in place of the sub-select for the same data in your original query.

-Dell

[cothmer]

I am just not sure how to add the CTE''s into the main query to get what I need. I can run just the CTE's and they seem to work but adding them into the main query is where I am struggling.

[DellSC]

You reference them as you would any other table you add to your query. You'll define the CTEs above the Select for your main query. In this case they're like this:

With adminInfo as (
...
),
pain1 as (
...
),
pain2 as (
...
)

So you would then add them to the From clause of your query, joining them as you would any other table:

Select
  <fields>
From PAT_ENC_HSP PEH
  Left outer join adminInfo ai
    on ...
  Left outer join pain1 p1
    on ...
<etc.>

-Dell

[cothmer]

Thanks Christy for your help with all my questions about Crystal and SQL. I will give it a try.