Listing records based on condition

Question

Listing records based on condition

jkw

SSC Eights!

Points: 815
More actions
November 6, 2017 at 12:20 pm

#326151

I'm trying to find when a person failed a test during a given time period (cycle) and the results, whether pass or fail, for the cycles after the first failure.
Ex: MemberID 1 failed their first test in cycle2. I need to list that failure and the results for cycle3 and 4 regardless of pass or fail

CREATE TABLE [dbo].[TestTable](
    [MemberID] [int] NULL,
    [CycleID] [int] NULL,
    [TestResult] [nchar](10) NULL
) ON [PRIMARY]
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (1, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (2, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (3, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (4, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (5, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (6, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (7, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (8, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (9, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (10, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (11, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (12, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (13, 1, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (14, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (15, 1, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (1, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (2, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (3, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (4, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (5, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (6, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (7, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (8, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (9, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (10, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (11, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (12, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (13, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (14, 2, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (15, 2, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (1, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (2, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (3, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (4, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (5, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (6, 3, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (7, 3, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (8, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (9, 3, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (10, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (11, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (12, 3, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (13, 3, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (14, 3, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (15, 3, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (1, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (2, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (3, 4, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (4, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (5, 4, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (6, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (7, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (8, 4, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (9, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (10, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (11, 4, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (12, 4, N'Pass')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (13, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (14, 4, N'Fail')
GO
INSERT [dbo].[TestTable] ([MemberID], [CycleID], [TestResult]) VALUES (15, 4, N'Fail')
GO

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183698 More actions · Answer 1

Maybe something like this:
WITH CTE AS( SELECT *, MIN(CASE WHEN TestResult = N'Fail' THEN CycleID END) OVER(PARTITION BY MemberID) FirstFail FROM dbo.TestTable ) SELECT * FROM CTE WHERE CycleID >= FirstFail OR FirstFail IS NULL ORDER BY MemberID, CycleID;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jkw SSC Eights! Points: 815 More actions · Answer 2

That's pointing me in the right direction. I was thinking more in line of pivoting on the cycle ids, but I should be able to work that out
Thanks

drew.allen SSC Guru Points: 76944 More actions · Answer 3

Here is another approach. My initial testing indicates that it should be faster.

WITH CTE AS ( SELECT *, MIN(TestResult) OVER(PARTITION BY MemberID ORDER BY CycleID ROWS UNBOUNDED PRECEDING ) AS TestStatus FROM #TestTable ) SELECT * FROM CTE WHERE TestStatus = 'Fail' ORDER BY MemberID, CycleID ;

Here are some tests results:
Drew's Version Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TestTable________00000003772C'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

Luis' version Table 'Worktable'. Scan count 3, logical reads 181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TestTable________00000003772C'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

I did take out a bunch of underscores to make the lines more legible.

Notice that both have one scan and one logical read of the base table, but Luis' has 3 scans, and 181 logical reads of the 'Worktable' (CTE).

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

drew.allen - Monday, November 6, 2017 2:36 PM
Here is another approach. My initial testing indicates that it should be faster.
WITH CTE AS ( SELECT *, MIN(TestResult) OVER(PARTITION BY MemberID ORDER BY CycleID ROWS UNBOUNDED PRECEDING ) AS TestStatus FROM #TestTable ) SELECT * FROM CTE WHERE TestStatus = 'Fail' ORDER BY MemberID, CycleID ;
Here are some tests results:
Drew's Version Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TestTable________00000003772C'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Luis' version Table 'Worktable'. Scan count 3, logical reads 181, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0. Table '#TestTable________00000003772C'. Scan count 1, logical reads 1, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
I did take out a bunch of underscores to make the lines more legible.
Notice that both have one scan and one logical read of the base table, but Luis' has 3 scans, and 181 logical reads of the 'Worktable' (CTE).
Drew

Do you need to come here with all those facts and fancy fast code? 😀
That's a nice solution you got there.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

jkw SSC Eights! Points: 815 More actions · Answer 5

Thanks for all of your input. I was able to get what I needed from the scripts everyone provided.