November 19, 2008 at 9:49 am
See script below.
Here is what I'm getting....
vst_int_id chi_evt_dtm order_code_ext_id order_code_desc1
936738 2008-07-10 05:56:00.000 CBC COMPLETE BLOOD COUNT
936738 2008-07-09 05:36:00.000 CBCMD CBC WITH MANUAL DIFFERENTIAL
936738 2008-07-08 05:50:00.000 CBC COMPLETE BLOOD COUNT
936738 2008-07-05 13:24:00.000 TS TYPE AND SCREEN
936738 2008-06-07 11:36:00.000 CBC COMPLETE BLOOD COUNT
936738 2008-06-07 11:36:00.000 TS TYPE AND SCREEN
Here is what I want....
vst_int_id chi_evt_dtm order_code_ext_id order_code_desc1
936738 2008-07-10 05:56:00.000 CBC COMPLETE BLOOD COUNT
I want this result because it's the lab order closest to the date of discharge. The newest order is always going to be the one I want back.
How would I script my query so that it only gets the newest result? I'm guessing that a subquery might be involved but I don't know the syntax or know if there is any function available that does this. I'm willing to read up if I can get some direction.
Here is the script....
select vom100.vst_int_id
,vom107.chi_evt_dtm
,vom107.order_code_ext_id
,vom200.order_code_desc1
from VOM107_CHILD_ORDER vom107 (nolock)
,VOM100_ORDER_HEADER vom100 (nolock)
,VOM200_ORDER_CODE vom200 (nolock)
where vom107.ord_int_id = vom100.ord_int_id
and vom107.order_code_int_id = vom200.order_code_int_id
--sample internal id
and vom100.vst_int_id = 936738
--3=Lab, 4=microbiology, 18=lab dept use, 19=histology/path
--40=lab pathology dept, 20=lab special
and vom200.order_type_int_id in (3, 4, 19)
--not cancelled or suspended
and vom107.chi_ord_sta_cd not in ('X', 'S')
order by
vom107.vom107.chi_evt_dtm desc
TIA,
John
November 19, 2008 at 10:01 am
Like this, you mean?
[font="Courier New"]SELECT TOP 1 vom100.vst_int_id
,vom107.chi_evt_dtm
,vom107.order_code_ext_id
,vom200.order_code_desc1
FROM VOM107_CHILD_ORDER vom107 (nolock)
,VOM100_ORDER_HEADER vom100 (nolock)
,VOM200_ORDER_CODE vom200 (nolock)
WHERE vom107.ord_int_id = vom100.ord_int_id
AND vom107.order_code_int_id = vom200.order_code_int_id
--sample internal id
AND vom100.vst_int_id = 936738
--3=Lab, 4=microbiology, 18=lab dept use, 19=histology/path
--40=lab pathology dept, 20=lab special
AND vom200.order_type_int_id IN (3, 4, 19)
--not cancelled or suspended
AND vom107.chi_ord_sta_cd NOT IN ('X', 'S')
ORDER BY
vom107.vom107.chi_evt_dtm DESC
[/font]
Cheers
ChrisM
For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden
November 19, 2008 at 3:30 pm
Thank you. That TOP 1 part worked when I use it on one account at a time. I figured I could take that same idea and apply it to a bigger script that pulls 945 unique accounts....but instead I only get one result. I need the last lab order for all 945 accounts.
Wouldn't this (script below) require a subquery on the...
,order_time = vom107.chi_evt_dtm (line 3)
....line instead?
@labdi is a table variable that contains the visit ids that I want this query to use to find the last lab information on each account.
From my original post, it's obvious I'm a newbie so feel free to tear up my code below or let me know what terminology or functions I need to read up on.
(I can post my whole script but I'm thinking it's not necessary....let me know if it's something that's needed to answer my question)
select top 1
lab.vst_ext_id
,order_time = vom107.chi_evt_dtm
,collection_time = vom107.col_dt
,result_release = vom107.rsu_rls_ts
,vom107.order_code_ext_id
,vom200.order_code_desc1
from VOM107_CHILD_ORDER vom107 (nolock)
,VOM100_ORDER_HEADER vom100 (nolock)
,VOM200_ORDER_CODE vom200 (nolock)
,@labdi lab
wherelab.vst_int_id = vom100.vst_int_id
and vom107.ord_int_id = vom100.ord_int_id
and vom107.order_code_int_id = vom200.order_code_int_id
--3=Lab, 4=microbiology, 18=lab dept use, 19=histology/path
--40=lab pathology dept, 20=lab special
and vom200.order_type_int_id in (3, 4, 19)
and vom107.chi_ord_sta_cd not in ('X', 'S')
and vom107.col_dt is not null
order by
vom107.chi_evt_dtm desc
November 19, 2008 at 8:37 pm
Derived table must be what you're looking for.
selectvom100.vst_int_id
,vom107.chi_evt_dtm
,vom107.order_code_ext_id
,vom200.order_code_desc1
from VOM107_CHILD_ORDER vom107
INNER JOIN VOM100_ORDER_HEADER vom100 ON vom107.ord_int_id = vom100.ord_int_id
INNER JOIN VOM200_ORDER_CODE vom200 ON vom107.order_code_int_id = vom200.order_code_int_id
INNER JOIN (
select C.chi_evt_dtm, MAX(H.chi_evt_dtm) as Latest
from VOM107_CHILD_ORDER C
inner join VOM100_ORDER_HEADER H ON C.ord_int_id = H.ord_int_id
group by C.chi_evt_dtm
) DT ON vom100.vst_int_id = DT.chi_evt_dtm AND vom107.chi_evt_dtm = DT.Latest
where --3=Lab, 4=microbiology, 18=lab dept use, 19=histology/path
--40=lab pathology dept, 20=lab special
vom200.order_type_int_id in (3, 4, 19)
--not cancelled or suspended
and vom107.chi_ord_sta_cd not in ('X', 'S')
Don't know what is the relation between records in tables VOM107_CHILD_ORDER and VOM100_ORDER_HEADER, so I've chosen the most safe but probably not most effective approach.
Probably grouping by ord_int_id in derived table will be enough, then it may be changed to have single table. But I cannot tell if it's right.
_____________
Code for TallyGenerator
November 20, 2008 at 2:46 pm
Sorry but in the interest of getting all information posted, see my full script below which includes the latest suggestion.
Disclaimer: I'm self taught so don't get too rough with me and my script. =)
I'm getting an error with the latest suggestion. The error is....
Server: Msg 170, Level 15, State 1, Line 328
Line 328: Incorrect syntax near '='.
Server: Msg 170, Level 15, State 1, Line 346
Line 346: Incorrect syntax near 'DT'.
I believe it might have to do with the following line....
) DT ON vom100.vst_int_id = DT.chi_evt_dtm
...the data type on vst_int_id (visit internal id) is int. The data type for chi_evt_dtm (child event datetime) is datetime
I'm trying to take the last advice given but I'm stuck....can't figure out how to take 945 records and do a search to get back the latest lab information on each account.
declare @labdi table
(
vst_int_idint
,vst_ext_idchar(20)
,med_rec_nochar(20)
,zip_cdchar(10)
,pat_typechar(40)
,genderchar(5)
,ageint
,daysint
,drg_cdchar(4)
,drg_dschar(80)
,financial_classchar(40)
,plan_dschar(40)
,dschrg_datechar(10)
,dschrg_daychar(3)
,dschrg_monthchar(3)
,dschrg_timechar(5)
,dschrg_hourint
,dschrg_statchar(40)
,discharg_unitchar(40)
,admit_datechar(10)
,admit_daychar(3)
,admit_monthchar(3)
,admit_timechar(5)
,admit_hourint
,adm_src_cdint
,adm_src_dschar(40)
,admit_doc_numberchar(20)
,admit_docchar(40)
,attend_doc_numberchar(20)
,attend_docchar(40)
,admitting_dxchar(10)
,admitting_dx_descchar(40)
,principal_dx_codechar(10)
,principal_dx_descchar(40)
,IP_OPchar(3)
)
insert into @labdi
(
vst_int_id
,vst_ext_id
,med_rec_no
,zip_cd
,pat_type
,gender
,age
,days
,drg_cd
,drg_ds
,financial_class
,plan_ds
,dschrg_date
,dschrg_day
,dschrg_month
,dschrg_time
,dschrg_hour
,dschrg_stat
,discharg_unit
,admit_date
,admit_day
,admit_month
,admit_time
,admit_hour
,adm_src_cd
,adm_src_ds
,admit_doc_number
,admit_doc
,attend_doc_number
,attend_doc
,admitting_dx
,admitting_dx_desc
,principal_dx_code
,principal_dx_desc
,IP_OP
)
selectvpm300.vst_int_id
,vpm300.vst_ext_id
,vpm300.med_rec_no
,zip_cd = isnull(vpm300.zip_cd, '')
,pat_type = rtrim(b.cod_dtl_ds)
,gender = (case vsm040.sex_cd
when '2303' then 'M'
when '2304' then 'F'
when '2318' then 'UNK'
end)
,age = cast(
case
when (datediff(dd,vsm040.bth_ts, vpm300.adm_ts) >= 365) then
convert(integer,(datediff(dd,vsm040.bth_ts, vpm300.adm_ts) / 365.25))
else
datediff(dd,vsm040.bth_ts, vpm300.adm_ts) / 365.25
end as int(4))
,days = (case datediff(dd,vpm300.adm_ts, Isnull(vpm300.dschrg_ts,getdate()))
when '0' then '1'
else
datediff(dd,vpm300.adm_ts, Isnull(vpm300.dschrg_ts,getdate()))
end)
,drg_cd = isnull(vmr410.drg_cd, '')
,vsm913.drg_ds
,financial_class = d.cod_dtl_ds
,plan_ds = rtrim(vpm700.plan_ds)
--converts datetime type to mm/dd/yyyy format
,dschrg_date = convert(char(10), vpm300.dschrg_ts, 101)
--converts 1-7 to weekday abbreviation
,dschrg_day = (case datepart(dw, vpm300.dschrg_ts)
when '1' then 'sun'
when '2' then 'mon'
when '3' then 'tue'
when '4' then 'wed'
when '5' then 'thu'
when '6' then 'fri'
when '7' then 'sat'
end)
--converting 1-12 to month abbreviation
,dschrg_month = (case datepart(mm, vpm300.dschrg_ts)
when '1' then 'jan'
when '2' then 'feb'
when '3' then 'mar'
when '4' then 'apr'
when '5' then 'may'
when '6' then 'jun'
when '7' then 'jul'
when '8' then 'aug'
when '9' then 'sep'
when '10' then 'oct'
when '11' then 'nov'
when '12' then 'dec'
end)
--converts datetime type to just time (24 hour time)
,dschrg_time = convert(char(5), vpm300.dschrg_ts, 14)
--extracts the hour part of the datetime data type
,dschrg_hour = datepart(hh, vpm300.dschrg_ts)
,dschrg_stat = rtrim(c.cod_dtl_ds)
,discharg_unit = vsm950.loc_ds
--see above for explanations
,admit_date = convert(char(10), vpm300.adm_ts, 101)
,admit_day = (case datepart(dw, vpm300.adm_ts)
when '1' then 'sun'
when '2' then 'mon'
when '3' then 'tue'
when '4' then 'wed'
when '5' then 'thu'
when '6' then 'fri'
when '7' then 'fri'
end)
,admit_month = (case datepart(mm, adm_ts)
when '1' then 'jan'
when '2' then 'feb'
when '3' then 'mar'
when '4' then 'apr'
when '5' then 'may'
when '6' then 'jun'
when '7' then 'jul'
when '8' then 'aug'
when '9' then 'sep'
when '10' then 'oct'
when '11' then 'nov'
when '12' then 'dec'
end)
,admit_time = convert(char(5), vpm300.adm_ts, 14)
,admit_hour = datepart(hh, vpm300.adm_ts)
,vpm300.adm_src_cd
,adm_src_ds = rtrim(a.cod_dtl_ds)
--'ADMIT' = 2322 (vpm114.func_int_id)
,[admit_doc_number] =
(
SELECT ext_id = rtrim(car_gvr_ext_id)
FROM VPM315_VISIT_CARE_GIVER vpm315
LEFT JOIN VPM114_CAR_GVR_FUNC vpm114 ON vpm114.func_aso_int_id = vpm315.func_aso_int_id
LEFT JOIN VPM100_CARE_GIVER vpm100 ON vpm100.car_gvr_int_id = vpm114.car_gvr_int_id
LEFT JOIN VSM040_PERSON_HDR vsm040 ON vsm040.psn_int_id = vpm100.psn_int_id
WHERE vpm300.vst_int_id = vpm315.vst_int_id
AND func_int_id = '2322'
)
,[admit_doc] =
(
SELECT adm_nm = rtrim(lst_nm) + ', ' + rtrim(fst_nm)
FROM VPM315_VISIT_CARE_GIVER vpm315
LEFT JOIN VPM114_CAR_GVR_FUNC vpm114 ON vpm114.func_aso_int_id = vpm315.func_aso_int_id
LEFT JOIN VPM100_CARE_GIVER vpm100 ON vpm100.car_gvr_int_id = vpm114.car_gvr_int_id
LEFT JOIN VSM040_PERSON_HDR vsm040 ON vsm040.psn_int_id = vpm100.psn_int_id
WHERE vpm300.vst_int_id = vpm315.vst_int_id
AND func_int_id = '2322'
)
--ATTND(ATTENDING) = 2323 (vpm114.func_int_id)
,[attend_doc_number] =
(
SELECT ext_id = rtrim(car_gvr_ext_id)
FROM VPM315_VISIT_CARE_GIVER vpm315
LEFT JOIN VPM114_CAR_GVR_FUNC vpm114 ON vpm114.func_aso_int_id = vpm315.func_aso_int_id
LEFT JOIN VPM100_CARE_GIVER vpm100 ON vpm100.car_gvr_int_id = vpm114.car_gvr_int_id
LEFT JOIN VSM040_PERSON_HDR vsm040 ON vsm040.psn_int_id = vpm100.psn_int_id
WHERE vpm300.vst_int_id = vpm315.vst_int_id
AND func_int_id = '2323'
)
,[attend_doc] =
(
SELECT adm_nm = rtrim(lst_nm) + ', ' + rtrim(fst_nm)
FROM VPM315_VISIT_CARE_GIVER vpm315
LEFT JOIN VPM114_CAR_GVR_FUNC vpm114 ON vpm114.func_aso_int_id = vpm315.func_aso_int_id
LEFT JOIN VPM100_CARE_GIVER vpm100 ON vpm100.car_gvr_int_id = vpm114.car_gvr_int_id
LEFT JOIN VSM040_PERSON_HDR vsm040 ON vsm040.psn_int_id = vpm100.psn_int_id
WHERE vpm300.vst_int_id = vpm315.vst_int_id
AND func_int_id = '2323'
)
,[admitting_dx] =
(
SELECT icd9_code
FROM VPM318_VISIT_DIAGNOSIS vpm318 (nolock)
LEFT JOIN VSM910_ICD9_REF vsm910 on vpm318.icd9_int_id = vsm910.icd9_int_id
WHERE vpm300.vst_int_id = vpm318.vst_int_id
and ICD9_diag_ty = 'A'
)
,[admitting_dx_desc] =
(
SELECT icd9_abrv_ds
FROM VPM318_VISIT_DIAGNOSIS vpm318 (nolock)
LEFT JOIN VSM910_ICD9_REF vsm910 on vpm318.icd9_int_id = vsm910.icd9_int_id
WHERE vpm300.vst_int_id = vpm318.vst_int_id
and ICD9_diag_ty = 'A'
)
,principal_dx_code = vsm910.icd9_code
,principal_dx_desc = vsm910.icd9_abrv_ds
,IP_OP = (case vpm300.pat_ty
when '15820' then 'INP'
else 'OUT'
end)
fromVPM300_PAT_VISIT vpm300 (nolock)
,VPM311_VISIT_PAYOR vpm311 (nolock)
,VPM318_VISIT_DIAGNOSIS vpm318 (nolock)
,VPM700_PAYOR_PLAN vpm700 (nolock)
,VSM910_ICD9_REF vsm910 (nolock)
,TSM913_DRG_REF vsm913 (nolock)
,VSM040_PERSON_HDR vsm040 (nolock)
,VSM950_LOCATION_REF vsm950 (nolock)
,VMR410_VISIT_DRG vmr410 (nolock)
--table used for admission source
,VSM180_MST_COD_DTL a (nolock)
,VSM180_MST_COD_DTL b (nolock)
,VSM180_MST_COD_DTL c (nolock)
,VSM180_MST_COD_DTL d (nolock)
where--join used to obtain drg info
vmr410.drg_int_id = vsm913.drg_int_id
--join used to get drg
and vpm300.vst_int_id = vmr410.vst_int_id
--join used to get payor plan
and vpm300.psn_int_id = vsm040.psn_int_id
--joins to get Principal DX
and vpm300.vst_int_id = vpm318.vst_int_id
and vpm318.icd9_int_id = vsm910.icd9_int_id
and vpm318.ICD9_rank_no = '1'
--Join to get discharging unit
and vpm300.loc_lvl_3_id = vsm950.loc_int_id
--joins to obtain payor information
and vpm300.vst_int_id = vpm311.vst_int_id
and vpm311.plan_int_id = vpm700.plan_int_id
--join used to get admission source description
and vpm300.adm_src_cd = a.cod_dtl_int_id
--join used to obtain patient type
and vpm300.pat_ty = b.cod_dtl_int_id
--join used to obtain discharge status description
and vpm300.dschg_sta_cd = c.cod_dtl_int_id
--join used to obtain financial class code
and vpm300.fin_cls_cd = d.cod_dtl_int_id
--need this to get primary payor name only
and vpm311.pyr_seq_no = '4981'
--no cancelled visits
--and vpm300.vst_sta_cd not in ('4745')
--Internal id for IP patient type
and vpm300.pat_ty = '15820'
--discharge date range
and vpm300.dschrg_ts >= '07-01-2008 00:00:00'
and vpm300.dschrg_ts < '10-01-2008 00:00:00'
order by
vpm300.vst_ext_id asc
--days asc
--select * from @labdi
select distinct
lab.vst_ext_id
,vom107.chi_evt_dtm =
,collection_time = vom107.col_dt
,result_release = vom107.rsu_rls_ts
,vom107.order_code_ext_id
,vom200.order_code_desc1
fromVOM107_CHILD_ORDER vom107 (nolock)
INNER JOIN VOM100_ORDER_HEADER vom100 ON vom107.ord_int_id = vom100.ord_int_id
INNER JOIN VOM200_ORDER_CODE vom200 ON vom107.order_code_int_id = vom200.order_code_int_id
inner join @labdi lab on vom100.vst_int_id = lab.vst_int_id
INNER JOIN
(
select C.chi_evt_dtm
,MAX(H.chi_evt_dtm) as Latest
fromVOM107_CHILD_ORDER C
inner join VOM100_ORDER_HEADER H ON C.ord_int_id = H.ord_int_id
group by C.chi_evt_dtm
) DT ON vom100.vst_int_id = DT.chi_evt_dtm
AND vom107.chi_evt_dtm = DT.Latest
--,VOM100_ORDER_HEADER vom100 (nolock)
--,VOM200_ORDER_CODE vom200 (nolock)
--,@labdi lab
where--lab.vst_int_id = vom100.vst_int_id
--and vom107.ord_int_id = vom100.ord_int_id
--and vom107.order_code_int_id = vom200.order_code_int_id
--3=Lab, 4=microbiology, 18=lab dept use, 19=histology/path
--40=lab pathology dept, 20=lab special
and vom200.order_type_int_id in (3, 4, 19)
and vom107.chi_ord_sta_cd not in ('X', 'S')
and vom107.col_dt is not null
order by
vom107.chi_evt_dtm desc
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply