January 10, 2012 at 3:58 pm
/*
Objective:
if report is for Year 2009, and 2009 is the first registration year, then return Programme for student.
if report is for Year 2010, and 2010 is the second registration year (based on same Programme as 2009), then dont return Programme for student. Same applies to 2011 & 2012 as for 2010.
So report always checks for previous year/s, thereby evaluates if 1st or 2nd or 3rd year, etc.
If 1st year, then there will only be one Programme for Student, so return results.
If NOT 1st year and same Programme for same Student, then return no results.
If NOT 1st year and different Programme for same Student, then return results.
For testing with below code, replace 2009 with 2010,2011,2012 in
WHERE [Year] = 2009
*/
Method1:
--------
create table #Students (
StudentID INT,
Programme varchar(20),
[Year] INT
);
create table #Students2 (
StudentID INT,
Programme varchar(20),
[Year] INT,
[rank] INT
);
insert into #Students
select 61037,'PROGCS',2009
union all
select 61037,'PROGCS',2010
union all
select 61037,'PROGCS',2011
union all
select 61037,'PROGCS',2012
;
insert into #Students2
SELECT StudentID, Programme, [Year], rank =
(
SELECT COUNT(*)
FROM #Students t2
WHERE t2.StudentID = t1.StudentID
AND t2.Programme = t1.Programme
AND t2.[Year] <= t1.[Year]
)
FROM #Students t1
ORDER BY StudentID, Programme, [Year]
SELECT [Year], Programme, StudentID--, rank
FROM #Students2
WHERE [Year] = 2009
AND RANK = (SELECT MIN(RANK) FROM #Students2)
DROP TABLE #Students
DROP TABLE #Students2
-------------------------------
-------------------------------
Method2:
--------
create table #Students (
StudentID INT,
Programme varchar(20),
[Year] INT
);
insert into #Students
select 61037,'PROGCS',2009
union ALL
select 61037,'PROGCS',2010
union ALL
select 61037,'PROGCS',2011
union ALL
select 61037,'PROGCS',2012
;
SELECT [Year], Programme, StudentID --, RowNumber
FROM
(SELECT [Year], Programme, StudentID,
ROW_NUMBER() OVER (ORDER BY Programme) RowNumber
FROM #Students) p
WHERE [Year] = 2009
AND p.RowNumber = 1
--WHERE p.RowNumber BETWEEN 1 AND 4
DROP TABLE #Students
-------------------------------
-------------------------------
Does anyone else have a different approach ?
January 10, 2012 at 5:33 pm
Isn't it as simple as:
SELECT
*
FROM #Students AS s
WHERE
s.[Year] = 2009
AND NOT EXISTS
(
SELECT *
FROM #Students AS s2
WHERE
s2.StudentID = s2.StudentID
AND s2.Programme = s.Programme
AND s2.[Year] = s.[Year] - 1
);
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 12:36 pm
Hi Paul, thanks for your quick response. Must admit, your method is much simpler. (wish I had your SQL knowledge).
The query is used as a filter - to remove all students who already registered the year before and now registered again for the same Programme. I apply the filter to another query.
January 11, 2012 at 1:33 pm
I think it's actually even simpler.
SELECT StudentID, Programme, Min([Year]) AS [Year]
FROM #Students
GROUP BY StudentID, Programme
HAVING Min([Year]) = 2009
You may not even need the HAVING clause depending on what you are looking for.
ROW_NUMBER() is useful if you want to return fields that are neither used for the grouping nor for determining the Top/Bottom/First/Last record. That's not the situation here based solely on your sample data. You're grouping on two of the fields and using the remaining one for determining the first record, so you can use a simple GROUP BY.
This has the added benefit that it only scans the table once instead of twice.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 11, 2012 at 1:54 pm
drew.allen (1/11/2012)
I think it's actually even simpler.
That has a different semantic though. Example:
CREATE TABLE #Students
(
StudentID integer,
Programme varchar(20),
[Year] integer
);
INSERT #Students
(StudentID, Programme, [Year])
VALUES
(1, 'P1', 2000),
(1, 'P2', 2001),
(1, 'P1', 2002),
(1, 'P3', 2003);
SELECT
*
FROM #Students AS s
WHERE
s.[Year] = 2002
AND NOT EXISTS
(
SELECT *
FROM #Students AS s2
WHERE
s2.StudentID = s2.StudentID
AND s2.Programme = s.Programme
AND s2.[Year] = s.[Year] - 1
);
SELECT StudentID, Programme, Min([Year]) AS [Year]
FROM #Students
GROUP BY StudentID, Programme
HAVING Min([Year]) = 2002
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
January 11, 2012 at 3:04 pm
drew.allen (1/11/2012)
I think it's actually even simpler.
SQL Kiwi (1/11/2012)
That has a different semantic though. Example:
That's true, but this statement from the original poster sounds like the GROUP BY is the correct version.
kevin_nikolai (1/10/2012)
If NOT 1st year and same Programme for same Student, then return no results.
He say 1st year, not 1st consecutive year. Of course, that could simply be a result of poor wording on the part of the OP.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
January 11, 2012 at 4:34 pm
drew.allen (1/11/2012)
That's true, but this statement from the original poster sounds like the GROUP BY is the correct version.kevin_nikolai (1/10/2012)
If NOT 1st year and same Programme for same Student, then return no results.He say 1st year, not 1st consecutive year. Of course, that could simply be a result of poor wording on the part of the OP.
My point was just that a student might have more than one first year in the same subject (or programme), and the two approaches would produce different results in that case. The GROUP BY/HAVING does also have the drawback of requiring a scan, whereas the NOT EXISTS one could use a seek, of course.
Paul White
SQLPerformance.com
SQLkiwi blog
@SQL_Kiwi
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply