March 28, 2018 at 5:49 am
hi
I have the following data
DROP TABLE #TestHldgs
CREATE TABLE #TestHldgs(
[HoldingID] [float] NULL,
[ClientURN] [float] NULL,
[IndicatorID] [float] NULL,
[IndicatorTypeId] [float] NULL,
[IndicatorStatusID] [float] NULL,
[DateApplied] [datetime] NULL
) ON [PRIMARY]
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079654422, 4, 45655532, 22, 0, CAST(N'2018-01-19T10:46:25.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079654422, 4, 45628305, 22, 1, CAST(N'2018-01-09T12:35:51.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079654422, 4, 45623699, 21, 2, CAST(N'2018-01-08T12:09:51.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079654422, 4, 45623704, 22, 1, CAST(N'2018-01-08T12:10:14.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079660137, 4, 45676249, 21, 0, CAST(N'2018-01-25T12:46:51.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079660137, 4, 45676252, 22, 2, CAST(N'2018-01-25T12:47:08.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079672945, 4, 45655558, 22, 0, CAST(N'2018-01-19T10:54:01.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079677001, 4, 45626069, 22, 0, CAST(N'2018-01-09T08:35:59.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079699899, 4, 45628338, 22, 0, CAST(N'2018-01-09T12:41:46.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079708761, 4, 45657109, 21, 0, CAST(N'2018-01-19T16:55:54.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079708761, 4, 45657113, 22, 0, CAST(N'2018-01-19T16:56:11.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079712474, 4, 45664623, 21, 0, CAST(N'2018-01-24T14:49:56.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079712474, 4, 45664625, 22, 0, CAST(N'2018-01-24T14:50:12.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079735994, 4, 45677501, 22, 0, CAST(N'2018-01-26T07:56:25.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079735994, 4, 45677505, 21, 0, CAST(N'2018-01-26T07:57:55.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079737535, 4, 45636751, 21, 0, CAST(N'2018-01-12T08:24:00.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079737535, 4, 45636752, 22, 0, CAST(N'2018-01-12T08:24:25.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079752749, 4, 45628513, 22, 0, CAST(N'2018-01-09T13:10:46.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079753173, 4, 45640252, 21, 0, CAST(N'2018-01-15T14:08:28.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079753173, 4, 45640253, 22, 0, CAST(N'2018-01-15T14:08:47.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079767145, 4, 45628108, 22, 0, CAST(N'2018-01-09T11:50:43.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079767145, 4, 45628159, 21, 0, CAST(N'2018-01-09T12:04:22.000' AS DateTime))
GO
INSERT #TestHldgs ([HoldingID], [ClientURN], [IndicatorID], [IndicatorTypeId], [IndicatorStatusID], [DateApplied]) VALUES (1079767391, 4, 45627234, 22, 0, CAST(N'2018-01-09T09:32:38.000' AS DateTime))
GO
CREATE
TABLE #IndicatorStatus(IndicatorStatusID int
,[Description]VARCHAR(255)
)
insert into #IndicatorStatus
(IndicatorStatusID
,[Description])
Select 0 as IndicatorStatusID, 'Active' as Description
union all
Select 2 as IndicatorStatusID, 'Deleted' as Description
union all
Select 1 as IndicatorStatusID, 'Dormant' as Description
go
this table identifies a sequence of data
The field HoldingID is repeated and entries made for a column caled IndicatorTypeID
in the above , in every grouping of repeated data for Holding ID, i want to be able to identify those records where the last entry
is IndicatorStatusID = 0
This could be at row 3, row 5 etc for every grouping of repeated data
How can Identify the last record in a grouping as indicatorStatusID = 0?
March 28, 2018 at 9:19 am
Try this on for size:CREATE TABLE #TestHldgs (
HoldingID float NULL,
ClientURN float NULL,
IndicatorID float NULL,
IndicatorTypeId float NULL,
IndicatorStatusID float NULL,
DateApplied datetime NULL
);
INSERT #TestHldgs (HoldingID, ClientURN, IndicatorID, IndicatorTypeId, IndicatorStatusID, DateApplied)
VALUES (1079654422, 4, 45655532, 22, 0, CAST(N'2018-01-19T10:46:25.000' AS DateTime)),
(1079654422, 4, 45628305, 22, 1, CAST(N'2018-01-09T12:35:51.000' AS DateTime)),
(1079654422, 4, 45623699, 21, 2, CAST(N'2018-01-08T12:09:51.000' AS DateTime)),
(1079654422, 4, 45623704, 22, 1, CAST(N'2018-01-08T12:10:14.000' AS DateTime)),
(1079660137, 4, 45676249, 21, 0, CAST(N'2018-01-25T12:46:51.000' AS DateTime)),
(1079660137, 4, 45676252, 22, 2, CAST(N'2018-01-25T12:47:08.000' AS DateTime)),
(1079672945, 4, 45655558, 22, 0, CAST(N'2018-01-19T10:54:01.000' AS DateTime)),
(1079677001, 4, 45626069, 22, 0, CAST(N'2018-01-09T08:35:59.000' AS DateTime)),
(1079699899, 4, 45628338, 22, 0, CAST(N'2018-01-09T12:41:46.000' AS DateTime)),
(1079708761, 4, 45657109, 21, 0, CAST(N'2018-01-19T16:55:54.000' AS DateTime)),
(1079708761, 4, 45657113, 22, 0, CAST(N'2018-01-19T16:56:11.000' AS DateTime)),
(1079712474, 4, 45664623, 21, 0, CAST(N'2018-01-24T14:49:56.000' AS DateTime)),
(1079712474, 4, 45664625, 22, 0, CAST(N'2018-01-24T14:50:12.000' AS DateTime)),
(1079735994, 4, 45677501, 22, 0, CAST(N'2018-01-26T07:56:25.000' AS DateTime)),
(1079735994, 4, 45677505, 21, 0, CAST(N'2018-01-26T07:57:55.000' AS DateTime)),
(1079737535, 4, 45636751, 21, 0, CAST(N'2018-01-12T08:24:00.000' AS DateTime)),
(1079737535, 4, 45636752, 22, 0, CAST(N'2018-01-12T08:24:25.000' AS DateTime)),
(1079752749, 4, 45628513, 22, 0, CAST(N'2018-01-09T13:10:46.000' AS DateTime)),
(1079753173, 4, 45640252, 21, 0, CAST(N'2018-01-15T14:08:28.000' AS DateTime)),
(1079753173, 4, 45640253, 22, 0, CAST(N'2018-01-15T14:08:47.000' AS DateTime)),
(1079767145, 4, 45628108, 22, 0, CAST(N'2018-01-09T11:50:43.000' AS DateTime)),
(1079767145, 4, 45628159, 21, 0, CAST(N'2018-01-09T12:04:22.000' AS DateTime)),
(1079767391, 4, 45627234, 22, 0, CAST(N'2018-01-09T09:32:38.000' AS DateTime));
CREATE NONCLUSTERED INDEX IX_TEMP_TestHldgs ON #TestHldgs
(
HoldingID ASC,
DateApplied ASC,
IndicatorStatusID ASC
)
INCLUDE
(
ClientURN,
IndicatorID,
IndicatorTypeId
);
GO
CREATE TABLE #IndicatorStatus (
IndicatorStatusID int NOT NULL PRIMARY KEY CLUSTERED,
[Description] varchar(255)
);
INSERT INTO #IndicatorStatus (IndicatorStatusID, [Description])
SELECT 0 AS IndicatorStatusID, 'Active' AS [Description]
UNION ALL
SELECT 1 AS IndicatorStatusID, 'Dormant' AS [Description]
UNION ALL
SELECT 2 AS IndicatorStatusID, 'Deleted' AS [Description];
WITH GROUPS AS (
SELECT T.HoldingID, MAX(T.DateApplied) AS MAX_DATE
FROM #TestHldgs AS T
GROUP BY T.HoldingID
)
SELECT T.*, S.[Description]
FROM #TestHldgs AS T
INNER JOIN GROUPS AS G
ON T.HoldingID = G.HoldingID
AND T.DateApplied = G.MAX_DATE
INNER JOIN #IndicatorStatus AS S
ON T.IndicatorStatusID = S.IndicatorStatusID
AND S.[Description] = 'Active'
ORDER BY T.HoldingID, T.DateApplied;
DROP TABLE #TestHldgs;
DROP TABLE #IndicatorStatus;
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
March 28, 2018 at 10:56 am
thats works brilliantly
thank you!
March 28, 2018 at 12:08 pm
Weegee2017 - Wednesday, March 28, 2018 10:56 AMthats works brilliantlythank you!
You're welcome. Glad I could help.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply