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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy