Results 1 to 8 of 8
  1. #1
    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.

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,506
    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)

  3. #3
    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

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,506
    Nicely done, Blindman.

  6. #6
    Join Date
    Dec 2012
    Posts
    7
    Both examples worked great. Thank you both for your responses. It is really appreciated.

    Evan

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,566
    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

  8. #8
    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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •