October 19, 2018 at 5:03 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
, DateDiff(minute, InActive.HistoryDateTime, Active.HistoryDateTime) IdleTime
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 5:16 am
Rather than using a JOIN with the clause InActive.RowNumber = Active.RowNumber -1 you would be much better looking at the LEAD and LAG functions. For example:SELECT *,
LAG(HistoryDateTime) OVER (ORDER BY HistoryDateTime)
FROM #OutputTable;
Also, why is HistoryDateTime2 a varchar? That is clearly a date and should be stored as one.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 19, 2018 at 6:36 am
Thom A - Friday, October 19, 2018 5:16 AMRather than using a JOIN with the clause InActive.RowNumber = Active.RowNumber -1 you would be much better looking at the LEAD and LAG functions. For example:SELECT *,
LAG(HistoryDateTime) OVER (ORDER BY HistoryDateTime)
FROM #OutputTable;
Also, why is HistoryDateTime2 a varchar? That is clearly a date and should be stored as one.
I am developing on SQL 2008 so LAG isnt available to me
October 19, 2018 at 6:43 am
Weegee2017 - Friday, October 19, 2018 6:36 AMThom A - Friday, October 19, 2018 5:16 AMRather than using a JOIN with the clause InActive.RowNumber = Active.RowNumber -1 you would be much better looking at the LEAD and LAG functions. For example:SELECT *,
LAG(HistoryDateTime) OVER (ORDER BY HistoryDateTime)
FROM #OutputTable;
Also, why is HistoryDateTime2 a varchar? That is clearly a date and should be stored as one.I am developing on SQL 2008 so LAG isnt available to me
/Headesk then did you post in the 2016 forum?
I'm afraid then you're stuck with the syntax you have then. I would really consider upgrading though. 2008 is way out of date, and support.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 19, 2018 at 6:47 am
Thom A - Friday, October 19, 2018 6:43 AMWeegee2017 - Friday, October 19, 2018 6:36 AMThom A - Friday, October 19, 2018 5:16 AMRather than using a JOIN with the clause InActive.RowNumber = Active.RowNumber -1 you would be much better looking at the LEAD and LAG functions. For example:SELECT *,
LAG(HistoryDateTime) OVER (ORDER BY HistoryDateTime)
FROM #OutputTable;
Also, why is HistoryDateTime2 a varchar? That is clearly a date and should be stored as one.I am developing on SQL 2008 so LAG isnt available to me
/Headesk then did you post in the 2016 forum?
I'm afraid then you're stuck with the syntax you have then. I would really consider upgrading though. 2008 is way out of date, and support.
My bad i should have posted this on 2008 forum
Apologies
October 19, 2018 at 6:55 am
;WITH CTE AS
(
SELECT a.HoldingID,
a.IndicatorID,
a.[Status],
a.HistoryDateTime,
CAST(a.HistoryDateTime AS date) AS ActivityDate,
b.IndicatorID AS PrevIndicatorID,
b.[Status] AS PrevStatus,
b.HistoryDateTime AS PrevHistoryDateTime,
CAST(b.HistoryDateTime AS date) AS PrevActivityDate
FROM #OutputTable a
OUTER APPLY (SELECT TOP(1) *
FROM #OutputTable b
WHERE b.HoldingID = a.HoldingID
AND b.HistoryDateTime < a.HistoryDateTime
ORDER BY b.HistoryDateTime DESC) AS b
)
SELECT HoldingID,
IndicatorID,
[Status],
PrevStatus,
HistoryDateTime,
ActivityDate,
CASE WHEN [Status] = 'D' AND PrevStatus = 'C' THEN 'Inactive'
WHEN [Status] = 'U' AND PrevStatus = 'D' THEN 'Active'
ELSE 'Unknown' END Indicator
FROM CTE
October 19, 2018 at 7:45 am
Jonathan AC Roberts - Friday, October 19, 2018 6:55 AM;WITH CTE AS
(
SELECT a.HoldingID,
a.IndicatorID,
a.[Status],
a.HistoryDateTime,
CAST(a.HistoryDateTime AS date) AS ActivityDate,
b.IndicatorID AS PrevIndicatorID,
b.[Status] AS PrevStatus,
b.HistoryDateTime AS PrevHistoryDateTime,
CAST(b.HistoryDateTime AS date) AS PrevActivityDate
FROM #OutputTable a
OUTER APPLY (SELECT TOP(1) *
FROM #OutputTable b
WHERE b.HoldingID = a.HoldingID
AND b.HistoryDateTime < a.HistoryDateTime
ORDER BY b.HistoryDateTime DESC) AS b
)
SELECT HoldingID,
IndicatorID,
[Status],
PrevStatus,
HistoryDateTime,
ActivityDate,
CASE WHEN [Status] = 'D' AND PrevStatus = 'C' THEN 'Inactive'
WHEN [Status] = 'U' AND PrevStatus = 'D' THEN 'Active'
ELSE 'Unknown' END Indicator
FROM CTE
Hi Jonathan
Thats briilliant. a nice much elegant solution to my problem than I had.
Thank you very much and have a nice weekend!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply