SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


select rows where values appear consecutively


select rows where values appear consecutively

Author
Message
jon.wilson
jon.wilson
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 359
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
laurie-789651
laurie-789651
UDP Broadcaster
UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)UDP Broadcaster (1.4K reputation)

Group: General Forum Members
Points: 1436 Visits: 1272
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
Jason-299789
SSCarpal Tunnel
SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)SSCarpal Tunnel (4.9K reputation)

Group: General Forum Members
Points: 4933 Visits: 3232
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.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17573 Visits: 6431
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!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
jon.wilson
jon.wilson
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 359
Thanks, I think I have what I need now
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25823 Visits: 12494
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>Wink 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

Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25823 Visits: 12494
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

jon.wilson
jon.wilson
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 359
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' );
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search