Another Interesting Sorting Problem

• DBABC

  SSCarpal Tunnel

  Points: 4813

  More actions

  February 13, 2013 at 2:02 pm

  #289447

  I want to identify the First & Second call based on following business rule.

  If the time difference between two records is between 5 and 120 minutes, we want to mark first record as FirstCall & second record as SecondCall.

  I am having trouble when three consecutive records satisfy the above criteria. Please see the currentOutput.jpg & RequiredOutput.jpg

  CREATE TABLE [MyTransactions](

  SNRank INT IDENTITY(1,1) Primary Key,

  [TTime] [datetime] NULL,

  [TFunction] [varchar](50) NULL,

  [TStatus] [varchar](50) NULL

  )

  INSERT INTO MyTransactions VALUES ('2012-09-25 11:37:47.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2012-09-25 11:38:17.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2012-11-06 15:14:17.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2012-11-06 15:44:20.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2012-11-06 16:24:56.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2012-12-10 18:30:37.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 10:58:47.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 11:06:59.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 11:17:39.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 12:42:23.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 13:47:43.000', 'Activate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 13:50:07.000', 'Dectivate', 'Successful')

  INSERT INTO MyTransactions VALUES ('2013-01-29 14:17:39.000', 'Activate', 'Successful')

  Here is my version of the query which is giving me two consecutive records with "SecondCall" which is incorrect (CurrentOutPut.jpg). I want data in RequiredOutPut.jpg

  SELECTDISTINCT

  CASE WHENFirstCall.SNRank IS NOT NULL

  AND FirstCall.TFunction = 'activate'

  AND SecondCall.TFunction = 'activate'

  AND FirstCall.TStatus = 'Successful'

  AND SecondCall.TStatus = 'Successful'

  AND DATEDIFF(MI, FirstCall.TTime, SecondCall.TTime) BETWEEN 6 AND 120

  THEN 'SecondCall' ELSE 'FirstCall' END,

  DATEDIFF(MI, FirstCall.TTime, SecondCall.TTime) DiffBtwCalls,

  SecondCall.*

  FROM[MyTransactions] SecondCall

  LEFT JOIN [MyTransactions] FirstCall ON FirstCall.SNRank = SecondCall.SNRank - 1

  ORDER BY SNRank

  CURRENT OUT FROM MY QUERY

  

  REQUIRED OUTPUT

  

• DBABC

  SSCarpal Tunnel

  Points: 4813

  More actions

  February 13, 2013 at 4:10 pm

  #1587677

  I think i got this one.

  WITH A as

  (

  SELECTcast('FirstCall' as varchar(100)) Cal, 0 DiffBtwCalls, *

  FROM [MyTransactions]

  WHERESNRank = 1

  UNION ALL

  SELECTcast(CASE WHENFirstCall.SNRank IS NOT NULL

  AND FirstCall.TFunction = 'activate'

  AND SecondCall.TFunction = 'activate'

  AND FirstCall.TStatus = 'Successful'

  AND SecondCall.TStatus = 'Successful'

  AND DATEDIFF(MI, FirstCall.TTime, SecondCall.TTime) BETWEEN 6 AND 120

  AND FirstCall.Cal = 'FirstCall'

  THEN 'SecondCall' ELSE 'FirstCall' END as varchar(100)) Cal,

  DATEDIFF(MI, FirstCall.TTime, SecondCall.TTime) DiffBtwCalls,

  SecondCall.*

  FROM[MyTransactions] SecondCall

  JOIN A FirstCall ON FirstCall.SNRank = SecondCall.SNRank - 1

  )

  SELECTDISTINCT

  *

  FROMA

  order by SNRANK