Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2007
    Posts
    1

    Unanswered: Using a loop to filter data?

    Current Code

    SELECT [Patient Identifier], Date, [Operator Index], Time
    FROM (SELECT ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AS [Patient Identifier], ISNULL(t9.Date, t8.Date) AS Date, ISNULL(t9.Rows, t8.Rows)
    AS Rows, c.[Operator Index], c.Time, ROW_NUMBER() OVER (PARTITION BY ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]),
    ISNULL(t9.Date, t8.Date)
    ORDER BY c.Time) AS RowNum
    FROM (SELECT [Patient Identifier], Date, 2 AS [Rows]
    FROM [First Step]
    WHERE [Operator Index] >= 90
    GROUP BY [Patient Identifier], Date
    HAVING COUNT(*) >= 2) AS t9 FULL JOIN
    (SELECT [Patient Identifier], Date, 4 AS [Rows]
    FROM [First Step]
    WHERE [Operator Index] >= 80
    GROUP BY [Patient Identifier], Date
    HAVING COUNT(*) >= 4) AS t8 ON t8.[Patient Identifier] = t9.[Patient Identifier] AND t8.Date = t9.Date INNER JOIN
    Complete AS c ON c.[Patient Identifier] = ISNULL(t9.[Patient Identifier], t8.[Patient Identifier]) AND c.Date = ISNULL(t9.Date, t8.Date)) AS d
    WHERE d .RowNum <= d .[Rows]

    Current Input
    Patient ID DATE Time Operator Index
    51700003 18OCT2006 11:48 91
    51700003 18OCT2006 11:50 100
    51700004 17OCT2006 11:41 89
    51700004 17OCT2006 11:50 93
    51700004 17OCT2006 11:52 91
    51700004 17OCT2006 12:00 93

    Current Output

    Patient ID DATE Time Operator Index
    0517_00003 18OCT2006 11:48 91
    0517_00003 18OCT2006 11:50 100
    0517_00004 17OCT2006 11:41 89
    0517_00004 17OCT2006 11:50 93

    It should be
    Patient ID DATE Time Operator Index
    51700003 18OCT2006 11:48 91
    51700003 18OCT2006 11:50 100
    51700004 17OCT2006 11:50 93
    51700004 17OCT2006 11:52 91

    The data is organized by patient id, date, time (ascending)
    For a given patient id, on a certain data, testing was performed. A value between 80 and 100 is acceptable data. I need either the first 2 tests with a score above 90 or the first 4 tests above 80. (The tests are further sorted by time because the testing is time dependant. On some occassions, there is just too much data. What is wrong with my current query?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    I do not have 2005 so can't test ROW_NUMBER() function
    The below code might help you solve your problem
    Code:
    create table #t1 (PatientID int, dt DATETime, OperatorIndex int)
    insert into #t1 select
    51700003 ,'18OCT2006 11:48' ,91  union all select 
    51700003 ,'18OCT2006 11:50' ,100 union all select 
    51700004 ,'17OCT2006 11:41' ,89  union all select
    51700004 ,'17OCT2006 11:50' ,93  union all select
    51700004 ,'17OCT2006 11:52' ,91  union all select
    51700004 ,'17OCT2006 12:00' ,93  union all select
    51700005 ,'17OCT2006 11:41' ,89  union all select
    51700005 ,'17OCT2006 11:50' ,86  union all select
    51700005 ,'17OCT2006 11:52' ,86  union all select
    51700005 ,'17OCT2006 11:53' ,86  union all select
    51700005 ,'17OCT2006 12:00' ,93  union all select
    51700006 ,'17OCT2006 11:53' ,86  union all select
    51700006 ,'17OCT2006 12:00' ,93
    
    
    select a.PatientID ,a.dt ,a.OperatorIndex --,RowNum=count(*)
    from #t1 a, #t1 b
    where a.OperatorIndex>90
    and b.OperatorIndex>90
    and a.PatientID=b.PatientID
    and (a.dt>b.dt 
         or(a.dt=b.dt and a.OperatorIndex>=b.OperatorIndex))
      and exists -- there must be at least 2
    (select 1 from #t1 c
     where a.PatientID=c.PatientID
       and c.OperatorIndex>90
     group by c.PatientID
     having count(*)>=2
    )
    group by a.PatientID ,a.dt ,a.OperatorIndex
    having count(*)<=2 -- show only first 2
    
    union all
    
    select a.PatientID ,a.dt ,a.OperatorIndex --,RowNum=count(*)
    from #t1 a, #t1 b
    where a.OperatorIndex>80
    and b.OperatorIndex>80
    and a.PatientID=b.PatientID
    and (a.dt>b.dt 
         or(a.dt=b.dt and a.OperatorIndex>=b.OperatorIndex))
      and not exists -- there wasn't at least two > 90
    (select 1 from #t1 c
     where a.PatientID=c.PatientID
       and c.OperatorIndex>90
     group by c.PatientID
     having count(*)>=2
    )
      and exists -- there must be at least 4
    (select 1 from #t1 d
     where a.PatientID=d.PatientID
       and d.OperatorIndex>80
     group by d.PatientID
     having count(*)>=4 
    )
    group by a.PatientID ,a.dt ,a.OperatorIndex
    having count(*)<=4 -- show only first 4
    
    order by a.PatientID, a.dt 
    
    drop table #t1
    And replacing the join to table b with the row_number() function
    I guess the first half of the union can change to
    Code:
    select a.PatientID ,a.dt ,a.OperatorIndex 
    from #t1 a
    where a.OperatorIndex>90
      and exists -- there must be at least 2
    (select 1 from #t1 c
     where a.PatientID=c.PatientID
       and c.OperatorIndex>90
     group by c.PatientID
     having count(*)>=2
    )
    and row_number() over (partition by a.PatientID ,a.dt order by a.PatientID ,a.dt) <= 2 -- show only first 2
    Last edited by pdreyer; 02-02-07 at 09:12.

Posting Permissions

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