September 18, 2012 at 2:53 am
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!
September 18, 2012 at 3:23 am
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
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
September 18, 2012 at 3:28 am
Hi,
thanks for looking, there is one student, with ID of one(1) attending two schoolIDs (3 and 2)
September 18, 2012 at 3:46 am
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
September 18, 2012 at 4:05 am
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
September 18, 2012 at 4:13 am
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
September 18, 2012 at 4:56 am
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.
September 18, 2012 at 5:14 am
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:-):-)
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply