select rows where values appear consecutively

Question

Post reply

select rows where values appear consecutively

jkw

SSC Eights!

Points: 815
More actions
September 24, 2012 at 6:30 am

#268231

Ok, I have a question. I need to select data from a table where a specific value appears for a user 2 or more times consecutively. For example:
User Year Status1 Status2 Status3
1 2011 Pass Fail Fail
2 2011 Pass Pass Pass
1 2012 Pass Fail Pass
2 2012 Pass Fail Pass
The query would select all records like User #1 because they had two years in a row where they "Failed" in the Status2 category. The user would have to fail a specific Status category 2 consecutive years in a row.
Any help would be appreciated. Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

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

laurie-789651 SSCertifiable Points: 7680 More actions · Answer 1

How about this?

--========= TEST DATA =============

declare @a table ([User] int, [Year] Char(4), Status1 Char(4), Status2 Char(4), Status3 Char(4));

insert @a values ( 1, 2011, 'Pass', 'Fail', 'Fail' );

insert @a values ( 2, 2011, 'Pass', 'Pass', 'Pass' );

insert @a values ( 1, 2012, 'Pass', 'Fail', 'Pass' );

insert @a values ( 2, 2012, 'Pass', 'Fail', 'Pass' );

select * from @a;

-- The query would select all records like User #1 because they had two years in a row

-- where they "Failed" in the Status2 category. The user would have to fail a specific

-- Status category 2 consecutive years in a row.

--========= SOLUTION =============

select *

from

(

select [User],

Year1=MAX(case when [Year]='2011' then [Year] end),

Year2=MAX(case when [Year]='2012' then [Year] end),

Status1Year1=MAX(case when [Year]='2011' then [Status1] end),

Status1Year2=MAX(case when [Year]='2012' then [Status1] end),

Status2Year1=MAX(case when [Year]='2011' then [Status2] end),

Status2Year2=MAX(case when [Year]='2012' then [Status2] end),

Status3Year1=MAX(case when [Year]='2011' then [Status3] end),

Status3Year2=MAX(case when [Year]='2012' then [Status3] end)

from @a

group by [User]

) z

where (Status1Year1='Fail' and Status1Year2='Fail')

or (Status2Year1='Fail' and Status2Year2='Fail')

or (Status3Year1='Fail' and Status3Year2='Fail');

Jason-299789 SSC-Insane Points: 21601 More actions · Answer 2

It would help if you provided the results that you were expecting as there are a couple of ways of doing this.

Using the previous sample data (except that Year is an Int instead of char) this is my take on the problem

declare @a table ([User] int, [Year] int, Status1 Char(4), Status2 Char(4), Status3 Char(4));

insert @a values ( 1, 2011, 'Pass', 'Fail', 'Fail' );

insert @a values ( 2, 2011, 'Pass', 'Pass', 'Pass' );

insert @a values ( 1, 2012, 'Pass', 'Fail', 'Pass' );

insert @a values ( 2, 2012, 'Pass', 'Fail', 'Pass' );

Select

x.*

From

@a x

JOIN @a y on x.[User]=y.[User]

and x.[Year] =y.Year+1

Where

(x.Status1=y.Status1 and x.Status1='Fail')

or (x.Status2=y.Status2 and x.Status2='Fail')

or (x.Status3=y.Status3 and x.Status3='Fail')

This will only work for checking consecutive years and not ranges of years.

_________________________________________________________________________
SSC Guide to Posting and Best Practices

Dwain Camps SSC Guru Points: 86948 More actions · Answer 3

This approach looks only at consecutive years also:

declare @a table ([User] int, [Year] int, Status1 Char(4), Status2 Char(4), Status3 Char(4));

insert @a values ( 1, 2011, 'Pass', 'Fail', 'Fail' );

insert @a values ( 2, 2011, 'Pass', 'Pass', 'Pass' );

insert @a values ( 3, 2011, 'Pass', 'Fail', 'Pass' );

insert @a values ( 1, 2012, 'Pass', 'Fail', 'Pass' );

insert @a values ( 2, 2012, 'Pass', 'Fail', 'Pass' );

insert @a values ( 3, 2012, 'Pass', 'Pass', 'Pass' );

insert @a values ( 1, 2013, 'Pass', 'Pass', 'Pass' );

insert @a values ( 2, 2013, 'Pass', 'Pass', 'Pass' );

