October 15, 2012 at 11:02 am
I received some great suggestions, but didn't get quite what I wanted mainly because I didn't provide enough info.
Here's what I'm looking for
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' );
October 15, 2012 at 12:06 pm
Try it this way:
with bob as (
select studentid, year, semester,
'Status1' StatName,
status1 as StatValue
from studstat
union
select studentid, year, semester,
'Status2' StatName,
status2 as StatValue
from studstat
union
select studentid, year, semester,
'Status3' StatName,
status3 as StatValue
from studstat),
joe as (
select ROW_NUMBER() over (partition by studentID,StatName order by year, semester) RN,
* from Bob)
select joe1.studentID,joe1.StatName, joe1.year, joe1.semester ,joe1.RN
from joe as joe1
where exists
(select null from joe j2
where j2.StatValue='FAIL'
and j2.rn between joe1.RN and joe1.RN+3
and joe1.studentID=j2.studentid
and joe1.StatName=j2.StatName
group by j2.studentID,j2.StatName having count(*)=4)
I unpivoted the data first so that we don't have to run the criteria multiple times. The data returns which status failed 4 times in a row, and what year+semester the first failure occurred in.
This may not scale particularly well due to the self-joining.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply