Recording time gaps - 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 2005 Forums

Transact-SQL (2005)

Recording time gaps

Author

Topic

ecawilkinson
Starting Member

5 Posts

Posted - 2007-06-18 : 09:47:57

hi,

I have a view that contains details of when people are allocated to certain task, relevant fields as follows:

RES_ID int(4) (The Id no for that member of staff)
PK TASK_ID int(4) (the code for the task)
PK START_DATE datetime(8) (The date and time that they started on the task
END_DATE datetime(8) (when the task finishes)

I have been asked to create a report that shows when people are NOT working on a particular date (so they can reassign people if a someone calls in sick). Sounds easy until you think about it.

e.g. say Brian works the following hours on 02.01.2007
3.00am - 4.00am
4.30am - 6.00am
7.00am - 11.00am

I need to show the following in report:
Brian free 12.00am - 2.59am
Brian free 4.01am - 4.29am
Brian free 6.01am - 6.59am
Brian free 11.01am - 11.59pm

(I need this info for each member of staff)

The purpose of the report is so that, if someone else is off sick, then HR can see easily who is available to cover the duties of the sick person.

I cannot work out how to do this. I am relatively new to SQL server but have a good understanding of relational databases.

Any help would be much appreciated as I am totally stuck.

Thanks,
Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-18 : 10:35:31

Search for "gaps and islands".

Peter Larsson
Helsingborg, Sweden

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-18 : 11:24:55

Can you please give us a little more sample data and expected results, for example, data that covers more than day and more than Res_ID so that we can see how you want to handle those situations. The answer is fairly easy but I'd like to get some more details first.

Peso -- I am not sure how searching for "gaps and islands" will help; nothing is returned for those words here at SQLteam that I could find, and via Google I found two articles about finding gaps in sequential numbers, which doesn't really help much.

- Jeff
http://weblogs.sqlteam.com/JeffS

ecawilkinson
Starting Member

5 Posts

Posted - 2007-06-19 : 04:55:55

Jeff,

I was trying to keep it as simple as possible, but if you don't mind helping with all eventualities then I'll oblige with the info you asked for.

I need to cope with situations when the first task during the day actually started the day before, when the last one continues into the next day and when some tasks overlap. Also, If someone is not in that day at all, it needs to show that they are free all that day. So I think the following test data would cover it all:

RES_ID TASK_ID START_DATE END_DATE
Brian 1 01.01.07 23.50 02.01.07 01.00
Brian 2 02.01.07 03.00 02.01.07 04.00
Brian 3 02.01.07 03.15 02.01.07 04.15
John 4 02.01.07 22.00 02.01.07 22.30
John 5 02.01.07 22.15 02.01.07 22.20
John 6 02.01.07 23.30 03.01.07 00.30
Chris 7 01.01.07 12.00 01.01.07 12.30
Chris 8 04.01.07 06.00 04.01.07 07.00

So, if a user wanted a report for 02.01.07 it would report:
Brian free 01.01 - 02.59
Brian free 04.16 - 23.59
John free 00.00 - 21.59
John free 22.31 - 23.29
Chris free 00.00 - 23.59 (the whole day)

This functionality needs to be a stored procedure - with the date of the report as the only parameter - that is the source for a report in a Access Data Project.

Good luck! and thanks so much for taking the time to look at my problem,

Chris

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-19 : 09:00:12

Simple is good, but not when it doesn't cover the specifications! If I had gone ahead and tried to give you a solution without the additional data, it wouldn't have worked for you, so it's always good to keep it simple, but to cover all possibilities in your sample data. Thanks for the updated info. I'll take a look today...

- Jeff
http://weblogs.sqlteam.com/JeffS

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-19 : 19:19:41

[code]-- set up sample data
declare @sample table (res_id varchar(5), task_id tinyint, start_date smalldatetime, end_date smalldatetime)

set dateformat dmy

insert @sample
select 'Brian', 1, '01.01.07 23:50', '02.01.07 01:00' union all
select 'Brian', 2, '02.01.07 03:00', '02.01.07 04:00' union all
select 'Brian', 3, '02.01.07 03:15', '02.01.07 04:15' union all
select 'John', 4, '02.01.07 22:00', '02.01.07 22:30' union all
select 'John', 5, '02.01.07 22:15', '02.01.07 22:20' union all
select 'John', 6, '02.01.07 23:30', '03.01.07 00:30' union all
select 'Chris', 7, '01.01.07 12:00', '01.01.07 12:30' union all
select 'Chris', 8, '04.01.07 06:00', '04.01.07 07:00'

-- stage the result
declare @theDate smalldatetime
set @theDate = dateadd(day, datediff(day, 0, '20070102'), 0)

declare @stage table (res_id varchar(5), minval smallint, recid smallint)

insert @stage
select distinct s.res_id,
case
when s.valid = 1 then d.number
else 0
end as minval,
dense_rank() over (partition by s.res_id order by case when s.valid = 1 then d.number else 0 end) as recid
from (
select number
from master..spt_values
where name is null
and number between 0 and 1439
) as d
inner join (
select res_id,
case
when start_date < @thedate then 0
else datediff(minute, 0, start_date) % 1440
end as startminute,
case
when end_date > 1 + @thedate then 1440
else datediff(minute, 0, end_date) % 1440
end as endminute,
case
when @thedate between dateadd(day, datediff(day, 0, start_date), 0) and dateadd(day, datediff(day, 0, end_date), 0) then 1
else 0
end as valid
from @sample
union all
select res_id, 0, 0, 0 from @sample
union all
select res_id, 1440, 1440, 0 from @sample
) as s on s.startminute <= d.number and s.endminute >= d.number

-- show the expected output
select s1.res_id,
convert(char(5), dateadd(minute, case when s1.minval = 0 and s1.recid = 1 then 0 else s1.minval + 1 end, 0), 108),
convert(char(5), dateadd(minute, isnull(s2.minval, 1440) - 1, 0), 108)
from @stage as s1
left join @stage as s2 on s2.res_id = s1.res_id and s2.recid = s1.recid + 1
where isnull(s2.minval, 1440) > s1.minval + 1
order by s1.res_id,
s1.recid[/code]

Peter Larsson
Helsingborg, Sweden

ecawilkinson
Starting Member

5 Posts

Posted - 2007-06-20 : 02:28:15

hi to everyone,

Thanks for all your help, but I've just found out the the server we use is SQL Server 2000.

thanks,
Chris

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-20 : 10:13:20

And you post on SQL2005 forum?

Peter Larsson
Helsingborg, Sweden

ecawilkinson
Starting Member

5 Posts

Posted - 2007-06-20 : 11:54:22

I do not have direct access to the SQL server or its development tools - I only have Access in which to create stored procedures, so when I started the thread I assumed my company had SS2005, that is why I said I've JUST found out. I'm sorry if anyone feels I've wasted their time.

Chris

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources