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.

 
Go Back  dBforums > Database Server Software > Microsoft SQL Server > Selecting Different Results

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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.
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,469
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)
Reply With Quote
  #3 (permalink)  
Old
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
Reply With Quote
  #4 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
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
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,469
Nicely done, Blindman.
Reply With Quote
  #6 (permalink)  
Old
Registered User
 
Join Date: Dec 2012
Posts: 7
Both examples worked great. Thank you both for your responses. It is really appreciated.

Evan
Reply With Quote
  #7 (permalink)  
Old
World Class Flame Warrior
 
Join Date: Jun 2003
Location: Ohio
Posts: 12,561
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.
__________________
If it's not practically useful, then it's practically useless.

blindman
www.chess.com: "sqlblindman"
www.LobsterShot.blogspot.com
Reply With Quote
  #8 (permalink)  
Old
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
Reply With Quote
Reply

Tags
case, max, min, select

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On