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


if 2nd year and same Programme for same Student, then return no results


if 2nd year and same Programme for same Student, then return no results

Author
Message
kevin_nikolai
kevin_nikolai
SSC Eights!
SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)

Group: General Forum Members
Points: 957 Visits: 523
/*
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 ?
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36824 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
kevin_nikolai
kevin_nikolai
SSC Eights!
SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)SSC Eights! (957 reputation)

Group: General Forum Members
Points: 957 Visits: 523
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.
drew.allen
drew.allen
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16041 Visits: 11224
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36824 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
drew.allen
drew.allen
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16041 Visits: 11224
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
How to post data/code on a forum to get the best help.
How to Post Performance Problems
Make sure that you include code in the appropriate IFCode tags, e.g. [code=sql]<your code here>[/code]. You can find the IFCode tags under the INSERT options when you are writing a post.
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36824 Visits: 11361
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
SQLblog.com
@SQL_Kiwi
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