April 4, 2012 at 10:46 am
Hello - I need some help in writing a SQL Query for students who are absent for consecutive days for each course. My mind is jammed at this point, any help is appreciated
Problem Domain: The query need to pull student names and no of consecutive days they have been absent for each course.
Parameter: Should be executable by a given attendance_date
Comments: Only need students with atleast 1 absent.
Expected Results: Executed on 2012/01/30 -- The results I expect are
Name: CourseCode : # of Days Absent consecutively
Student 3, SOF-550, 1
Student 1, SOF-550, 2
Student 4, SOF-200, 1
Expected Results: Executed on 2012/01/26 -- The results I expect are
Name: CourseCode : # of Days Absent consecutively
Student 4, SOF-200, 1
Student 1, SOF-550, 1
DECLARE @tblStudents TABLE (StudentID varchar(50), Name varchar(50),Attendance_Date datetime, Attendance varchar(10), CourseCode varchar(10));
insert into @tblStudents VALUES
('S0001','Student 1','2012-01-15 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0003','Student 3','2012-01-15 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0004','Student 4','2012-01-15 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0002','Student 2','2012-01-15 10:00:00','ABSENT','SOF-550');
insert into @tblStudents VALUES
('S0001','Student 1','2012-01-21 10:00:00','ABSENT','SOF-550');
insert into @tblStudents VALUES
('S0002','Student 2','2012-01-21 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0003','Student 3','2012-01-21 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0004','Student 4','2012-01-21 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0003','Student 3','2012-01-25 10:00:00','PRESENT','SOF-200');
insert into @tblStudents VALUES
('S0004','Student 4','2012-01-25 10:00:00','ABSENT','SOF-200');
insert into @tblStudents VALUES
('S0001','Student 1','2012-01-28 10:00:00','ABSENT','SOF-550');
insert into @tblStudents VALUES
('S0002','Student 2','2012-01-28 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0004','Student 4','2012-01-28 10:00:00','PRESENT','SOF-550');
insert into @tblStudents VALUES
('S0003','Student 3','2012-01-28 10:00:00','ABSENT','SOF-550');
April 4, 2012 at 11:00 am
I appreciate the question as it stands,
but how in the real world do you want to handle absences on a Friday and again on a Monday - are they consecutive?
Last day of one term and the first day of next ?
Any code that we can help you out with here may work on the sample you provided but not when you run it against live data.
This sounds like an ideal place to use a tally table (this link would be a great place to start http://www.sqlservercentral.com/articles/Tally+Table/70735/), be sure to read Jeff's article as well.
-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx
April 4, 2012 at 11:07 am
Stuart Davies (4/4/2012)
I appreciate the question as it stands,but how in the real world do you want to handle absences on a Friday and again on a Monday - are they consecutive?
Last day of one term and the first day of next ?
Any code that we can help you out with here may work on the sample you provided but not when you run it against live data.
This sounds like an ideal place to use a tally table (this link would be a great place to start http://www.sqlservercentral.com/articles/Tally+Table/70735/), be sure to read Jeff's article as well.
Yep, this is a sample data but its 99% of what I need for my actual SSRS report. So yes any help will still help move my jammed mind.
For your question on Friday, Monday - This report is for a school, and so students have classes on set schedule. So a course will have class on Friday always. AND as a course's scheduled class occur student is marked Present or Absent for that course. So, if the report is ran on say Wednesday and student was absent in last Friday's class he should show up in the report.
April 4, 2012 at 11:18 am
One, for what time period is the report run? Past 7 days, since the beginning of the term, etc.
When one sees consecutive days, one is usually refering to more than one day in a row. From your last post, it looks like you are asking for how many days a student was absent for some unspecified period regardless if it was consecutive days.
April 4, 2012 at 11:33 am
Lynn Pettis (4/4/2012)
One, for what time period is the report run? Past 7 days, since the beginning of the term, etc.When one sees consecutive days, one is usually refering to more than one day in a row. From your last post, it looks like you are asking for how many days a student was absent for some unspecified period regardless if it was consecutive days.
Lynn,
this is how my actual table looks like. And yes the report will be executed for "since the beginning of the term".
I think you are right, I possibly confused my original question with consecutive bit. I want to know for how many days a student was absent from from a specified date. So if he was not present in last 4 lectures he should show up in the report but if a student has not taken lecture 1, lecture 2, lecture 3, but has taken the last lecture (4th) he should NOT show up in the report. Does this makes sense?
CREATE TABLE [dbo].[TRANATTENDANCE](
[PEOPLE_CODE_ID] [varchar](10) NOT NULL,
[ACADEMIC_YEAR] [varchar](4) NOT NULL,
[ACADEMIC_TERM] [varchar](10) NOT NULL,
[COURSE_CODE] [varchar](15) NOT NULL,
[ATTENDANCE_DATE] [datetime] NOT NULL,
[ATTENDANCE_STATUS] [varchar](10) NOT NULL,
[TranAttendanceId] [int] IDENTITY(1,1) NOT NULL);
Do you want me to fill up some INSERT statements for this table?
April 4, 2012 at 12:13 pm
aptlogix (4/4/2012)
Lynn Pettis (4/4/2012)
One, for what time period is the report run? Past 7 days, since the beginning of the term, etc.When one sees consecutive days, one is usually refering to more than one day in a row. From your last post, it looks like you are asking for how many days a student was absent for some unspecified period regardless if it was consecutive days.
Lynn,
this is how my actual table looks like. And yes the report will be executed for "since the beginning of the term".
I think you are right, I possibly confused my original question with consecutive bit. I want to know for how many days a student was absent from from a specified date. So if he was not present in last 4 lectures he should show up in the report but if a student has not taken lecture 1, lecture 2, lecture 3, but has taken the last lecture (4th) he should NOT show up in the report. Does this makes sense?
CREATE TABLE [dbo].[TRANATTENDANCE](
[PEOPLE_CODE_ID] [varchar](10) NOT NULL,
[ACADEMIC_YEAR] [varchar](4) NOT NULL,
[ACADEMIC_TERM] [varchar](10) NOT NULL,
[COURSE_CODE] [varchar](15) NOT NULL,
[ATTENDANCE_DATE] [datetime] NOT NULL,
[ATTENDANCE_STATUS] [varchar](10) NOT NULL,
[TranAttendanceId] [int] IDENTITY(1,1) NOT NULL);
Do you want me to fill up some INSERT statements for this table?
One, what determines the start date for the report?
Two, If I take Lecture 1 and lecture 4 and miss Lecture 2 and 3 I wouldn't show up has having missed any classes since some predetermined point in time?
April 4, 2012 at 1:02 pm
Lynn Pettis (4/4/2012)
aptlogix (4/4/2012)
Lynn Pettis (4/4/2012)
One, for what time period is the report run? Past 7 days, since the beginning of the term, etc.When one sees consecutive days, one is usually refering to more than one day in a row. From your last post, it looks like you are asking for how many days a student was absent for some unspecified period regardless if it was consecutive days.
Lynn,
this is how my actual table looks like. And yes the report will be executed for "since the beginning of the term".
I think you are right, I possibly confused my original question with consecutive bit. I want to know for how many days a student was absent from from a specified date. So if he was not present in last 4 lectures he should show up in the report but if a student has not taken lecture 1, lecture 2, lecture 3, but has taken the last lecture (4th) he should NOT show up in the report. Does this makes sense?
CREATE TABLE [dbo].[TRANATTENDANCE](
[PEOPLE_CODE_ID] [varchar](10) NOT NULL,
[ACADEMIC_YEAR] [varchar](4) NOT NULL,
[ACADEMIC_TERM] [varchar](10) NOT NULL,
[COURSE_CODE] [varchar](15) NOT NULL,
[ATTENDANCE_DATE] [datetime] NOT NULL,
[ATTENDANCE_STATUS] [varchar](10) NOT NULL,
[TranAttendanceId] [int] IDENTITY(1,1) NOT NULL);
Do you want me to fill up some INSERT statements for this table?
One, what determines the start date for the report?
Two, If I take Lecture 1 and lecture 4 and miss Lecture 2 and 3 I wouldn't show up has having missed any classes since some predetermined point in time?
I am sorry for confusing it all - If we do not consider the date part of this all. the plain english version is
"When user runs the report, the logic should run from today to the start of the term and list down any students who have missed recent classes and their count"
so
if a student takes lecture 1,4 and misses lecture 2,3 -- he will NOT show up as he attended the most recent lecture (4th)
if a student takes lecture 1,2 and misses 3,4 --- he will show up in the report as "Alice Bob, SOF-550, 2" -- (the number 2 here is that he has missed most recent two classes )
does this help?
April 4, 2012 at 1:25 pm
Let me rephrase the question as to how I understand it and it might help.
Basically, what you're looking to do is take the existing list, re-order it by student, and go through each student's history to the last 'PRESENT' they had and then count how many 'ABSENT's existed between that and the end of the recordset, as ordered by date for that student?
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2012 at 1:32 pm
Evil Kraig F (4/4/2012)
Let me rephrase the question as to how I understand it and it might help.Basically, what you're looking to do is take the existing list, re-order it by student, and go through each student's history to the last 'PRESENT' they had and then count how many 'ABSENT's existed between that and the end of the recordset, as ordered by date for that student?
EXACTLY!!!! That's what I want
April 4, 2012 at 2:14 pm
Like so?
DECLARE @RunForDate DATETIME
--SET @RunForDate = '20120126'
SET @RunForDate = GETDATE()
;WITH LastPresent AS
(SELECT
StudentID,
MAX( Attendance_Date) AS MaxDate
FROM
@tblStudents
WHERE
Attendance = 'PRESENT'
AND Attendance_Date <= @RunForDate
GROUP BY
StudentID
)
SELECT
lp.StudentID,
COUNT(*) AS NumAbsents
FROM
LastPresent AS lp
JOIN
@tblStudents AS s
ONlp.StudentID = s.StudentID
WHERE
s.Attendance_Date > lp.MaxDate
AND s.Attendance_Date <= @RunForDate
GROUP BY
lp.StudentID
Please note, Student 4 had a last attendance of Present, so he doesn't come up in the 'current' call.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2012 at 2:33 pm
Please note, Student 4 had a last attendance of Present, so he doesn't come up in the 'current' call.
Thank you - it looks good and looks to meet what I said above, but I think I missed one bit when I confirmed the understanding
"Basically, what you're looking to do is take the existing list, re-order it by student, and go through each student's history to the last 'PRESENT' of "EACH COURSE" they had and then count how many 'ABSENT's existed between that and the end of the recordset, as ordered by date for that student?
So Ideally - when you run the query - the output should have
S0001 - 2 - SOF-550
S0003 - 1 - SOF-550
S0004 - 1 - SOF-200 <-- This guy was PRESENT in the last lecture of SOF-550 but was absent in SOF-200's last lecture - so he should show up with that course -
I am going to play with your query, but if you can suggest something meanwhile - that will help alot.
April 4, 2012 at 2:59 pm
aptlogix (4/4/2012)
S0001 - 2 - SOF-550
S0003 - 1 - SOF-550
S0004 - 1 - SOF-200 <-- This guy was PRESENT in the last lecture of SOF-550 but was absent in SOF-200's last lecture - so he should show up with that course -
I am going to play with your query, but if you can suggest something meanwhile - that will help alot.
Ah, so by student, by course? Just adjust the grouping and linking mechanisms to be both Student ID and Course ID, like so:
DECLARE @RunForDate DATETIME
--SET @RunForDate = '20120126'
SET @RunForDate = GETDATE()
;WITH LastPresent AS
(SELECT
StudentID,
CourseCode,
MAX( Attendance_Date) AS MaxDate
FROM
@tblStudents
WHERE
Attendance = 'PRESENT'
AND Attendance_Date <= @RunForDate
GROUP BY
StudentID,
CourseCode
)
SELECT
lp.StudentID,
lp.CourseCode,
COUNT(*) AS NumAbsents
FROM
LastPresent AS lp
JOIN
@tblStudents AS s
ONlp.StudentID = s.StudentID
AND lp.CourseCode = s.CourseCode
WHERE
s.Attendance_Date > lp.MaxDate
AND s.Attendance_Date <= @RunForDate
GROUP BY
lp.StudentID,
lp.CourseCode
However, one problem.
1) Sample data never shows that student actually attending SOF200, so he never gets 'absented' from it.
Really, what we want here is a calendar table showing the meet dates of the different classes and the like, and a course-enrolled table for each student. We're deriving a lot of entity information off this one table. It can be done but it's not the best choice, but I'd have to poke at your full schema to really tell you a better option.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 4, 2012 at 3:03 pm
Ah, thank you for all this. I was able to modify it for groups but It still does not give the correct output -
the sample data i posted in my original post, has the 'absent' record and should 'show up' in this result set
insert into @tblStudents VALUES
('S0003','Student 3','2012-01-25 10:00:00','PRESENT','SOF-200');
insert into @tblStudents VALUES
('S0004','Student 4','2012-01-25 10:00:00','ABSENT','SOF-200');
April 4, 2012 at 3:27 pm
Allrite - I think, I have resolved it- I have written quite a long stored procedure to resolve it.
I am sure its not the best way to do it - but I think this will work for now! - if anyone here has suggestions please feel free to reply.
My solution is
1- Pull records in reverse order into a temp table
2- iterate the table row by row and check the status of attendance based on coruse code and update a second temp table with counts
3- eventually pull all records from second temp table having the count.
It just does not look nicer but seems to be working.
Thank you everyone!
April 4, 2012 at 6:02 pm
If it works and you understand it, roll with it. If you'd like further help and have code that does exactly what you need it to do, post it and we can probably help you clean it up further.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply