Using Current and Previous Row of a Group of Data to Identify Status

• Weegee2017

  SSC Eights!

  Points: 994

  More actions

  October 19, 2018 at 6:49 am

  #416207

  Hi there

  I have the following sample data
  IF OBJECT_ID(N'TempDB.dbo.#OutputTable')    IS NOT NULL DROP TABLE #OutputTable 

  Create Table #OutputTable (
   [HistoryDateTime2] [varchar](20) NULL,
   [HistoryDateTime] [datetime] NOT NULL,
   [IndicatorID] [int] NOT NULL,
   [Status] [varchar](4) NOT NULL,
   [HoldingID] [int] NULL
  )
  GO

  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-03-15T14:36:12.537' AS DateTime), N'C',N'15/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T11:04:21.567' AS DateTime), N'D',N'18/03/2018')
  GO
  INSERT #OutputTable([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T11:41:49.137' AS DateTime), N'D',N'18/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T11:42:26.070' AS DateTime), N'U',N'18/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T13:46:17.247' AS DateTime), N'D',N'18/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T13:48:12.213' AS DateTime), N'D',N'18/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T13:49:43.327' AS DateTime), N'U',N'18/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T14:16:30.217' AS DateTime), N'U',N'18/03/2018')
  GO
  INSERT #OutputTable ([HoldingID], [IndicatorID], [HistoryDateTime], [status],[HistoryDateTime2]) VALUES (1013370166, 45905080, CAST(N'2018-10-18T14:24:35.147' AS DateTime), N'U',N'18/03/2018')
  GO

  I want to be able to do the following

  Look for patterns in a sequence of related data

  in the example below, i want to produce a summary
  report of changing statuses

  So when row goes from 'C to 'D' then I want to mark this
  as 'Inactive'

  When a row goes from 'D' to 'U' then mark this as 'Active'

  So I would then have the following data

  HoldingID IndicatorID  DateFrom  DateTo   IndicatorStatus
  1013370166 45905080  15/03/2018 14:36 18/10/2018 11:04 Inactive
  1013370166 45905080  18/10/2018 13:46 18/10/2018 13:49 Active

  Im using the following query but am stuck on how to derive the above result set but not getting any results

  ;With UserActivityWRowNum( RowNumber, HoldingID, IndicatorID, [Status], HistoryDateTime, ActivityDate)
  AS
  (
  Select ROW_NUMBER() OVER (order by HoldingID, IndicatorID,[Status],HistoryDateTime) RowNumber
  , HoldingID
  , IndicatorID
  , [Status]
  , HistoryDateTime
  , CAST (HistoryDateTime As DATE) as ActivityDate
  From #OutputTable
  --where HoldingID =1013370166
  )

  Select Active.HoldingID, Active.IndicatorID, Active.ActivityDate
  , InActive.HistoryDateTime LogOff
  , Active.HistoryDateTime LogOn

  from  (select * from UserActivityWRowNum where [Status] = 'C') Active
  join (select * from UserActivityWRowNum where [Status] = 'D') InActive
   ON InActive.RowNumber = Active.RowNumber -1
   and InActive.HoldingID = Active.HoldingID
   and InActive.ActivityDate = Active.ActivityDate

  can someone please help me out on this?

• John Mitchell-245523

  SSC Guru

  Points: 148809

  More actions

  October 19, 2018 at 7:23 am

  #2010418

  Are you really on SQL Server 2008?  If you're on 2012 or later, you can use the LAG or LEAD function, which will make it a lot simpler.

  John

• Jonathan AC Roberts

  SSCoach

  Points: 18407

  More actions

  October 19, 2018 at 7:30 am

  #2010421

  See my answer in your other post.
  https://www.sqlservercentral.com/Forums/FindPost2004619.aspx