Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

select rows where values appear consecutively Expand / Collapse
Author
Message
Posted Monday, September 24, 2012 6:30 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:37 AM
Points: 14, Visits: 171
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
Post #1363452
Posted Monday, September 24, 2012 7:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, June 27, 2014 8:03 AM
Points: 320, Visits: 1,079
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');

Post #1363475
Posted Monday, September 24, 2012 8:03 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Yesterday @ 9:31 AM
Points: 876, Visits: 2,392
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
Post #1363500
Posted Tuesday, September 25, 2012 8:28 PM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 8:29 PM
Points: 3,648, Visits: 5,322
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!

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?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
Post #1364402
Posted Friday, October 12, 2012 7:45 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:37 AM
Points: 14, Visits: 171
Thanks, I think I have what I need now
Post #1372131
Posted Friday, October 12, 2012 10:19 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 1,945, Visits: 3,002
Rows are not records and it is not that hard to post real DDL with meaningful data element names. I like to use the MySQL format for years; you can look it up in other postings. How about this?

CREATE TABLE Gradebook
(student_id INTEGER NOT NULL,
school_year CHAR(10) NOT NULL
CHECK (school_year LIKE '[12][0-9][0-9][0-9]-00-00'),
PRIMARY KEY (student_id, school_year),
course_1 CHAR(4) NOT NULL CHECK (course_1 IN ('pass', 'fail'),
course_2 CHAR(4) NOT NULL CHECK (course_2 IN ('pass', 'fail'),
course_3 CHAR(4) NOT NULL CHECK (course_3 IN ('pass', 'fail'));

We have the ANSI Standard row constructors for insertion now; no need to use old Sybase dialect any more:

INSERT INTO Gradebook
VALUES (1, '2011-00-00', 'pass', 'fail', 'fail'),
(1, '2012-00-00', 'pass', 'fail', 'pass'),
(2, '2011-00-00', 'pass', 'pass', 'pass'),
(2, '2012-00-00', 'pass', 'fail', 'pass');

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');


Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1372398
Posted Sunday, October 14, 2012 7:40 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 PM
Points: 8,830, Visits: 9,388
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
Post #1372503
Posted Sunday, October 14, 2012 1:28 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Today @ 7:06 AM
Points: 1,945, Visits: 3,002

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


Trivial to fix. And the ANSI syntax is INSERT INTO, not he MS shorthand.

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


I disagree and this convention has been proposed for ISO standardization. I do not know the status. Using an INTEGER fails to cast to '0001' to '9999' year display formats as required by ISO-8601 without some string manipulations and constraints. The MySQL convention will sort with the ISO-8601 display formats and can be validated with a simple regular expression.

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).


Read his specs again. All he asked, but it is easy to generalize:

CREATE TABLE Gradebook
(student_id INTEGER NOT NULL,
school_year CHAR(10) NOT NULL
CHECK (school_year LIKE '[12][0-9][0-9][0-9]-00-00'),
PRIMARY KEY (student_id, school_year),
course_1 CHAR(4) NOT NULL CHECK (course_1 IN ('pass', 'fail')),
course_2 CHAR(4) NOT NULL CHECK (course_2 IN ('pass', 'fail')),
course_3 CHAR(4) NOT NULL CHECK (course_3 IN ('pass', 'fail'))
);

INSERT INTO Gradebook
VALUES (1, '2011-00-00', 'pass', 'fail', 'fail'),
(1, '2012-00-00', 'pass', 'fail', 'pass'),
(2, '2011-00-00', 'pass', 'pass', 'pass'),
(2, '2012-00-00', 'pass', 'fail', 'pass'),
(4, '2009-00-00', 'fail', 'fail', 'fail'),
(4, '2010-00-00', 'pass', 'fail', 'pass'),
(3, '2010-00-00', 'fail', 'fail', 'pass'),
(3, '2011-00-00', 'pass', 'fail', 'pass');

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');

2011-00-00 1
2010-00-00 3
2009-00-00 4



Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
Post #1372528
Posted Sunday, October 14, 2012 2:13 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Yesterday @ 6:30 PM
Points: 8,830, Visits: 9,388
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
Post #1372533
Posted Sunday, October 14, 2012 8:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 10:37 AM
Points: 14, Visits: 171
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


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

insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 1, 2011, 1, 'FAIL', 'FAIL', 'FAIL' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 1, 2011, 2, 'PASS', 'PASS', 'FAIL' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 1, 2012, 1, 'PASS','PASS', 'FAIL' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 1, 2012, 2, 'FAIL', 'PASS', 'FAIL' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 1, 2010, 1, 'PASS', 'PASS', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 1, 2010, 2, 'FAIL', 'PASS', 'PASS' );

insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 2, 2012, 1, 'FAIL', 'FAIL', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 2, 2012, 2, 'PASS', 'FAIL', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 2, 2011, 1, 'PASS','FAIL', 'FAIL' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 2, 2011, 2, 'FAIL', 'FAIL', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 2, 2010, 1, 'PASS', 'PASS', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 2, 2010, 2, 'FAIL', 'PASS', 'PASS' );

insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 3, 2010, 1, 'PASS', 'PASS', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 3, 2010, 2, 'PASS', 'FAIL', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 3, 2011, 1, 'PASS','PASS', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 3, 2011, 2, 'PASS', 'PASS', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 3, 2012, 1, 'FAIL', 'PASS', 'PASS' );
insert into StudStat (StudentID, Year, Semester, Status1, Status2, Status3)
values ( 3, 2012, 2, 'PASS', 'PASS', 'PASS' );

Post #1372552
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse