Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
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: Thursday, August 25, 2016 3:03 PM
Points: 181, Visits: 510
/*
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: Friday, July 22, 2016 6:18 PM
Points: 9,932, Visits: 11,342
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
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: Thursday, August 25, 2016 3:03 PM
Points: 181, Visits: 510
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


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 2,063, Visits: 8,487
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.
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 on the left when you are writing a post.
How to Post Performance Problems
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: Friday, July 22, 2016 6:18 PM
Points: 9,932, Visits: 11,342
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
Post #1234354
Posted Wednesday, January 11, 2012 3:04 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: 2 days ago @ 1:53 PM
Points: 2,063, Visits: 8,487
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.
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 on the left when you are writing a post.
How to Post Performance Problems
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: Friday, July 22, 2016 6:18 PM
Points: 9,932, Visits: 11,342
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
Post #1234463
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse