October 24, 2016 at 2:27 am
Hi, I have the following data with me:
CREATE TABLE Table1 (ID varchar(10),StudentName varchar(30), Course varchar(15) ,SECTION varchar(2),DAY varchar(10),
START_TIME time , END_TIME time, actual_starttime time, actual_endtime time);
INSERT INTO Table1 VALUES (111, 'Mary','Science','A','Mon','13:30:00.0000000',16:20:00.0000000','09:00:00.0000000','21:20:00.0000000')
INSERT INTO Table1 VALUES (111, 'Mary','Maths','A','Tue','12:30:00.0000000',13:20:00.0000000','09:00:00.0000000','21:20:00.0000000')
INSERT INTO Table1 VALUES (111, 'Mary','Physics','C','Tue','10:30:00.0000000',11:10:00.0000000','09:00:00.0000000','21:20:00.0000000')
INSERT INTO Table1 VALUES (112, 'Robert','Maths','A','Mon','13:30:00.0000000',16:20:00.0000000','09:00:00.0000000','21:20:00.0000000')
The scenario is as follows : The student can have class from morning 9 to night 9:30 from Monday to Friday. My requirement is I have to identify a timeslot where all the students in the same section are free so that a teacher
can reshdule a class. Example: both mary and robert are free since morning 9:00 till 1:30 afternoon on Monday. I would like to write query for this.
Please help.
October 24, 2016 at 4:22 am
Please let me know if you need more information .
Thanks in advance
October 24, 2016 at 4:36 am
Firstly, your INSERT statement is malformed. let me fix that for you:
CREATE TABLE StudentCourse (ID VARCHAR(10), --Is there any reason why this is not an INT value? It seems that you're only inserting numerics, so no need for it to be a VARCHAR.
StudentName VARCHAR(30),
Course VARCHAR(15),
SECTION VARCHAR(2),
[DAY] VARCHAR(10),
START_TIME TIME,
END_TIME TIME,
actual_starttime TIME,
actual_endtime TIME);
INSERT INTO StudentCourse
VALUES (111, 'Mary','Science','A','Mon','13:30:00.0000000','16:20:00.0000000','09:00:00.0000000','21:20:00.0000000'),
(111, 'Mary','Maths','A','Tue','12:30:00.0000000','13:20:00.0000000','09:00:00.0000000','21:20:00.0000000'),
(111, 'Mary','Physics','C','Tue','10:30:00.0000000','11:10:00.0000000','09:00:00.0000000','21:20:00.0000000'),
(112, 'Robert','Maths','A','Mon','13:30:00.0000000','16:20:00.0000000','09:00:00.0000000','21:20:00.0000000');
DROP TABLE StudentCourse;
I was thinking of an idea, however, Mary seems to belong to two sections, which confuses matters. You state you only want data returned when all students belong to the same section, so how does someone belong to 2? Could this mean that they could have 2 possible changes, where all of C are free, and all of A are free?
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2016 at 4:45 am
Yes, Id is just numeric sorry about that.
And yes I want the students who are free in the same section with timings. Ex: all students who are free in section A on Monday,Tuesday,... with timings and all the students who are free in section C on Monday,Tuesday,... with timings.
Thank you!
October 24, 2016 at 4:55 am
Please do not cross post.
This question was posted on SQL 2008 and SQL 2012 boards.
What version of SQL are you using, as it may affect the solutions that are proposed.
October 24, 2016 at 4:58 am
sorry. I didnt know where to post earlier . Im using SQL SERVER 2012. Will deleete my post in sql server 2008. Howerver didnt get proper reply in sql 2008 post
October 24, 2016 at 5:19 am
Is this the actual format your data is in, or do you have further tables. For something such as this, I'd be hoping for a TimeSlot table, Student Table, and Sections table (with some information on what students belong to what sections, seeing as it's a many to many relationship).
Timeslot table is almost a requirement here, as without it, when do you know when the timeslots are if they're not in use?
Do you have DDLs, and sample data, with a more conclusive data representation?
On the topic of the other post, they are correct though, a calendar table is going to be your daving grace here. if you don't have a timeslot data (which would be your calendar table), this task is almost impossible.
Thom~
Excuse my typos and sometimes awful grammar. My fingers work faster than my brain does.
Larnu.uk
October 24, 2016 at 11:33 am
This is known as the packing intervals problem. Itzik Ben-Gan wrote up a New Solution to the Packing Intervals Problem. You're going to need to pack the intervals for each student and then pack the results for each section. One of the issues that you're going to face is that it looks like there is a small gap between the end of one session and the start of another (presumably to allow people time to get from one class to another if they have two classes in a row), and you probably don't want to include that gap in the available times.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
October 25, 2016 at 12:35 pm
drew.allen (10/24/2016)
This is known as the packing intervals problem. Itzik Ben-Gan wrote up a New Solution to the Packing Intervals Problem. You're going to need to pack the intervals for each student and then pack the results for each section. One of the issues that you're going to face is that it looks like there is a small gap between the end of one session and the start of another (presumably to allow people time to get from one class to another if they have two classes in a row), and you probably don't want to include that gap in the available times.Drew
The OP has had a day to stew over Drew's answer, with no feedback yet.
This question had my head in knots, so I would like to post what I came up with, based on the link to Itzik's code.
Perhaps there is a better way.
Using the corrected setup code from ThomA
CREATE TABLE StudentCourse (ID VARCHAR(10), --Is there any reason why this is not an INT value? It seems that you're only inserting numerics, so no need for it to be a VARCHAR.
StudentName VARCHAR(30),
Course VARCHAR(15),
SECTION VARCHAR(2),
[DAY] VARCHAR(10),
START_TIME TIME,
END_TIME TIME,
actual_starttime TIME,
actual_endtime TIME);
INSERT INTO StudentCourse
VALUES (111, 'Mary','Science','A','Mon','13:30:00.0000000','16:20:00.0000000','09:00:00.0000000','21:20:00.0000000'),
(111, 'Mary','Maths','A','Tue','12:30:00.0000000','13:20:00.0000000','09:00:00.0000000','21:20:00.0000000'),
(111, 'Mary','Physics','C','Tue','10:30:00.0000000','11:10:00.0000000','09:00:00.0000000','21:20:00.0000000'),
(112, 'Robert','Maths','A','Mon','13:30:00.0000000','16:20:00.0000000','09:00:00.0000000','21:20:00.0000000');
Add supporting indexes
CREATE UNIQUE INDEX ux_start_end
ON dbo.StudentCourse([DAY], START_TIME, END_TIME, ID);
-- Making the assumption here that ID is the PK for Student, so StudentName is not required
CREATE NONCLUSTERED INDEX ix_course_student
ON dbo.StudentCourse(Course, ID);
My attempt at a solution. It requires the definition of the start and end of the day, as well as the duration of the movement time between classes, and the subject that is being rescheduled.
-- The start and end times for each day
DECLARE @Actual_Starttime TIME = '09:00:00';
DECLARE @Actual_Endtime TIME = '21:20:00';
-- The time allowed for students to move between classes
DECLARE @Student_Movement_Minutes INT = 10;
-- Course that needs to be rescheduled.
---This is used in 2 places in the first CTE to get a list of students.
-- Alter both those WHERE clauses if your criteria is not on [Course] only.
DECLARE @Course_To_Reschedule VARCHAR(15) = 'Maths';
WITH cteStudentSchedule AS (
-- First, Get a list of all the Students that are taking the course being re-scheduled.
-- Also ensure that we have a time slot that indicates the start and end of every day.
-- This is the ugly part of the execution plan, which is improved by ix_course_student.
SELECT stud.ID, dys.[Day], tim.START_TIME, tim.END_TIME
FROM dbo.StudentCourse AS stud
CROSS APPLY (VALUES ('Mon'), ('Tue'), ('Wed'), ('Thu'), ('Fri')) AS dys([Day])
CROSS APPLY (VALUES (@Actual_Starttime, @Actual_Starttime), (@Actual_Endtime, @Actual_Endtime)) AS tim(START_TIME, END_TIME)
WHERE Course = @Course_To_Reschedule
GROUP BY stud.ID, stud.StudentName, dys.[Day], tim.START_TIME, tim.END_TIME
-- And add the existing schedule data to that
UNION ALL
SELECT stud.ID, stud.[Day], stud.START_TIME, stud.END_TIME
FROM dbo.StudentCourse AS stud
WHERE EXISTS (SELECT 1 FROM dbo.StudentCourse AS sc
WHERE sc.ID = stud.ID
AND sc.Course = @Course_To_Reschedule)
)
, ctePrevSessionEnd AS (
-- Figure out the end of the previous session, so that we can see whether they overlap.
SELECT ss.ID, ss.[Day], ss.START_TIME, ss.END_TIME
, prevEnd = MAX(ss.END_TIME) OVER(PARTITION BY ss.[Day]
ORDER BY ss.START_TIME, ss.END_TIME, ss.ID
ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING)
FROM cteStudentSchedule AS ss
)
, cteSessionGroups AS (
-- Group overlapping sessions into contiguous blocks
SELECT pse.[Day], pse.START_TIME, pse.END_TIME
, grp = SUM(CASE WHEN pse.START_TIME <= pse.prevEnd THEN 0 ELSE 1 END)
OVER(PARTITION BY pse.[Day]
ORDER BY pse.START_TIME, pse.END_TIME, pse.ID
ROWS UNBOUNDED PRECEDING)
FROM ctePrevSessionEnd AS pse
)
, cteNotAvailable AS (
-- Now we can see all of the unavailable times for each day, so that we can determine what is availiable.
SELECT [Day]
, START_TIME = MIN(START_TIME)
, END_TIME = MAX(END_TIME)
-- Get the start of the next session after this one
, nxtStart = LEAD(MIN(START_TIME)) OVER(PARTITION BY [Day]
ORDER BY MIN(START_TIME))
FROM cteSessionGroups
GROUP BY [Day], grp
)
-- Finally, get the time slots that are not used.
SELECT [Day]
, GAP_START_TIME = CASE WHEN END_TIME <= @Actual_Starttime THEN @Actual_Starttime
ELSE DATEADD(minute, @Student_Movement_Minutes, END_TIME) -- Add [@Student_Movement_Minutes] minutes
END
, GAP_END_TIME = CASE WHEN nxtStart >= @Actual_Endtime THEN @Actual_Endtime
ELSE DATEADD(minute, -@Student_Movement_Minutes, nxtStart) -- Subtract [@Student_Movement_Minutes] minutes
END
FROM cteNotAvailable
WHERE DATEDIFF(minute, END_TIME, nxtStart) > (1 + @Student_Movement_Minutes)
-- ORDER BY CASE [Day]
-- WHEN 'Mon' THEN 1
-- WHEN 'Tue' THEN 2
-- WHEN 'Wed' THEN 3
-- WHEN 'Thu' THEN 4
-- WHEN 'Fri' THEN 5
-- END, GAP_START_TIME;
Cleanup
DROP TABLE dbo.StudentCourse;
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply