Comparing rows in same table

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 6:12 am

  #262279

  My question is how to compare rows in the same table. I have processed data and got it into following format.

  ClientIDRefDateClosureDate StartDate EndDate SeqNo CountEpisode

  502013505/05/201218/05/2012 08/05/201214/05/2012 1 1

  502013518/05/201217/05/2012 18/05/201218/05/2012 2

  502013519/05/2012 21/05/2012 3

  507930314/05/201224/05/2012 14/05/201214/05/2012 1 1

  507930324/05/2012 28/05/2012 2

  501101804/04/201216/04/2012 04/04/201212/04/2012 1 1

  501101816/04/201224/05/2012 16/04/201203/05/2012 2

  501101817/04/201203/05/2012 24/04/201230/04/2012 3

  4027618/04/201223/05/2012 18/04/201218/04/2012 1 1

  4027612/05/201223/05/2012 15/05/201215/05/2012 2

  4027625/05/2012 25/05/2012 3

  e.g., first client "5020135" i got 3 records with SeqNo 1, 2 and 3. I will always count the first row (with SeqNo 1) in CountEpisode. The 2nd record (with SeqNo2) I have to check 2 conditions a) if ClosureDate is populated in 1st record (with SeqNo1) if yes then b) If the difference of "Record 2 StartDate - Record1 EndDate" is >=14.

  Have to use the same login for next record (look back to previous record). There can be more than 3 records.

  Can someone help please, having difficulty solving this problem.

  Thanks

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 30, 2012 at 6:19 am

  #1494604

  SELECT TOP 10 a.*, b.*

  FROM MyTable a

  LEFT JOIN MyTable b

  ON b.ClientID = a.ClientID

  AND b.SeqNo = 2

  AND a.ClosureDate IS NOT NULL -- only join a row from b if closure date is populated in a.

  AND 'Record 2 StartDate - Record1 EndDate is >=14' -- adjust to suit your datatypes

  WHERE a.SeqNo = 1

  EDIT: added filter for closuredate

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 6:58 am

  #1494636

  Thanks for your response

  The total number of records can be more than 3, I can only use your query if there are only 2 records for one client.

  I would like to compare record 2 to 1, then 3 to 2, then 4 to 3 and so on. Based on the condition i would like to update the "CountEpisode" field of highest record to 1 if true and 0 if false.

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 30, 2012 at 7:07 am

  #1494639

  jinkazama_11 (5/30/2012)

  ---

  Thanks for your response

  The total number of records can be more than 3, I can only use your query if there are only 2 records for one client.

  I would like to compare record 2 to 1, then 3 to 2, then 4 to 3 and so on. Based on the condition i would like to update the "CountEpisode" field of highest record to 1 if true and 0 if false.

  Let's see if you can figure this out with a little help.

  Use an inner join, and include SeqNo, something like this:

  a.seqno+1 = b.seqno

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 30, 2012 at 7:11 am

  #1494642

  jinkazama_11 (5/30/2012)

  ---

  Thanks for your response

  The total number of records can be more than 3, I can only use your query if there are only 2 records for one client.

  I would like to compare record 2 to 1, then 3 to 2, then 4 to 3 and so on. Based on the condition i would like to update the "CountEpisode" field of highest record to 1 if true and 0 if false.

  What output do you expect to see from the sample data set you've posted?

  BTW you will attract many more helpers if you can set this up as a create table / insert, so folks only have to copy/paste/run.

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 7:16 am

  #1494648

  jinkazama_11 (5/30/2012)

  ---

  Thanks for your response

  Let's see if you can figure this out with a little help.

  Use an inner join, and include SeqNo, something like this:

  a.seqno+1 = b.seqno

  I have already tried the above condition using a recursive CTE. the problem with this is that it display the result againt first record instead of second record when i compare second record with first.

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 7:18 am

  #1494649

  jinkazama_11 (5/30/2012)

  ---

  jinkazama_11 (5/30/2012)

  ---

  Thanks for your response

  Let's see if you can figure this out with a little help.

  Use an inner join, and include SeqNo, something like this:

  a.seqno+1 = b.seqno

  I have already tried the above condition using a recursive CTE. the problem with this is that it display the result againt first record instead of second record when i compare second record with first.

  I tried to use this

  with CalculateEpisdoes as

  (

  select * from DWMH.HTTEpisodes

  )

  select a.*, cast(b.startdate - a.enddate as int), case when a.ClosureDate is null then 'Open'

  else 'close' end

  from CalculateEpisdoes a

  left outer join CalculateEpisdoes b

  on a.ClientID = b.ClientID

  and a.seqno +1 = b.seqno

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 7:22 am

  #1494652

  ChrisM@Work (5/30/2012)

  ---

  jinkazama_11 (5/30/2012)

  ---

  Thanks for your response

  The total number of records can be more than 3, I can only use your query if there are only 2 records for one client.

  I would like to compare record 2 to 1, then 3 to 2, then 4 to 3 and so on. Based on the condition i would like to update the "CountEpisode" field of highest record to 1 if true and 0 if false.

  What output do you expect to see from the sample data set you've posted?

  BTW you will attract many more helpers if you can set this up as a create table / insert, so folks only have to copy/paste/run.

  From the datset i have posted I would like to populate last field with either 1 or 0. Always 1 when SeqNo = 1

  if SeqNo > 1 then 1 if condtion which i mentioned earlier is true and 0 if condition is false.

  I am going to write the sample create table / insert script in few mins.

