Schedule Overlapping - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Schedule Overlapping

Author

Topic

sqlgeek
Starting Member

3 Posts

Posted - 2009-05-23 : 03:34:11

Hi Friends,

I am faciing a problem with overlapping schedules for internet services. We have the capability of storing the availability time and non-availability time of the internet service.

Please consider the following scenario:
SHEDULE 1: (Schedule for the availability time of the internet services)
start_date: 01-Jan-2009
end_date: 31-Jan-2009
start_time: 9.00 a.m.
end_time: 5.00 p.m

SHEDULE 2: (Schedule for the non-availability time of the internet services)
start_date: 15-Jan-2009
end_date: 20-Jan-2009
start_time: 1.00 p.m.
end_time: 2.00 p.m

SHEDULE 3: (Schedule for the non-availability time of the internet services)
start_date: 24-Jan-2009
end_date: 26-Jan-2009
start_time: 1.00 p.m.
end_time: 2.00 p.m

The information would be stored in the database in the following manner

schedule_id schedule_type start_date end_date start_time end_time
----------- ------------- ---------- ---------- -------- ---------
1 Available 01-Jan-2009 31-Jan-2009 9.00 a.m 5.00 p.m
2 Non-Available 15-Jan-2009 20-Jan-2009 1.00 p.m 2.00 p.m
3 Non-Available 24-Jan-2009 26-Jan-2009 1.00 p.m 2.00 p.m

Now I want to break up the above schedule in actual available minutes depending upon the available/non-available schedule of the internet services for the month of january. Note that schedule 2 and 3 overlap schedule number 1.

Expected Output
---------------
start_date end_date start_time end_time work_minutes_per_day
---------- -------- ---------- -------- --------------------
01-Jan-2009 14-Jan-2009 9.00 a.m 5.00 p.m 480
15-Jan-2009 20-Jan-2009 9.00 a.m 1.00 p.m 240
15-Jan-2009 20-Jan-2009 2.00 a.m 5.00 p.m 180
15-Jan-2009 20-Jan-2009 1.00 p.m 2.00 p.m -60
21-Jan-2009 23-Jan-2009 9.00 a.m 5.00 p.m 480
24-Jan-2009 26-Jan-2009 9.00 a.m 1.00 p.m 240
24-Jan-2009 26-Jan-2009 2.00 a.m 5.00 p.m 180
24-Jan-2009 26-Jan-2009 1.00 p.m 2.00 p.m -60
27-Jan-2009 31-Jan-2009 9.00 a.m 5.00 p.m 480

Please note how the schedules for records between 15-Jan-2009 and 20-Jan-2009 have been broken into multiple records depending upon the non-availability time of the internet services. The same concept have been applied for the records between 24-Jan-2009 and 26-Jan-2009.

The above expected result is for display/reporting purpose. Hence cannot tweak around with the requirements.

Any help in terms of query or algorithms would be highly appreciated.

Thanks in advance.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 14:47:09

what are the datatypes of start_date, end_date, start_time & end_time fields?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-23 : 15:37:41

[code]SELECT start_date, end_date, start_time, end_time,
(-1) * DATEDIFF(mi,CONVERT(datetime,start_time), CONVERT(datetime,end_time)) AS work_minutes_per_day
FROM Table
WHERE Schedule_Type='Non-Available'

UNION ALL

SELECT t.start_date, t.end_date, t1.start_time, t.start_time,
DATEDIFF(mi,CONVERT(datetime,t1.start_time), CONVERT(datetime,t.start_time)) AS work_minutes_per_day
FROM Table t
CROSS JOIN (SELECT TOP 1 start_date, start_time
FROM Table
WHERE CONVERT(datetime,start_date+ ' ' + start_time)<=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND CONVERT(datetime,end_date+ ' ' + end_time)>=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND Schedule_Type='Available')t1
WHERE Schedule_Type='Non-Available'

UNION ALL

SELECT t.start_date, t.end_date, t.end_time, t1.end_time,
DATEDIFF(mi,CONVERT(datetime,t.end_time), CONVERT(datetime,t1.end_time)) AS work_minutes_per_day
FROM Table t
CROSS JOIN (SELECT TOP 1 end_date, end_time
FROM Table
WHERE CONVERT(datetime,start_date+ ' ' + start_time)<=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND CONVERT(datetime,end_date+ ' ' + end_time)>=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND Schedule_Type='Available')t1
WHERE Schedule_Type='Non-Available'

UNION ALL

SELECT t1.start_date, CONVERT(varchar(11),CONVERT(datetime,t.start_date)-1), t1.start_time, t1.end_time,
DATEDIFF(mi,CONVERT(datetime,t1.start_time), CONVERT(datetime,t1.end_time)) AS work_minutes_per_day
FROM Table t
CROSS JOIN (SELECT TOP 1 start_date, start_time
FROM Table
WHERE CONVERT(datetime,start_date+ ' ' + start_time)<=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND CONVERT(datetime,end_date+ ' ' + end_time)>=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND Schedule_Type='Available')t1
WHERE Schedule_Type='Non-Available'

UNION ALL

SELECT CONVERT(varchar(11),CONVERT(datetime,t.end_date)+1), t1.end_date, t1.start_time, t1.end_time,
DATEDIFF(mi,CONVERT(datetime,t1.start_time), CONVERT(datetime,t1.end_time)) AS work_minutes_per_day
FROM Table t
CROSS JOIN (SELECT TOP 1 start_date, start_time
FROM Table
WHERE CONVERT(datetime,start_date+ ' ' + start_time)<=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND CONVERT(datetime,end_date+ ' ' + end_time)>=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND Schedule_Type='Available')t1
WHERE Schedule_Type='Non-Available'

UNION ALL

SELECT t.start_date, t.end_date, t.start_time, t.end_time,
DATEDIFF(mi,CONVERT(datetime,t.start_time), CONVERT(datetime,t.end_time)) AS work_minutes_per_day
FROM Table t
CROSS JOIN (SELECT COUNT(*) AS Non_availableCount
FROM Table
WHERE (((CONVERT(datetime,end_date+ ' ' + end_time)>=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND CONVERT(datetime,end_date+ ' ' + end_time)<=CONVERT(datetime,t.end_date+ ' ' + t.end_time))
OR (CONVERT(datetime,start_date+ ' ' + start_time)>=CONVERT(datetime,t.start_date+ ' ' + t.start_time)
AND CONVERT(datetime,start_date+ ' ' + start_time)<=CONVERT(datetime,t.end_date+ ' ' + t.end_time)))
AND Schedule_Type='Non-Available')t1
WHERE Schedule_Type='Available'
AND Non_availableCount=0
[/code]

sqlgeek
Starting Member

3 Posts

Posted - 2009-05-25 : 00:45:54

Thanks for your quick response.

I am facing a few problems while running the above query. It gives me an error like:
The multi-part identifier "t.start_date" could not be bound.
The multi-part identifier "t.start_time" could not be bound.

BTW...the start/end date fields are of datatype "datetime" and I am running the query in mssql-2005.

Any help is highly appreciable

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-25 : 14:14:53

are your field names correct (start_date,start_time)?

sqlgeek
Starting Member

3 Posts

Posted - 2009-05-25 : 23:15:38

yes..the field names are correct.

I got the query working. I changed the alias in the query used in CROSS JOIN to use the table name as the alias i.e. used table.start_date instead of t.start_date and it worked.

The query gave me desired results. Thanks a lot for the help that you provided.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-05-26 : 12:34:59

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources