Datediff on same column

• asbains8

  Say Hey Kid

  Points: 690

  More actions

  October 19, 2010 at 10:04 am

  #74341

  I am trying to exclude records from my query where the datediff is more than 14 days from the original specimen date and where patient id, organism id, antib id and result match e.g the first 3 records would be grouped together and the next 3 and so on. My data table looks like below:

  Patient id Organism id Antib id Result Specimen date

  12345678 1586.0009AMOXR2010-03-30 00:00:00.000

  12345678 1586.0009AMOXR2010-04-06 00:00:00.000

  12345678 1586.0009AMOXR2010-04-20 00:00:00.000

  12345678 1586.0009CEFS2010-03-30 00:00:00.000

  12345678 1586.0009CEFS2010-04-06 00:00:00.000

  12345678 1586.0009CEFS2010-04-20 00:00:00.000

  12345678 1586.0009CIPS2010-03-30 00:00:00.000

  12345678 1586.0009CIPS2010-04-06 00:00:00.000

  12345678 1586.0009CIPS2010-04-20 00:00:00.000

  12345678 1586.0009CLEXS2010-03-30 00:00:00.000

  12345678 1586.0009CLEXS2010-04-06 00:00:00.000

  12345678 1586.0009CLEXS2010-04-20 00:00:00.000

  12345678 1586.0009NITROS2010-03-30 00:00:00.000

  12345678 1586.0009NITROS2010-04-06 00:00:00.000

  12345678 1586.0009NITROS2010-04-20 00:00:00.000

  12345678 1586.0009TMPR2010-03-30 00:00:00.000

  12345678 1586.0009TMPR2010-04-06 00:00:00.000

  12345678 1586.0009TMPR2010-04-20 00:00:00.000

  12345678 2441.0000CIPS2010-06-08 00:00:00.000

  12345678 2441.0000CIPS2010-07-26 00:00:00.000

  12345678 2441.0000CIPS2010-07-26 00:00:00.000

  12345678 2441.0000ERYS2010-06-01 00:00:00.000

  12345678 2441.0000ERYS2010-06-08 00:00:00.000

  12345678 2441.0000ERYS2010-07-26 00:00:00.000

  12345678 2441.0000ERYS2010-07-26 00:00:00.000

  12345678 2441.0000FLUS2010-06-01 00:00:00.000

  12345678 2441.0000FLUS2010-06-08 00:00:00.000

  12345678 2441.0000FLUS2010-07-26 00:00:00.000

  12345678 2441.0000FLUS2010-07-26 00:00:00.000

  12345678 2441.0000FUSS2010-06-08 00:00:00.000

  12345678 2441.0000FUSS2010-07-26 00:00:00.000

  12345678 2441.0000FUSS2010-07-26 00:00:00.000

  12345678 2441.0000GENTS2010-06-01 00:00:00.000

  12345678 2441.0000GENTS2010-06-08 00:00:00.000

  12345678 2441.0000GENTS2010-07-26 00:00:00.000

  12345678 2441.0000GENTS2010-07-26 00:00:00.000

  12345678 2441.0000LINS2010-06-01 00:00:00.000

  12345678 2441.0000LINS2010-06-08 00:00:00.000

  12345678 2441.0000LINZS2010-07-26 00:00:00.000

  12345678 2441.0000LINZS2010-07-26 00:00:00.000

  12345678 2441.0000MUPS2010-06-01 00:00:00.000

  12345678 2441.0000MUPS2010-06-08 00:00:00.000

  12345678 2441.0000MUPS2010-07-26 00:00:00.000

  12345678 2441.0000MUPS2010-07-26 00:00:00.000

  12345678 2441.0000NEOS2010-06-08 00:00:00.000

  12345678 2441.0000NEOS2010-07-26 00:00:00.000

  12345678 2441.0000NEOS2010-07-26 00:00:00.000

  12345678 2441.0000RIFS2010-06-08 00:00:00.000

  12345678 2441.0000RIFS2010-07-26 00:00:00.000

  12345678 2441.0000RIFS2010-07-26 00:00:00.000

  12345678 2441.0000TETS2010-06-01 00:00:00.000

  12345678 2441.0000TETS2010-06-08 00:00:00.000

  12345678 2441.0000TETS2010-07-26 00:00:00.000

  12345678 2441.0000TETS2010-07-26 00:00:00.000

  12345678 2441.0000VANS2010-06-08 00:00:00.000

  12345678 2441.0000VANS2010-07-26 00:00:00.000

  12345678 2441.0000VANS2010-07-26 00:00:00.000

  12345678 2510.0000ERYS2010-07-26 00:00:00.000

  12345678 2510.0000FLUS2010-07-26 00:00:00.000

  12345678 2510.0000PENS2010-07-26 00:00:00.000

  12345678 2510.0000TETR2010-07-26 00:00:00.000

  So from the first 3 records i would only want to keep the first and third record as the datediff between this is more than 14 days and exclude the second record. I have tried lots of queries but cannot get it to work.

  Any help would be much appreciated.

• Lowell

  SSC Guru

  Points: 323518

  More actions

  October 19, 2010 at 10:22 am

  #1239176

  this gets you the data in a manner where you can look at it; fromt here you can start filtering on your 14 day requirement.

  notice how I took the time to put your data into a format than any of us unpaid volunteers can copy and paste into SSMS to start testing a solution?

  you need to do that in the future in order to get people to look at the solution...if it's not there, most people will move on to the next question without reading your problem.

  With myCTE As (

  SELECT '12345678' AS PatientID,'1586.0009' AS OrganismID,'AMOX' AS AntibID,'R' AS Result,'2010-03-30 00:00:00.000' As SpecimenDate,'' As Filler UNION ALL

  SELECT '12345678','1586.0009','AMOX','R','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','AMOX','R','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CEF','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CEF','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CEF','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CIP','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CIP','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CIP','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CLEX','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CLEX','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CLEX','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','NITRO','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','NITRO','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','NITRO','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','TMP','R','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','TMP','R','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','TMP','R','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','CIP','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','CIP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','CIP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FUS','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FUS','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FUS','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LIN','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LIN','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LINZ','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LINZ','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','NEO','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','NEO','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','NEO','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','RIF','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','RIF','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','RIF','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','VAN','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','VAN','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','VAN','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','ERY','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','FLU','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','PEN','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','TET','R','2010-07-26 00:00:00.000',''

  )

  SELECT ROW_NUMBER() OVER(PARTITION BY PatientID,OrganismID,AntibID,Result ORDER BY PatientID,SpecimenDate DESC) AS RW,

  *

  FROM myCTE

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• asbains8

  Say Hey Kid

  Points: 690

  More actions

  October 19, 2010 at 10:29 am

  #1239184

  Thanks for that i am a newbiw on these forums will remeber for next time, i am using SQL 2000 so ROW_NUMBER wont work is there an equivalent in sql 2000?

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  October 19, 2010 at 11:22 am

  #1239222

  asbains8 (10/19/2010)

  ---

  Thanks for that i am a newbiw on these forums will remeber for next time, i am using SQL 2000 so ROW_NUMBER wont work is there an equivalent in sql 2000?

  I'm not real clear on what you are looking for, as your request could be interpretted several ways. Are you saying you want to exclude PatientID/OrganismID/AntibID instances where the time since *any* of the instances is greater then 14 days? Or are you saying you want to exclude the ones where the time between the first and last instances is greater than 14 days? Anyhow, try the below, and it would help if you showed *exactly* how you want your output to look based on the sample data you provided and Lowell formated.

  --Create a test table

  IF OBJECT_ID('TempDB..#t','u') IS NOT NULL

  DROP TABLE #t

  CREATE TABLE #t

  (

  PatientID INT,

  OrganismID VARCHAR(20),

  AntibID VARCHAR(20),

  Result CHAR(1),

  SpecimenDate SMALLDATETIME,

  Filler CHAR(1)

  )

  GO

  --Populate it with your data, so graciously formatted by Lowell

  INSERT INTO #t

  SELECT '12345678' AS PatientID,'1586.0009' AS OrganismID,'AMOX' AS AntibID,'R' AS Result,'2010-03-30 00:00:00.000' As SpecimenDate,'' As Filler UNION ALL

  SELECT '12345678','1586.0009','AMOX','R','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','AMOX','R','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CEF','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CEF','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CEF','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CIP','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CIP','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CIP','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CLEX','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CLEX','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','CLEX','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','NITRO','S','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','NITRO','S','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','NITRO','S','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','TMP','R','2010-03-30 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','TMP','R','2010-04-06 00:00:00.000','' UNION ALL

  SELECT '12345678','1586.0009','TMP','R','2010-04-20 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','CIP','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','CIP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','CIP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','ERY','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FLU','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FUS','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FUS','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','FUS','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','GENT','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LIN','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LIN','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LINZ','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','LINZ','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','MUP','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','NEO','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','NEO','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','NEO','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','RIF','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','RIF','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','RIF','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-06-01 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','TET','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','VAN','S','2010-06-08 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','VAN','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2441.0000','VAN','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','ERY','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','FLU','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','PEN','S','2010-07-26 00:00:00.000','' UNION ALL

  SELECT '12345678','2510.0000','TET','R','2010-07-26 00:00:00.000',''

  --See both the time in days since the first instance of each

  --PatientID/OrganismID/AntibID, and also the difference

  --between the first and last instance.

  SELECT

  *,

  [Max - Min] = DATEDIFF(d,t1.MinSpecimenDate,t1.MaxSpecimenDate),

  AGE = DATEDIFF(d,t1.MinSpecimenDate,GETDATE())

  FROM

  (

  SELECT

  PatientID,

  OrganismID,

  AntibID,

  MinSpecimenDate = MIN(SpecimenDate),

  MaxSpecimenDate = MAX(SpecimenDate)

  FROM #t

  GROUP BY PatientID,OrganismID,AntibID

  ) t1

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• asbains8

  Say Hey Kid

  Points: 690

  More actions

  October 19, 2010 at 12:33 pm

  #1239276

  Sorry I made a typo on my original request what I meant to say is I would like to exclude records which are less than 14 days from the first specimen date shown this is why from my first post I was saying I would only want to output the first and third record and not the second as this was less than 14 days from the first records specimen date. When I'm at work tomorrow I will post what the output should look like. Hope this clears things up.

  Sorry for the confusion.

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  October 19, 2010 at 1:52 pm

  #1239311

  asbains8 (10/19/2010)

  ---

  Sorry I made a typo on my original request what I meant to say is I would like to exclude records which are less than 14 days from the first specimen date shown this is why from my first post I was saying I would only want to output the first and third record and not the second as this was less than 14 days from the first records specimen date. When I'm at work tomorrow I will post what the output should look like. Hope this clears things up.

  Sorry for the confusion.

  asbains8, try this instead.

  SELECT

  t2.*

  FROM #t t2 INNER JOIN

  (

  SELECT

  PatientID,

  OrganismID,

  AntibID,

  Result,

  MinSpecimenDate = MIN(SpecimenDate)

  FROM #t

  GROUP BY PatientID,OrganismID,AntibID,Result

  ) t1

  ON t1.PatientID = t2.PatientID

  AND t1.OrganismID = t2.OrganismID

  AND t1.AntibID = t2.AntibID

  AND t1.Result = t2.result

  WHERE (DATEDIFF(d,t1.MinSpecimenDate,t2.SpecimenDate) >= 14

  OR t1.MinSpecimenDate = t2.SpecimenDate)

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• asbains8

  Say Hey Kid

  Points: 690

  More actions

  October 20, 2010 at 2:35 am

  #1239520

  Thanks Greg that worked a treat.

• sharath.chalamgari

  SSCertifiable

  Points: 5680

  More actions

  October 20, 2010 at 4:36 am

  #1239561

  i was looking for similar solution and it is realy simple and helpfull

  Thanks to greg

• Greg Snidow

  SSCoach

  Points: 16108

  More actions

  October 20, 2010 at 8:09 pm

  #1240027

  Thank you both for the feedback, I just wish I still had the time to hang out here like I used to. I think I have forgotten more in the past year than I have learned.

  Greg
  _________________________________________________________________________________________________
  The glass is at one half capacity: nothing more, nothing less.

• asbains8

  Say Hey Kid

  Points: 690

  More actions

  October 29, 2010 at 5:34 am

  #1244065

  Hi I need the query changing so that it now looks for records where the time since any date is greater than 14 days where patient id, organism id, antib id and result are all the same. I have created a test table below with some data.

  CREATE TABLE #t

  (

  PatientID INT,

  ORganiSmID VARCHAR,(20),

  AntibID VARCHAR(20),

  ReSult CHAR(1),

  SpecimenDate SMALLDATETIME,

  FilleR CHAR,(1)

  )

  GO

  INSERT INTO #t

  SELECT '123456' AS PatientID,'1571.0010' AS ORganiSmID,'AMI' AS AntibID,'S,'AS ReSult,'2010-03-03 00:00:00.000' AS SpecimenDate,'' AS FilleR UNION ALL

  SELECT '123456','1571.0010','AMI',S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','R','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','R','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','R','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','R','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','S,'2009-11-17 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','S,'2009-12-02 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','S,'2009-12-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AMPAMOX','S,'2010-02-19 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2009-11-17 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2009-12-02 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2009-12-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2010-01-22 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2010-02-19 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','AUG','S,'2010-07-15 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2009-11-17 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2009-12-02 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2009-12-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2010-01-22 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2010-02-19 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CEF','S,'2010-07-15 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CIP','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','CIP','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','ER','T','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','ER','T','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','R','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','R','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','S,'2009-11-17 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','S,'2009-12-02 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','S,'2009-12-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','S,'2010-01-22 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','S,'2010-02-19 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','GENT','S,'2010-07-15 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','IMI','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','IMI','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2009-11-17 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2009-12-02 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2009-12-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2010-01-22 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2010-02-19 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','NITRO','S,'2010-07-15 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TCIN','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TCIN','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','R','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','R','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','S,'2009-11-17 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','S,'2009-12-02 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','S,'2009-12-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','S,'2010-01-22 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','S,'2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TMP','S,'2010-06-09 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TOBS','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '123456','1571.0010','TOBS','2010-06-09 00:00:00.000','' UNION ALL

  So if we look at antibiotic AMPOX it has 8 entries shown below:

  123456 1571.0010AMPAMOXS2009-11-17 00:00:00.000

  123456 1571.0010AMPAMOXS2009-12-02 00:00:00.000

  123456 1571.0010AMPAMOXS2009-12-03 00:00:00.000

  123456 1571.0010AMPAMOXR2010-01-22 00:00:00.000

  123456 1571.0010AMPAMOXS2010-02-19 00:00:00.000

  123456 1571.0010AMPAMOXR2010-03-03 00:00:00.000

  123456 1571.0010AMPAMOXR2010-06-09 00:00:00.000

  123456 1571.0010AMPAMOXR2010-07-15 00:00:00.000

  I would like to remove the 3 record as the date difference between the one above is only 1 day and keep the rest as they are all >=14 days.

• asbains8

  Say Hey Kid

  Points: 690

  More actions

  October 29, 2010 at 6:49 am

  #1244122

  Please use this to create the data table the previous one i sent was a an earlier version which had errors:

  IF OBJECT_ID('TempDB..#t','u') IS NOT NULL

  DROP TABLE #g

  CREATE TABLE #g

  (

  PatientID int,

  OrganiSmID VARCHAR(20),

  AntibID VARCHAR(20),

  ReSult CHAR(1),

  SpecimenDate SMALLDATETIME,

  Filler CHAR(1)

  )

  GO

  INSERT INTO #g

  SELECT '077611' AS PatientID,'1571.0010' AS OrganiSmID,'AMI' AS AntibID,'S' AS ReSult,'2010-03-03 00:00:00.000' AS SpecimenDate,'' AS Filler UNION ALL

  SELECT '077611','1571.0010','AMI','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','R','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','R','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','R','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','R','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','S','2009-11-17 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','S','2009-12-02 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','S','2009-12-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AMPAMOX','S','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2009-11-17 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2009-12-02 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2009-12-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','AUG','S','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2009-11-17 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2009-12-02 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2009-12-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CEF','S','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CIP','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','CIP','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','ERT','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','ERT','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','R','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','R','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','S','2009-11-17 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','S','2009-12-02 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','S','2009-12-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','S','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','S','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','GENT','S','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','IMI','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','IMI','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2009-11-17 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2009-12-02 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2009-12-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','NITRO','S','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TCIN','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TCIN','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','R','2010-02-19 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','R','2010-07-15 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','S','2009-11-17 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','S','2009-12-02 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','S','2009-12-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','S','2010-01-22 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TMP','S','2010-06-09 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TOBS','S','2010-03-03 00:00:00.000','' UNION ALL

  SELECT '077611','1571.0010','TOBS','S','2010-06-09 00:00:00.000',''