• Mark Fitzgerald-331224

  SSCrazy Eights

  Points: 8489

  More actions

  May 30, 2012 at 7:32 am

  #1494659

  Try this. Works with your two rules. It returns the new episode starts, ie sequence=1 or 14days after last closure. (NHS data not included).

  -- Create a table variable for demo only

  declare @TimeStuff table

  (

  ClientID int,

  RefDate date,

  ClosureDate date,

  StartDate date,

  EndDate date,

  SeqNo int,

  CountEpisode int

  )

  -- set dateformat to DDMMYYYY as UK (and other places) date format

  set dateformat dmy

  -- insert data as presented

  insert into @TimeStuff values

  (5020135, '05/05/2012', '18/05/2012', '08/05/2012', '14/05/2012', 1, 1 ),

  (5020135, '18/05/2012', '17/05/2012', '18/05/2012', '18/05/2012' ,2 , NULL),

  (5020135 ,'19/05/2012' ,'21/05/2012', NULL, NULL , 3 ,NULL),

  (5079303 ,'14/05/2012', '24/05/2012', '14/05/2012', '14/05/2012', 1 ,1 ),

  (5079303 ,'24/05/2012', '28/05/2012', NULL,NULL, 2,NULL ),

  (5011018 ,'04/04/2012', '16/04/2012', '04/04/2012', '12/04/2012', 1, 1 ),

  (5011018 ,'16/04/2012', '24/05/2012', '16/04/2012', '03/05/2012' ,2 ,NULL),

  (5011018 ,'17/04/2012' ,'03/05/2012', '24/04/2012', '30/04/2012', 3 ,NULL),

  (40276 ,'18/04/2012', '23/05/2012' ,'18/04/2012', '18/04/2012' ,1 ,1 ),

  (40276 ,'12/05/2012', '23/05/2012', '15/05/2012', '15/05/2012' ,2 ,NULL),

  (40276 ,'25/05/2012', '25/05/2012', NULL,NULL,3,NULL )

  -- Run the query

  ----a) Get the first episode

  select *

  from @TimeStuff

  where

  (SeqNo = 1) -- rule 1

  UNION

  ----b) Get all subsequent episode starts outside of 14 days of previous closure

  select TS2.*

  from @TimeStuff TS1

  left join @TimeStuff TS2

  on TS1.ClientID = TS2.ClientID

  and TS1.SeqNo = TS2.SeqNo-1

  where

  (TS1.ClosureDate IS NOT NULL and

  DATEDIFF(DD,TS1.EndDate,TS2.StartDate) >= 14) -- rule 2

  Fitz

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 7:55 am

  #1494677

  Thanks Chris and Mark

  Mark i think your code will work, i am going to test now, need to make small modifcation to update the last feild "CountEpisode"

  Thaks alot for your help.

  Cheers

• jinkazama_11

  SSC-Addicted

  Points: 479

  More actions

  May 30, 2012 at 8:24 am

  #1494704

  Just to confirm its working as required, thanks alot for helping me.

  cheers