insert @a values ( 3, 2013, 'Pass', 'Fail', 'Pass' );

;WITH CTE AS (

SELECT [User], [Year], n, status

FROM @a

CROSS APPLY (

VALUES (1, Status1), (2, Status2), (3, Status3)) a(n, [status])

WHERE status = 'Fail')

SELECT a.[User], [Status]=a.n

FROM CTE a

INNER JOIN CTE b ON a.[User] = b.[User] AND a.n = b.n AND a.[Year] = b.[Year] - 1

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

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

jkw

SSC Eights!

Points: 815

October 12, 2012 at 7:45 am

#1548644

Thanks, I think I have what I need now

TomThomson SSC Guru Points: 104773 More actions · Answer 5

CELKO (10/12/2012)
Then try this query:
SELECT student_id
FROM Gradebook
GROUP BY student_id
HAVING (MIN(course_1)= MAX(course_1) AND MIN(course_1) = 'fail')
OR (MIN(course_2)= MAX(course_2) AND MIN(course_2) = 'fail')
OR (MIN(course_3)= MAX(course_3) AND MIN(course_3) = 'fail');

Oh, great. A solution that works solely because you have only two years data, and they are two successive years. The original post made the point that the two fails have to be in consecutive years, which suggests fairly strongly that the problem isn't limited to two consecutive years (since if it were, that point would have no consequences for the solution).

And sloppy coding: insert tablename values.... instead of insert tablename9<column list>) values...

And choosing a string representation of a year number that is a bizarre contrivance from MySql instead of using an integer, or a natural string rtepresentation of a number.

You can do better than that, JC!

Tom

TomThomson SSC Guru Points: 104773 More actions · Answer 6

CELKO (10/14/2012)
Then try this query. The CTE can be changed quickly to other report periods. I like this because MIN() and MAX() can be optimized into the GROUP BY
WITH Year_Pairs (prior_school_year, current_school_year)
AS
( SELECT X.*
FROM (VALUES ('2011-00-00' ,'2012-00-00'),
('2010-00-00' ,'2011-00-00'),
('2009-00-00' ,'2010-00-00'),
('2008-00-00' ,'2009-00-00'),
('2007-00-00' ,'2008-00-00'))
AS X(prior_school_year, current_school_year)
)
SELECT MIN(G.school_year) AS current_school_year, G.student_id
FROM Gradebook AS G, Year_Pairs AS P
WHERE G.school_year
IN (P.current_school_year, P.prior_school_year)
GROUP BY student_id
HAVING (MIN(course_1)= MAX(course_1) AND MIN(course_1) = 'fail')
OR (MIN(course_2)= MAX(course_2) AND MIN(course_2) = 'fail')
OR (MIN(course_3)= MAX(course_3) AND MIN(course_3) = 'fail');

Yes, with that CTE added it works and looks quite flexible.

Tom

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

Ok, here's the way things are laid out in my table. I hope this makes sense. What I'm looking to get is to obtain the Student ID of each student who have a 'FAIL' in one of the 'Status' category consecutively 2 years in a row. So the script would pull StudentID 1 because they failed the Status3 category 2 consecutive years (not semesters) in a row. So the person would have to have a 'FAIL' status in Semesters 1 and 2 for two consecutive years in a row of a specific category.

StudentID 1 would be a valid result because they failed in Status3 in years 2012 and 2011

StudentID 2 would be a valid result because they failed in Status2 in years 2012 and 2011

Here's my table

/****** Object: Table [dbo].[StudStat] Script Date: 10/14/2012 21:06:26 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

SET ANSI_PADDING ON

GO

CREATE TABLE [dbo].[StudStat](

[StudentID] [int] NULL,

[Year] [int] NULL,

[Semester] [int] NULL,

[Status1] [char](10) NULL,

[Status2] [char](10) NULL,

[Status3] [char](10) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

ALTER TABLE [dbo].[StudStat] ADD CONSTRAINT [DF_Table_1_Status10] DEFAULT ((0)) FOR [Status1]

GO

ALTER TABLE [dbo].[StudStat] ADD CONSTRAINT [DF_StudStat_Status2] DEFAULT ((0)) FOR [Status2]

GO

ALTER TABLE [dbo].[StudStat] ADD CONSTRAINT [DF_StudStat_Status3] DEFAULT ((0)) FOR [Status3]

GO

++++++++++++++++++++++++++++++++++++++++++++++