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

Student Multiple attendances Expand / Collapse
Author
Message
Posted Tuesday, September 18, 2012 2:53 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 1, 2012 1:57 AM
Points: 4, Visits: 17
Hi All,

new here so be kind....:)

I have a task to identify all students in a dataset of 49K that have multiple open attendances at schools.

I can pull all students that have multiple open attendances and produce a list of these:

SELECT studentID
FROM step1 [ step1 identifies all student with open attendances ]
GROUP BY studentID
HAVING (COUNT(*) > 1)

The help i need is how to then enable end user to specify a particular school and return all students with open attendance at that specified school AND show the other schools the student has a linked open attendance with.

Example:

studentID, schoolID
1 2
1 3
2 7
3 4
4 5

user selects schoolID of 3, then returned set would show:

schoolId, studentId
3 1
2 1


Thanks very much for any help that can be passed my way!
Post #1360630
Posted Tuesday, September 18, 2012 3:23 AM


Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Monday, July 14, 2014 7:25 AM
Points: 539, Visits: 876
skizzly (9/18/2012)
Hi All,

new here so be kind....:)

I have a task to identify all students in a dataset of 49K that have multiple open attendances at schools.

I can pull all students that have multiple open attendances and produce a list of these:

SELECT studentID
FROM step1 [ step1 identifies all student with open attendances ]
GROUP BY studentID
HAVING (COUNT(*) > 1)

The help i need is how to then enable end user to specify a particular school and return all students with open attendance at that specified school AND show the other schools the student has a linked open attendance with.

Example:

studentID, schoolID
1 2
1 3
2 3
3 4
4 5

user selects schoolID of 3, then returned set would show:

schoolId, studentId
3 1
2 1


Thanks very much for any help that can be passed my way!


Your output is confusing.For school id 3 there are two students.


--rhythmk
------------------------------------------------------------------
To post your question use below link

http://www.sqlservercentral.com/articles/Best+Practices/61537/
Post #1360640
Posted Tuesday, September 18, 2012 3:28 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 1, 2012 1:57 AM
Points: 4, Visits: 17
Hi,

thanks for looking, there is one student, with ID of one(1) attending two schoolIDs (3 and 2)

Post #1360643
Posted Tuesday, September 18, 2012 3:46 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 2,422, Visits: 7,435
skizzly (9/18/2012)
Hi All,

new here so be kind....:)

I have a task to identify all students in a dataset of 49K that have multiple open attendances at schools.

I can pull all students that have multiple open attendances and produce a list of these:

SELECT studentID
FROM step1 [ step1 identifies all student with open attendances ]
GROUP BY studentID
HAVING (COUNT(*) > 1)

The help i need is how to then enable end user to specify a particular school and return all students with open attendance at that specified school AND show the other schools the student has a linked open attendance with.

Example:

studentID, schoolID
1 2
1 3
2 3
3 4
4 5

user selects schoolID of 3, then returned set would show:

schoolId, studentId
3 1
2 1


Thanks very much for any help that can be passed my way!


Here's the correct way to lay out sample data: -
CREATE TABLE #student_attendance (student_attendance_id INT IDENTITY(1,1), student_id INT, school_ID INT);

INSERT INTO #student_attendance(student_id, school_ID)
SELECT student_id, school_ID
FROM (VALUES(1,2),(1,3),(2,3),(3,4),(4,5))a(student_id, school_ID);

Really, you should have set it up like that for us, so that anyone wanting to help you can copy it straight into SSMS then execute and have a replica of your table. This helps you to get tested and working code from anyone wanting to help.

OK, using the sample data above, here is one way to solve the question asked: -
DECLARE @user_input INT = 3;

SELECT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
FROM #student_attendance
WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
FROM #student_attendance
WHERE student_id = school_filter.student_id
AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
UNION SELECT other_schools.student_id, other_schools.school_ID
WHERE other_schools.student_id IS NOT NULL) final_result_set;

Results in: -
student_id  school_ID
----------- -----------
1 2
1 3
2 3



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1360654
Posted Tuesday, September 18, 2012 4:05 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 1, 2012 1:57 AM
Points: 4, Visits: 17
thanks Cadavre,

note taken on guidelines for posting :)

Very new to SQL and never used the APPLY operator. So learning hard and fast, or least trying to. Only one other thing is the user input will be by parameter used in reporting services, will your solution change with this added detail?

thanks for quick response

Post #1360657
Posted Tuesday, September 18, 2012 4:13 AM
SSC Journeyman

SSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC JourneymanSSC Journeyman

Group: General Forum Members
Last Login: Thursday, October 3, 2013 12:38 AM
Points: 91, Visits: 77
declare @var1 int
set @var1=3
select schid, stuid from stu where stuid in (select stuid from (select * from stu where schid=@var1) a )


where (select * from stu) is your resultset query

SELECT studentID
FROM step1 [ step1 identifies all student with open attendances ]
GROUP BY studentID
HAVING (COUNT(*) > 1)


*******
Sudhakar
Post #1360659
Posted Tuesday, September 18, 2012 4:56 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:29 AM
Points: 2,422, Visits: 7,435
skizzly (9/18/2012)
thanks Cadavre,

note taken on guidelines for posting :)

Very new to SQL and never used the APPLY operator. So learning hard and fast, or least trying to. Only one other thing is the user input will be by parameter used in reporting services, will your solution change with this added detail?

thanks for quick response



No. Obviously you need to grab @user_input from the parameter of the report instead of in the way I did it.

Also, I'd test both my solution and Sudhakar's solution. I'm not honestly certain which would be faster.

OUTER APPLY
SELECT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
FROM #student_attendance
WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
FROM #student_attendance
WHERE student_id = school_filter.student_id
AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
UNION SELECT other_schools.student_id, other_schools.school_ID
WHERE other_schools.student_id IS NOT NULL) final_result_set;

IN SUBQUERY
SELECT student_id, school_ID
FROM #student_attendance
WHERE student_id IN (SELECT student_id
FROM #student_attendance
WHERE school_ID = @user_input
);

Statistics: -
=================================
OUTER APPLY
=================================

Table '#student_attendance'. Scan count 2, logical reads 3, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.

=================================
IN SUBQUERY
=================================

Table '#student_attendance'. Scan count 2, logical reads 6, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 0 ms, elapsed time = 0 ms.


Obviously, on the tiny sample data we have the results are pretty much the same.

Now, I don't know what your actual data looks like, so this might be totally out of whack. But here's some sample data with 1,000,000 rows.

IF object_id('tempdb..#student_attendance') IS NOT NULL
BEGIN
DROP TABLE #student_attendance;
END;

SELECT TOP 1000000 IDENTITY(INT,1,1) AS student_attendance_id,
(ABS(CHECKSUM(NEWID())) % 500000) + 1 AS student_id,
(ABS(CHECKSUM(NEWID())) % 50000) + 1 AS school_ID
INTO #student_attendance
FROM master.dbo.syscolumns sc1, master.dbo.syscolumns sc2, master.dbo.syscolumns sc3;

Now, if we run the two queries again we get the following: -
=================================
OUTER APPLY
=================================
(70 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 186 ms, elapsed time = 46 ms.

=================================
IN SUBQUERY
=================================
(50 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 125 ms, elapsed time = 34 ms.


Roughly the same over a million rows, with the "IN Subquery" query winning. Please note however, that the "IN Subquery" method produced 50 rows whilst the "OUTER APPLY" produced 70 rows. This is an error in my code. If we correct this error: -
SELECT DISTINCT final_result_set.student_id, final_result_set.school_ID
FROM (SELECT student_id, school_ID
FROM #student_attendance
WHERE school_ID = @user_input) school_filter(student_id, school_ID)
OUTER APPLY (SELECT student_id, school_ID
FROM #student_attendance
WHERE student_id = school_filter.student_id
AND school_ID <> school_filter.school_ID) other_schools(student_id, school_ID)
OUTER APPLY (SELECT school_filter.student_id, school_filter.school_ID
UNION SELECT other_schools.student_id, other_schools.school_ID
WHERE other_schools.student_id IS NOT NULL) final_result_set;

Then try again over 1 million rows: -
=================================
OUTER APPLY
=================================
(40 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 2, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 140 ms, elapsed time = 36 ms.

=================================
IN SUBQUERY
=================================
(40 row(s) affected)
Table '#student_attendance'. Scan count 10, logical reads 5216, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SQL Server Execution Times:
CPU time = 173 ms, elapsed time = 35 ms.


Each now appears to be identical in both run-time and result-set. Without knowing how your indexes are set on the table, that is the best we're likely to be able to do with helping you.



Not a DBA, just trying to learn

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better, quicker answers on SQL Server performance related questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #1360681
Posted Tuesday, September 18, 2012 5:14 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Monday, October 1, 2012 1:57 AM
Points: 4, Visits: 17
BIG THANKS to

Cadavre
Sudhakar Vallamsetty

with your help got the output wanted and started a new line of learning!

To anyone interested I went with the sub query method, but a massive shout to Cadavre for showing me a new way of doing things well explained!

Really appreciate the time guys

Post #1360690
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse