September 27, 2021 at 10:07 am
hi
got a question here that i hope someone can help with.
i have script that pulls a persons timesheet for a day (from 2 different tables) - start date/time and end date/time for each event in the day. it's an SQL 2000 server (yes i know!)
what i need to do is be able to calculate (in minutes) is 2 things.
the total number of gaps (in minutes) between each event
the total number of overlaps (in mionutes) between each event.
basically there should no no gaps or overlaps from the first start time to the last end time.
i hope i explained this fully.
here is my example,
as you can see, there is a 10 minute gap between the end time on event 3 and the start time on event 4
and there is a 10 minute overlap between the end time on event 10 and the start time on event 11
persreftravel_start travel_end
GIPAQ2021-09-19 07:30:00.000 2021-09-19 08:45:00.000
GIPAQ2021-09-19 08:45:00.000 2021-09-19 09:45:00.000
GIPAQ2021-09-19 09:45:00.000 2021-09-19 09:55:00.000
GIPAQ2021-09-19 10:05:00.000 2021-09-19 10:35:00.000
GIPAQ2021-09-19 10:35:00.000 2021-09-19 11:35:00.000
GIPAQ2021-09-19 11:35:00.000 2021-09-19 12:00:00.000
GIPAQ2021-09-19 12:00:00.000 2021-09-19 13:00:00.000
GIPAQ2021-09-19 13:00:00.000 2021-09-19 13:50:00.000
GIPAQ2021-09-19 13:50:00.000 2021-09-19 14:20:00.000
GIPAQ2021-09-19 14:20:00.000 2021-09-19 15:15:00.000
GIPAQ2021-09-19 15:05:00.000 2021-09-19 16:00:00.000
this is the query that i am using to pull this data
declare @startdate datetime
declare @persref varchar(10)
set @startdate= '19/09/2021'
set @persref = 'gipaq'
select
pers.pers_ref as 'persref',
travel_start,
travel_end
from
-- get personnel details --
(select pers_ref, pers_department_code, case when left(pers_name,2) in ('A1','A2','A3','A4','A5','E1','E2','E3') then STUFF(pers_name, 1, 3, '') when left(pers_name,4) in ('SER ','COM ') then RIGHT(pers_name, LEN(pers_name) - 4) else pers_name end as 'pers_name'
from personnel )
where pers_ref = @persref) pers
-- get event details --
left join (
select allocated_to 'engineer', pers_name as 'engineername', pers_department_code 'dept', dbo.dateonly(on_site) 'Date', call_ref, add1+' - '+post_code 'address', link_to_contract_header, convert(varchar(2000),engineers_report) as 'engineers_report', travel_start, on_site, off_site, travel_end, call_status_description, call_type_description, travel_miles, ce_id, 'CE' as 'eventtype'
from calls )
inner join clients ) on client_ref=link_to_client
inner join lu_call_types ) on call_type=call_type_code
inner join call_events ) on call_ref=link_to_call
inner join lu_call_status ) on event_code=call_status_code
inner join personnel ) on pers_ref=@persref
where dbo.dateonly(on_site)=@startdate
and allocated_to=@persref
and event_code in ('PR','F','RD','NA','PO')
union all
select NP_Engineer, pers_name, pers_department_code, dbo.dateonly(np_travel_start), null, NonProd_Description, 'NonProd', convert(varchar(2000),NP_Notes), NP_Travel_start, np_on_site, np_off_site, np_travel_end, 'NonProd', nonprod_description, 0, NP_ID, 'NP'
from non_productive_events )
inner join lu_nonprod_types ) on np_code=nonprod_code
inner join personnel ) on pers_ref=NP_Engineer
where NP_Engineer=@persref
and dbo.dateonly(np_travel_start)=@startdate) event on event.engineer=pers.pers_ref
order by travel_start
September 27, 2021 at 11:05 am
You should provide consumable date like this:
IF OBJECT_ID('tempdb..#temp','U') is not null
drop table #temp
select *
into #temp
from (values
('GIPAQ','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000'),
('GIPAQ','2021-09-19 08:45:00.000', '2021-09-19 09:45:00.000'),
('GIPAQ','2021-09-19 09:45:00.000', '2021-09-19 09:55:00.000'),
('GIPAQ','2021-09-19 10:05:00.000', '2021-09-19 10:35:00.000'),
('GIPAQ','2021-09-19 10:35:00.000', '2021-09-19 11:35:00.000'),
('GIPAQ','2021-09-19 11:35:00.000', '2021-09-19 12:00:00.000'),
('GIPAQ','2021-09-19 12:00:00.000', '2021-09-19 13:00:00.000'),
('GIPAQ','2021-09-19 13:00:00.000', '2021-09-19 13:50:00.000'),
('GIPAQ','2021-09-19 13:50:00.000', '2021-09-19 14:20:00.000'),
('GIPAQ','2021-09-19 14:20:00.000', '2021-09-19 15:15:00.000'),
('GIPAQ','2021-09-19 15:05:00.000', '2021-09-19 16:00:00.000')) T(persref,travel_start,travel_end)I can think of two ways of getting results.
Using LAG:
;with cte as
(
select *,
LAG(t1.travel_end) OVER (PARTITION BY t1.persref ORDER BY t1.travel_start) travel_end_lag
from #temp t1
)
select datediff(mi,travel_end_lag,travel_start) gap,
*
from cte
where travel_end_lag <> travel_start
Using a self join:
;with cte as
(
select *, ROW_NUMBER() OVER (PARTITION BY t1.persref ORDER BY t1.travel_start) rn
from #temp t1
)
select datediff(mi,t1.travel_end,t2.travel_start) gap,
*
from cte t1
left join cte t2
on t2.rn = t1.rn + 1
where datediff(mi,t1.travel_end,t2.travel_start) <> 0
A negative gap is an overlap and a positive gap is a gap.
September 27, 2021 at 11:47 am
I've always considered the following article to be a must read on this subject.
https://www.itprotoday.com/sql-server/new-solution-packing-intervals-problem
--Jeff Moden
Change is inevitable... Change for the better is not.
September 27, 2021 at 1:08 pm
i'm not sure either of those will work with SQL 2000?
i know LAG isn't supported. and i don't believe PARTITION is supported in SQL 2000 either?
September 27, 2021 at 1:10 pm
A negative gap is an overlap and a positive gap is a gap.
also, i need those to be 2 distinct seperate values.
September 27, 2021 at 1:26 pm
i'm not sure either of those will work with SQL 2000? i know LAG isn't supported. and i don't believe PARTITION is supported in SQL 2000 either?
Have you thought about upgrading your server?
It is easy to separate into columns:
select CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) >0
THEN datediff(mi,t1.travel_end,t2.travel_start)
ELSE NULL
END gap,
CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) <0
THEN datediff(mi,t1.travel_end,t2.travel_start)
ELSE NULL
END overlap,
September 27, 2021 at 1:34 pm
Have you thought about upgrading your server?
not an option i'm afraid.
this is what i have to work with.
i have done what you suggest though and used a consumable data
the end result is that i have 2 columns, 1 showing the total gaps (in minues) and 1 the total overlaps (in minutes).
there may be one of either, or multiples of each within a timesheet.
declare @startdate datetime
declare @persref varchar(10)
set @startdate= '19/09/2021'
set @persref = 'gipaq'
declare @ViewTimesheet table
(
persref varchar(8),
dept varchar(20),
date datetime,
call_ref int,
Address varchar(90),
link_to_contract_header varchar(30),
engineers_report text,
travel_start datetime,
on_site datetime,
off_site datetime,
travel_end datetime,
call_status_description varchar(50),
call_type_description varchar(50),
travel_miles int,
ce_id int,
eventtype varchar(10)
)
insert into @ViewTimesheet
select *
from (
select allocated_to, pers_department_code, dbo.dateonly(on_site) as 'startdate', call_ref, add1+' - '+post_code as 'address', link_to_contract_header, convert(varchar(2000),engineers_report) as 'engineers_report', travel_start, on_site, off_site, travel_end, call_status_description, call_type_description, travel_miles, ce_id, 'CE' as 'eventtype'
from calls )
inner join clients ) on client_ref=link_to_client
inner join lu_call_types ) on call_type=call_type_code
inner join call_events ) on call_ref=link_to_call
inner join lu_call_status ) on event_code=call_status_code
inner join personnel ) on pers_ref=@persref
where dbo.dateonly(on_site)=@startdate
and allocated_to=@persref
and event_code in ('PR','F','RD','NA','PO')
union all
select NP_Engineer, pers_department_code, dbo.dateonly(np_travel_start), null, NonProd_Description, 'NonProd', convert(varchar(2000),NP_Notes), NP_Travel_start, np_on_site, np_off_site, np_travel_end, 'NonProd', nonprod_description, 0, NP_ID, 'NP'
from non_productive_events )
inner join lu_nonprod_types ) on np_code=nonprod_code
inner join personnel ) on pers_ref=NP_Engineer
where NP_Engineer=@persref
and dbo.dateonly(np_travel_start)=@startdate) timesheetdetails
select
persref, travel_start, travel_end
from @ViewTimesheet
order by travel_start
September 27, 2021 at 1:48 pm
I can't see how your SQL will work with all those right brackets?
September 27, 2021 at 1:52 pm
I can't see how your SQL will work with all those right brackets?
looks like the copy/paste into this forum has screwed something up, the script does work though
declare @startdate datetime
declare @persref varchar(10)
set @startdate = '19/09/2021'
set @persref = 'gipaq'
declare @ViewTimesheet table
(
persref varchar(8),
dept varchar(20),
date datetime,
call_ref int,
Address varchar(90),
link_to_contract_header varchar(30),
engineers_report text,
travel_start datetime,
on_site datetime,
off_site datetime,
travel_end datetime,
call_status_description varchar(50),
call_type_description varchar(50),
travel_miles int,
ce_id int,
eventtype varchar(10)
)
insert into @ViewTimesheet
select *
from (
select allocated_to, pers_department_code, dbo.dateonly(on_site) as 'startdate', call_ref, add1+' - '+post_code as 'address', link_to_contract_header, convert(varchar(2000),engineers_report) as 'engineers_report', travel_start, on_site, off_site, travel_end, call_status_description, call_type_description, travel_miles, ce_id, 'CE' as 'eventtype'
from calls with (nolock)
inner join clients on client_ref=link_to_client
inner join lu_call_types on call_type=call_type_code
inner join call_events on call_ref=link_to_call
inner join lu_call_status on event_code=call_status_code
inner join personnel on pers_ref=@persref
where dbo.dateonly(on_site)=@startdate
and allocated_to=@persref
and event_code in ('PR','F','RD','NA','PO')
union all
select NP_Engineer, pers_department_code, dbo.dateonly(np_travel_start), null, NonProd_Description, 'NonProd', convert(varchar(2000),NP_Notes), NP_Travel_start, np_on_site, np_off_site, np_travel_end, 'NonProd', nonprod_description, 0, NP_ID, 'NP'
from non_productive_events with (nolock)
inner join lu_nonprod_types on np_code=nonprod_code
inner join personnel on pers_ref=NP_Engineer
where NP_Engineer=@persref
and dbo.dateonly(np_travel_start)=@startdate) timesheetdetails
select
t1.*
from @ViewTimesheet as t1
order by travel_start
September 27, 2021 at 2:10 pm
Jonathan AC Roberts wrote:Have you thought about upgrading your server?
not an option i'm afraid. this is what i have to work with.
i have done what you suggest though and used a consumable data
the end result is that i have 2 columns, 1 showing the total gaps (in minues) and 1 the total overlaps (in minutes). there may be one of either, or multiples of each within a timesheet.
Does that mean you have a solution?
September 27, 2021 at 2:14 pm
no, i was expaining what the end result needs to be.
you said i should provide consumable data, so that's all i have done
September 27, 2021 at 2:27 pm
That is not really consumable data as I don't have all your tables that make up the query.
You could add an identity column to the temporary table then insert values into the table ordered by allocated_to, travel_start
Then use a method similar to the one I pasted in.
I'm not sure if SQL 2000 has TOP?
DECLARE @startdate DATETIME;
DECLARE @persref VARCHAR(10);
SET @startdate = '19/09/2021';
SET @persref = 'gipaq';
DECLARE @ViewTimesheet TABLE
(
id int identity(1,1) not null,
persref VARCHAR(8),
dept VARCHAR(20),
date DATETIME,
call_ref INT,
Address VARCHAR(90),
link_to_contract_header VARCHAR(30),
engineers_report TEXT,
travel_start DATETIME,
on_site DATETIME,
off_site DATETIME,
travel_end DATETIME,
call_status_description VARCHAR(50),
call_type_description VARCHAR(50),
travel_miles INT,
ce_id INT,
eventtype VARCHAR(10)
);
INSERT INTO @ViewTimesheet
(
persref,
dept,
date,
call_ref,
Address,
link_to_contract_header,
engineers_report,
travel_start,
on_site,
off_site,
travel_end,
call_status_description,
call_type_description,
travel_miles,
ce_id,
eventtype
)
SELECT TOP(1000000000) *
FROM
(
SELECT allocated_to persref,
pers_department_code,
dbo.dateonly(on_site) AS 'startdate',
call_ref,
add1 + ' - ' + post_code AS 'address',
link_to_contract_header,
CONVERT(VARCHAR(2000), engineers_report) AS 'engineers_report',
travel_start,
on_site,
off_site,
travel_end,
call_status_description,
call_type_description,
travel_miles,
ce_id,
'CE' AS 'eventtype'
FROM calls WITH(NOLOCK)
INNER JOIN clients ON client_ref = link_to_client
INNER JOIN lu_call_types ON call_type = call_type_code
INNER JOIN call_events ON call_ref = link_to_call
INNER JOIN lu_call_status ON event_code = call_status_code
INNER JOIN personnel ON pers_ref = @persref
WHERE dbo.dateonly(on_site) = @startdate
AND allocated_to = @persref
AND event_code IN('PR', 'F', 'RD', 'NA', 'PO')
UNION ALL
SELECT NP_Engineer,
pers_department_code,
dbo.dateonly(np_travel_start),
NULL,
NonProd_Description,
'NonProd',
CONVERT(VARCHAR(2000), NP_Notes),
NP_Travel_start,
np_on_site,
np_off_site,
np_travel_end,
'NonProd',
nonprod_description,
0,
NP_ID,
'NP'
FROM non_productive_events WITH(NOLOCK)
INNER JOIN lu_nonprod_types ON np_code = nonprod_code
INNER JOIN personnel ON pers_ref = NP_Engineer
WHERE NP_Engineer = @persref
AND dbo.dateonly(np_travel_start) = @startdate
) timesheetdetails
order by allocated_to ,travel_start;
SELECT datediff(mi,t1.travel_end,t2.travel_start) gap,
t1.*
FROM @ViewTimesheet AS t1
inner join @ViewTimesheet AS t2
on t1.persref = t2.persref
and on t2.id = t1.id + 1
where datediff(mi,t1.travel_end,t2.travel_start) <> 0
ORDER BY travel_start;
September 27, 2021 at 2:36 pm
that doesn't seem to give the total minutes of gaps and/or overlaps though?
It just shows the 2 rows of data that may have an issue.
sql2000 does support TOP, but in your script it produces an error. removing the TOP and leaving the * brings back the 2 rows mentioned above
September 27, 2021 at 3:11 pm
that doesn't seem to give the total minutes of gaps and/or overlaps though? It just shows the 2 rows of data that may have an issue.
sql2000 does support TOP, but in your script it produces an error. removing the TOP and leaving the * brings back the 2 rows mentioned above
Do you want it to just show the 2 rows or do you want it to show all rows?
What does the first column show (labelled gap)?
For the identity column to work the rows must be inserted into the table in the correct order. Maybe the ORDER BY works without the TOP. I'm not sure as I've never used SQL Server 2000.
September 27, 2021 at 3:18 pm
i don't want it to show any rows.
the result i want is this
persref Gaps Overlaps
gipaq 10 10
you script doesn't run at all, but produces an error, i have to remove the TOP(10000) line to get it to produce anything, and remove "allocated_to" from some of the lines as well
Msg 170, Level 15, State 1, Line 45
Line 45: Incorrect syntax near '('.
Msg 170, Level 15, State 1, Line 95
Line 95: Incorrect syntax near 'timesheetdetails'.
once i remove those it produces 2 rows, with a 10 and -10 in the "gap" column.
this is the amended version of your script that allowed it to run without errors
DECLARE @startdate DATETIME;
DECLARE @persref VARCHAR(10);
SET @startdate = '19/09/2021';
SET @persref = 'gipaq';
DECLARE @ViewTimesheet TABLE
(
id int identity(1,1) not null,
persref VARCHAR(8),
dept VARCHAR(20),
date DATETIME,
call_ref INT,
Address VARCHAR(90),
link_to_contract_header VARCHAR(30),
engineers_report TEXT,
travel_start DATETIME,
on_site DATETIME,
off_site DATETIME,
travel_end DATETIME,
call_status_description VARCHAR(50),
call_type_description VARCHAR(50),
travel_miles INT,
ce_id INT,
eventtype VARCHAR(10)
);
INSERT INTO @ViewTimesheet
(
persref,
dept,
date,
call_ref,
Address,
link_to_contract_header,
engineers_report,
travel_start,
on_site,
off_site,
travel_end,
call_status_description,
call_type_description,
travel_miles,
ce_id,
eventtype)
SELECT *
FROM
(
SELECT allocated_to persref,
pers_department_code,
dbo.dateonly(on_site) AS 'startdate',
call_ref,
add1 + ' - ' + post_code AS 'address',
link_to_contract_header,
CONVERT(VARCHAR(2000), engineers_report) AS 'engineers_report',
travel_start,
on_site,
off_site,
travel_end,
call_status_description,
call_type_description,
travel_miles,
ce_id,
'CE' AS 'eventtype'
FROM calls WITH(NOLOCK)
INNER JOIN clients ON client_ref = link_to_client
INNER JOIN lu_call_types ON call_type = call_type_code
INNER JOIN call_events ON call_ref = link_to_call
INNER JOIN lu_call_status ON event_code = call_status_code
INNER JOIN personnel ON pers_ref = @persref
WHERE dbo.dateonly(on_site) = @startdate
AND allocated_to = @persref
AND event_code IN('PR', 'F', 'RD', 'NA', 'PO')
UNION ALL
SELECT NP_Engineer,
pers_department_code,
dbo.dateonly(np_travel_start),
NULL,
NonProd_Description,
'NonProd',
CONVERT(VARCHAR(2000), NP_Notes),
NP_Travel_start,
np_on_site,
np_off_site,
np_travel_end,
'NonProd',
nonprod_description,
0,
NP_ID,
'NP'
FROM non_productive_events WITH(NOLOCK)
INNER JOIN lu_nonprod_types ON np_code = nonprod_code
INNER JOIN personnel ON pers_ref = NP_Engineer
WHERE NP_Engineer = @persref
AND dbo.dateonly(np_travel_start) = @startdate
) timesheetdetails
order by travel_start;
SELECT datediff(mi,t1.travel_end,t2.travel_start) gap,
t1.*
FROM @ViewTimesheet AS t1
inner join @ViewTimesheet AS t2
on t1.persref = t2.persref
and t2.id = t1.id + 1
where datediff(mi,t1.travel_end,t2.travel_start) <> 0
Viewing 15 posts - 1 through 15 (of 21 total)
You must be logged in to reply to this topic. Login to reply