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

if 2nd year and same Programme for same Student, then return no results Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2012 3:58 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:31 PM
Points: 161, Visits: 461
/*
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 ?
Post #1233619
Posted Tuesday, January 10, 2012 5:33 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1233656
Posted Wednesday, January 11, 2012 12:36 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 29, 2014 3:31 PM
Points: 161, Visits: 461
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.
Post #1234297
Posted Wednesday, January 11, 2012 1:33 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1234338
Posted Wednesday, January 11, 2012 1:54 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234354
Posted Wednesday, January 11, 2012 3:04 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 3, 2013 8:24 AM
Points: 1,240, Visits: 5,421
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
Post #1234414
Posted Wednesday, January 11, 2012 4:34 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Tuesday, October 28, 2014 9:36 PM
Points: 9,927, Visits: 11,189
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1234463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse