October 19, 2018 at 6:49 am
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?
October 19, 2018 at 7:23 am
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
October 19, 2018 at 7:30 am
See my answer in your other post.
https://www.sqlservercentral.com/Forums/FindPost2004619.aspx
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply