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)
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
The best way to accomplish this type of goal is to have a 'Calendar' table. Then you simply JOIN against the Calendar table to interact with your data in a temporal fashion.
Read about using a Calendar table here:
Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519
Try:
Code Snippet
createtable #t (
RES_ID int,
TASK_ID int,
[START_DATE] datetime,
END_DATE datetime
)
go
insertinto #t values(1, 1,'2007-06-18T03:00:00','2007-06-18T04:00:00')
insertinto #t values(1, 1,'2007-06-18T04:30:00','2007-06-18T06:00:00')
insertinto #t values(1, 1,'2007-06-18T07:00:00','2007-06-18T11:00:00')
insertinto #t values(2, 1,'2007-06-18T03:20:00','2007-06-18T04:30:00')
insertinto #t values(2, 1,'2007-06-18T04:31:00','2007-06-18T06:15:00')
insertinto #t values(2, 1,'2007-06-18T07:00:00','2007-06-18T12:00:00')
go
declare @.d datetime
set @.d ='20070618'
;with cte
as
(
select
a.RES_ID,
dateadd(minute, 1, a.END_DATE)as [START_DATE_1],
dateadd(minute,-1,
(
selectmin(b.[START_DATE])
from #t as b
where b.RES_ID = a.RES_ID and b.[START_DATE] > a.END_DATE
)
)as [END_DATE_1]
from
#t as a
where
a.[START_DATE] >= @.d and a.[END_DATE] <dateadd(day, 1, @.d)
andnotexists(
select*
from #t as b
where
b.RES_ID = a.RES_ID
and b.[START_DATE] >= @.d and b.[END_DATE] <dateadd(day, 1, @.d)
and b.[START_DATE] =dateadd(minute, 1, a.END_DATE)
)
and a.END_DATE <(
selectmax(c.END_DATE)
from #t as c
where
c.RES_ID = a.RES_ID
and c.[START_DATE] >= @.d and c.[END_DATE] < dateadd(day, 1, @.d)
)
)
select
*
from
cte
union
select
RES_ID,
@.d,
dateadd(minute, -1, min([START_DATE]))
from
#t
where
[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)
group by
RES_ID
having
dateadd(minute, -1, min([START_DATE])) > @.d
union
select
RES_ID,
dateadd(minute, 1, max([START_DATE])),
dateadd(minute, 1439, @.d)
from
#t
where
[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)
group by
RES_ID
having
dateadd(minute, 1, max([START_DATE])) < dateadd(minute, 1439, @.d)
order by
RES_ID,
[START_DATE_1]
go
drop table #t
go
The idea behind this possible solution, is to find the gaps for each RES_ID for a specific date.
AMB
|||Hi,
thanks for the responses. Hunchback, how would I turn this into a stored procedure with the date as a parameter? I have tried but get lots of syntax errors. I'm sorry but I have only created basic SPs in the past.
Thanks,
Chris
|||
Code Snippet
create procedure dbo.usp_p1
@.d datetime
as
set nocount on
;with cte
as
(
select
a.RES_ID,
dateadd(minute, 1, a.END_DATE)as [START_DATE_1],
dateadd(minute,-1,
(
selectmin(b.[START_DATE])
from #t as b
where b.RES_ID = a.RES_ID and b.[START_DATE] > a.END_DATE
)
)as [END_DATE_1]
from
#t as a
where
a.[START_DATE] >= @.d and a.[END_DATE] <dateadd(day, 1, @.d)
andnotexists(
select*
from #t as b
where
b.RES_ID = a.RES_ID
and b.[START_DATE] >= @.d and b.[END_DATE] <dateadd(day, 1, @.d)
and b.[START_DATE] =dateadd(minute, 1, a.END_DATE)
)
and a.END_DATE <(
selectmax(c.END_DATE)
from #t as c
where
c.RES_ID = a.RES_ID
and c.[START_DATE] >= @.d and c.[END_DATE] < dateadd(day, 1, @.d)
)
)
select
*
from
cte
union
select
RES_ID,
@.d,
dateadd(minute, -1, min([START_DATE]))
from
#t
where
[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)
group by
RES_ID
having
dateadd(minute, -1, min([START_DATE])) > @.d
union
select
RES_ID,
dateadd(minute, 1, max([START_DATE])),
dateadd(minute, 1439, @.d)
from
#t
where
[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)
group by
RES_ID
having
dateadd(minute, 1, max([START_DATE])) < dateadd(minute, 1439, @.d)
order by
RES_ID,
[START_DATE_1]
go
exec dbo.usp_p1 '19960704'
go
AMB
|||hi,
I keep getting the following message when I try to save the SP
ADO error:Incorrect syntax near the word 'with'
the server is SQL SERVER 2000 and I am trying to save the SP in an Access .adp file. Do I need a slightly different syntax?
Thanks,
Chris
|||Commom table expressions were introduced with SS 2005, so we have to change the statement.
create procedure dbo.usp_p1
@.d datetime
as
set nocount on
select
a.RES_ID,
dateadd(minute, 1, a.END_DATE)as [START_DATE_1],
dateadd(minute,-1,
(
selectmin(b.[START_DATE])
from #t as b
where b.RES_ID = a.RES_ID and b.[START_DATE] > a.END_DATE
)
)as [END_DATE_1]
from
#t as a
where
a.[START_DATE] >= @.d and a.[END_DATE] <dateadd(day, 1, @.d)
andnotexists(
select*
from #t as b
where
b.RES_ID = a.RES_ID
and b.[START_DATE] >= @.d and b.[END_DATE] <dateadd(day, 1, @.d)
and b.[START_DATE] =dateadd(minute, 1, a.END_DATE)
)
and a.END_DATE <(
selectmax(c.END_DATE)
from #t as c
where
c.RES_ID = a.RES_ID
and c.[START_DATE] >= @.d and c.[END_DATE] < dateadd(day, 1, @.d)
)
union
select
RES_ID,
@.d,
dateadd(minute, -1, min([START_DATE]))
from
#t
where
[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)
group by
RES_ID
having
dateadd(minute, -1, min([START_DATE])) > @.d
union
select
RES_ID,
dateadd(minute, 1, max([START_DATE])),
dateadd(minute, 1439, @.d)
from
#t
where
[START_DATE] >= @.d and [END_DATE] < dateadd(day, 1, @.d)
group by
RES_ID
having
dateadd(minute, 1, max([START_DATE])) < dateadd(minute, 1439, @.d)
order by
RES_ID,
[START_DATE_1]
go
exec dbo.usp_p1 '19960704'
go
|||here's, another approach, this may or may not perform better than hunchbacks post, although they both have the same logic, btw, thanks hunchback for the test data that i piggy back with
[edited]
create table #t (
RES_ID int,
TASK_ID int,
[START_DATE] datetime,
END_DATE datetime
)
insert into #t values(1, 1, '2007-06-18T03:00:00', '2007-06-18T04:00:00')
insert into #t values(1, 1, '2007-06-18T04:30:00', '2007-06-18T06:00:00')
insert into #t values(1, 1, '2007-06-18T07:00:00', '2007-06-18T11:00:00')
insert into #t values(2, 1, '2007-06-18T03:20:00', '2007-06-18T04:30:00')
insert into #t values(2, 1, '2007-06-18T04:31:00', '2007-06-18T06:15:00')
insert into #t values(2, 1, '2007-06-18T07:00:00', '2007-06-18T12:00:00')
declare @.d datetime
set @.d = '20070618'
select *
from #t
select identity(int,1,1) as row
, *
, dateadd(mi,-1,start_date) as free_end
, dateadd(mi,1,end_date) as free_start
into #r
from #t
order by
res_id
, start_date
select res_id
, task_id
, min(dateadd(mi,-1,start_date)) as min_start
, max(dateadd(mi,1,end_date)) as max_end
into #minmax
from #t
group by
res_id
, task_id
select distinct
t.res_id
, t.task_id
, dateadd(mi,1,dateadd(mi,-1,@.d)) as free_start
, r.min_start as free_end
from #t t inner join
#minmax r on t.res_id = r.res_id
and t.task_id = r.task_id
union
select r1.res_id
, r1.task_id
, r1.free_start as free_start
, r2.free_end as free_end
from #r r1 inner join
#r r2 on r1.row = r2.row - 1
and r1.res_id = r2.res_id
and r1.task_id = r2.task_id
union
select distinct
t.res_id
, t.task_id
, r.max_end as free_start
, dateadd(mi,-1,dateadd(day,1,@.d)) as free_end
from #t t inner join
#minmax r on t.res_id = r.res_id
and t.task_id = r.task_id
drop table #t
drop table #r
drop table #minmax|||
hi Hunchback,
thanks very much this is great, but having had a close look at the data that the report has to deal with, it is not quite there - not your fault, mine for not being specific enough.
These are the extra conditions it has to deal with:
a task can start on the day before but end on the day of the report,e.g.
Chris task1 01/01/2007 23:50 - 02/01/2007 01.00
for the above, the report would have to say the freetime started at 01.01am.
Also, tasks can overlap ,e.g.
Chris task1 02/01/2007 06.50 - 02/01/2007 07.30
Chris task2 02/01/2007 07.15 - 02/01/2007 07.40
Chris task4 02/01/2007 07.20 - 02/01/2007 07.35
Chris task3 02/01/2007 07.50 - 02/01/2007 23.59
in the above the freetime is
00.00 - 06.49,
07.41 - 07.49
And finally,
if someone does not have any tasks that day then their freetime should show 00.00 - 23.59.
I realise that these extra conditions make it that much harder, but they were the reason that I originally thought I had no chance of working it out. It is my fault that I overlooked mentioning them.
Thanks,
Chris
|||You will probably find that cursors run much quicker here when you have a realistic amount of data.How does this code grab you? I'd use a proper resources table in the first statement though. This should handle overlaps and people who are missing.
--First populate everyone
select
r.res_id, @.thedate as start_date, dateadd(day,1,dateadd(minute,-1,@.thedate)) as end_date
into #tmp
from (select distinct res_id from #t) r
--Now run through the time worked
declare csrCsr cursor for
select res_id, start_date, end_date
from #t
where start_date >= @.thedate
and start_date < dateadd(day,1,@.thedate)
order by res_id, start_date
for read only
declare @.res int, @.start datetime, @.end datetime
open csrCsr
fetch csrCsr into @.res, @.start, @.end
while (@.@.fetch_status = 0)
begin
-- For each overlap, put records in either side, and then get rid of the original
insert into #tmp
select @.res, start_date, dateadd(minute,-1,@.start)
from #tmp
where res_id = @.res
and start_date < @.end
and @.start < end_date
and start_date < @.start
insert into #tmp
select @.res, dateadd(minute,1,@.end), end_date
from #tmp
where res_id = @.res
and start_date < @.end
and @.start < end_date
and @.end < end_date
delete
from #tmp
where res_id = @.res
and start_date < @.end
and @.start < end_date
fetch csrCsr into @.res, @.start, @.end
end
close csrCsr
deallocate csrCsr
--Now query the table, and drop the temporary resource
select * from #tmp
drop table #tmp
--Rob|||Oh, throw that into a stored procedure, like this:
create procedure dbo.fetch_times (@.thedate datetime)
as
...
--and of course, use the proper table name instead of #t.|||
hi,
I'd like to thank everyone for their help. After a day or too of navel-gazing, I admitted to myself that my SQL is not that great and resorted to.......CURSORS!
Sorry, but once I did that, it was pretty easy (see below). It is all well and good saying everything should be done with set-base solutions, but I don't have the time or skill for the more extreme examples of these.
anyway, here's my solution:
ALTER PROCEDURE GAP_Report
(
@.d DATETIME
)
AS
SET NOCOUNT ON
DECLARE @.resname VARCHAR(50), @.startdate DATETIME, @.enddate DATETIME, @.rname VARCHAR(50), @.edate DATETIME
SET @.rname = ''
DECLARE @.FreeTime TABLE
(
Res_name VARCHAR(50),
free_start DATETIME,
free_end DATETIME
)
DECLARE cCursor CURSOR FAST_FORWARD
FOR
SELECT
RES_NAME,TASK_START_DATE, TASK_FINISH_DATE
FROM
view_TasksPerUser
WHERE
((DATEDIFF(d,TASK_START_DATE,@.d) = 0) OR (DATEDIFF(d,TASK_FINISH_DATE,@.d) = 0))
ORDER BY
RES_NAME,TASK_START_DATE
OPEN cCursor
FETCH cCursor INTO @.resname, @.startdate, @.enddate
WHILE @.@.Fetch_Status = 0
BEGIN
IF @.rname <> @.resname
BEGIN
IF DATEDIFF(d,@.d,@.startdate) = 0
BEGIN
INSERT INTO @.FreeTime VALUES (@.resname,@.d,DATEADD(s,-1,@.startdate))
END
IF @.rname <> ''
BEGIN
IF DATEDIFF(d,@.d,@.enddate) = 0
BEGIN
INSERT INTO @.FreeTime VALUES (@.rname,DATEADD(s,1,@.edate),DATEADD(s,-1,DATEADD(d,1,@.d)))
END
END
SET @.edate = @.enddate
END
ELSE
BEGIN
IF DATEDIFF(s,@.edate,@.Startdate) > 0
BEGIN
INSERT INTO @.FreeTime VALUES (@.resname,DATEADD(s,1,@.edate), DATEADD(s,-1,@.startdate))
END
END
SET @.rname = @.resname
IF @.enddate > @.edate
SET @.edate = @.enddate
FETCH cCursor INTO @.resname, @.startdate, @.enddate
END
CLOSE cCursor
DEALLOCATE cCURSOR
INSERT INTO
@.FreeTime
SELECT
RES_NAME as res_name,
@.d as Free_start,
DATEADD(s,-1,DATEADD(d,1,@.d)) as Free_end
FROM
(SELECT
DISTINCT RES_NAME
FROM
MSP_RESOURCES
WHERE
RES_NAME <> ''
UNION ALL
SELECT
DISTINCT res_name
FROM
@.Freetime) U
GROUP BY res_name
HAVING COUNT(*) = 1
SELECT
*
FROM
@.Freetime
ORDER BY res_name, free_start, free_end
RETURN 0
thanks again,
Chris
