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


Student Multiple attendances


Student Multiple attendances

Author
Message
skizzly
skizzly
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
Hi All,

new here so be kind....Smile

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!
rhythmk
rhythmk
SSC Eights!
SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)SSC Eights! (912 reputation)

Group: General Forum Members
Points: 912 Visits: 1078
skizzly (9/18/2012)
Hi All,

new here so be kind....Smile

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/
:-)
skizzly
skizzly
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
Hi,

thanks for looking, there is one student, with ID of one(1) attending two schoolIDs (3 and 2)
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3900 Visits: 8472
skizzly (9/18/2012)
Hi All,

new here so be kind....Smile

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



Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
skizzly
skizzly
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
thanks Cadavre,

note taken on guidelines for posting Smile

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
Sudhakar Vallamsetty
Sudhakar Vallamsetty
SSC Journeyman
SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)SSC Journeyman (95 reputation)

Group: General Forum Members
Points: 95 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)

*******Cool
Sudhakar
Cadavre
Cadavre
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3900 Visits: 8472
skizzly (9/18/2012)
thanks Cadavre,

note taken on guidelines for posting Smile

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.


Forever 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


Craig Wilkinson - Software Engineer
LinkedIn
skizzly
skizzly
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 36
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

:-D:-D:-D:-):-)
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