| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
12-10-12, 09:20
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
|
|
|
Selecting Different Results
|
|
Hello,
I am trying to write a report that includes different lab values for an account number depending on the test. What I mean is if patient xyz had lab work and procedure number 1012 was ordered I need to include one line for the highest result value and one for the lowest result value. If I have procedure number 1032 I only need a line for the lowest value. I have a list of about 40 lab procedures that some require both highest and lowest, some just the lowest and some the highest. I have played around with CASE, but that hasn't worked for me.
Here is an example of what I'm getting:
Acct Number MR # AdDate DDate Code CDate CTime Result Test
E00000000000 MR00000000 082411 090211 1012 083011 0515 4.5 WBC
E00000000000 MR00000000 082411 090211 1012 083111 0515 4.7 WBC
E00000000000 MR00000000 082411 090211 1012 082711 0525 5.1 WBC
E00000000000 MR00000000 082411 090211 1012 082611 0455 5.3 WBC
E00000000000 MR00000000 082411 090211 1012 082811 0525 5.7 WBC
E00000000000 MR00000000 082411 090211 1012 082911 0500 5.7 WBC
E00000000000 MR00000000 082411 090211 1012 090111 0500 6.6 WBC
E00000000000 MR00000000 082411 090211 1012 082511 0609 6.8 WBC
E00000000000 MR00000000 082411 090211 1012 082411 2050 9.3 WBC
E00000000000 MR00000000 082411 090211 1032 083011 0515 10.1 HB
E00000000000 MR00000000 082411 090211 1032 083111 0515 10.2 HB
E00000000000 MR00000000 082411 090211 1032 082611 0957 10.2 HB
E00000000000 MR00000000 082411 090211 1032 082711 0525 10.4 HB
E00000000000 MR00000000 082411 090211 1032 082611 0455 10.5 HB
E00000000000 MR00000000 082411 090211 1032 090111 0500 10.7 HB
Her is what I need:
Acct Number MR # AdDate DDate Code CDate CTime Result Test
E00000000000 MR00000000 082411 090211 1012 083011 0515 4.5 WBC
E00000000000 MR00000000 082411 090211 1012 082411 2050 9.3 WBC
E00000000000 MR00000000 082411 090211 1032 083011 0515 10.1 HB
Thank You,
Evan
|
Last edited by bassee; 12-10-12 at 10:13.
|
12-10-12, 10:54
|
|
Wage drone 24601
|
|
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,232
|
|
This may not be the best way to do this, so I have included my test setup for the others who want to try it out:
Code:
drop table test1
create table test1
(patientID varchar(20),
MRNo varchar(20),
AdDate varchar(10),
ddate varchar(10),
Code varchar(5),
CDate varchar(10),
CTime time,
Result float,
Test varchar(5))
insert into test1 values
('E00000000000', 'MR00000000', '082411', '090211', '1012', '083011', '0515', 4.5, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '083111', '0515', 4.7, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082711', '0525', 5.1, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082611', '0455', 5.3, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082811', '0525', 5.7, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082911', '0500', 5.7, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '090111', '0500', 6.6, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082511', '0609', 6.8, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082411', '2050', 9.3, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '083011', '0515', 10.1, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '083111', '0515', 10.2, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '082611', '0957', 10.2, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '082711', '0525', 10.4, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '082611', '0455', 10.5, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '090111', '0500', 10.7, 'HB')
with cte1
as
(select row_number() over (partition by patientID, test order by result) as RowNum, patientid, test, result, cdate, ctime
from test1
),
cte2
as
(select max(rownum) as maxresult, min(rownum) as minresult, patientid, test
from cte1
group by patientid, test)
select t.*
from test1 t join
cte1 c1 on t.patientid = c1.patientid
and t.test = c1.test
and t.cdate = c1.cdate
and t.ctime = c1.ctime join
cte2 c2 on c1.patientid = c2.patientid
and c1.test = c2.test
where t.test = 'WBC' and (c1.rownum = c2.maxresult or c1.rownum = c2.minresult)
or t.test = 'HB' and (c1.rownum = c2.minresult)
|
|
12-10-12, 16:35
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
|
|
|
|
That worked!
Here is what I ended up with ( I sent my initial Select to #FirstSelect):
WITH CTE1 AS (SELECT ROW_NUMBER() OVER (PARTITION by AccountNumber, ReferenceCode ORDER BY ResultRW) as RowNum, AccountNumber, ReferenceCode, ResultRW, CollectDate,
CollectTime FROM #FirstSort),
CTE2 AS (SELECT MAX(RowNum) AS MaxResult, MIN(RowNum) AS MinResult, AccountNumber, ReferenceCode FROM CTE1 GROUP BY AccountNumber, ReferenceCode)
SELECT CAST(FS.AccountNumber AS CHAR(24)),
CAST(FS.UnitNumber AS CHAR(24)),
REPLACE(CONVERT(CHAR(10), FS.AdmitDateTime, 1), '/', ''),
REPLACE(CONVERT(CHAR(10), FS.DischargeDateTime, 1), '/', ''),
CAST(FS.ReferenceCode AS CHAR(10)),
REPLACE(CONVERT(CHAR(10), FS.CollectDate, 101), '/', ''),
REPLACE(CONVERT(CHAR(5), FS.CollectTime, 108), ':', ''),
CAST(FS.ResultRW AS CHAR(12)),
FS.TestMnemonic
FROM #FirstSelect FS
JOIN CTE1 C1 ON FS.AccountNumber = C1.AccountNumber
AND FS.ReferenceCode = C1.ReferenceCode
AND FS.CollectDate = C1.CollectDate
AND FS.CollectTime = C1.CollectTime
JOIN CTE2 C2 ON C1.AccountNumber = C2.AccountNumber
AND C1.ReferenceCode = C2.ReferenceCode
WHERE
FS.ReferenceCode IN ('1012','1014','1032','1034','5012','5032','5052', '5054','5232','5234','5236','7012','7032','7034',' 7072','7092') AND (C1.RowNum = C2.MaxResult or C1.RowNum = C2.MinResult)
OR FS.ReferenceCode IN ('1072','3012','3032','3052','5072','5074','5092', '5094','5252','5254','5272','5292','5294','5412',' 5434','5452','5454','5472','5492','5612') AND (C1.RowNum = C2.MaxResult)
OR FS.ReferenceCode IN ('1052','1054','5212','5214','7052','7212') AND (C1.RowNum = C2.MinResult)
Thank You
Evan
|
|
12-11-12, 09:59
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 12,327
|
|
Not necessary to use two CTEs.
Code:
create table test1
(patientID varchar(20),
MRNo varchar(20),
AdDate varchar(10),
ddate varchar(10),
Code varchar(5),
CDate varchar(10),
CTime time,
Result float,
Test varchar(5))
insert into test1 values
('E00000000000', 'MR00000000', '082411', '090211', '1012', '083011', '0515', 4.5, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '083111', '0515', 4.7, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082711', '0525', 5.1, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082611', '0455', 5.3, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082811', '0525', 5.7, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082911', '0500', 5.7, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '090111', '0500', 6.6, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082511', '0609', 6.8, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1012', '082411', '2050', 9.3, 'WBC'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '083011', '0515', 10.1, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '083111', '0515', 10.2, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '082611', '0957', 10.2, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '082711', '0525', 10.4, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '082611', '0455', 10.5, 'HB'),
('E00000000000', 'MR00000000', '082411', '090211', '1032', '090111', '0500', 10.7, 'HB')
;--This returns only 1 record, if it is both the highest and lowest test value.
with CTE as
(select patientID,
MRNo,
AdDate,
ddate,
Code,
CDate,
CTime,
Result,
Test,
row_number() over (partition by patientID, test order by result asc) as MinRowNum,
row_number() over (partition by patientID, test order by result desc) as MaxRowNum
from test1)
select *
from CTE
where MinRowNum = 1
or MaxRowNum = 1
;--This returns a record twice if it is both the highest and lowest test value.
with CTE as
(select patientID,
MRNo,
AdDate,
ddate,
Code,
CDate,
CTime,
Result,
Test,
row_number() over (partition by patientID, test order by result asc) as MinRowNum,
row_number() over (partition by patientID, test order by result desc) as MaxRowNum
from test1)
select *
from CTE
where MinRowNum = 1
union all
select *
from CTE
where MaxRowNum = 1
drop table test1
|
|
12-11-12, 11:11
|
|
Wage drone 24601
|
|
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,232
|
|
|
|
12-11-12, 11:53
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
|
|
Both examples worked great. Thank you both for your responses. It is really appreciated.
Evan
|
|
12-11-12, 14:05
|
|
World Class Flame Warrior
|
|
Join Date: Jun 2003
Location: Ohio
Posts: 12,327
|
|
Just make sure you understand the subtle difference between those two queries. Especially if its possible that a patient might have only one instance of a particular test, or if two tests are performed that both yield the same result value.
|
|
12-11-12, 14:29
|
|
Registered User
|
|
Join Date: Dec 2012
Posts: 7
|
|
I did notice the difference, thanks. I had to use the example that returns only one line. The report looks at the other fields as well. Multiple lines with the same collectdate and time would fail. Thanks again
Evan
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Selecting Different Results
