help with script to calculate gaps and overlaps from list of times
-
So it produces the correct result.
To get it into 2 separate column just use the CASE statement I provided earlier.
Then just remove the other columns from the query you do not want to show.
-
well no it doesn't produce the correct result.
-10 is not the correct result.
-
Does it insert the data into the table variable in the correct order? You can tell if you query the table and look at the Id value.
-
yes it is inserting into the table correctly
also, where there are mulitple gaps and overlaps, the result is this (when using you case statement)
persref gap overlap
GIPAQ 10 NULL
GIPAQ 10 NULL
GIPAQ NULL -5
GIPAQ 5 NULL
GIPAQ NULL -10 -
this gives a closer match as to what the end result should be, but still with the minus figure.
i could just remove the minus symbol.
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
t1.persref,
sum(CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) >0 THEN datediff(mi,t1.travel_end,t2.travel_start) ELSE NULL END) gap,
sum(CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) <0 THEN datediff(mi,t1.travel_end,t2.travel_start) ELSE NULL END) overlap
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
group by t1.persref -
Just change the order of comparison in the datediff for overlap to change the sign:
sum(CASE WHEN datediff(mi,t1.travel_end,t2.travel_start) <0
THEN datediff(mi,t2.travel_start,t1.travel_end) ELSE NULL
END) overlap -
Ok. There's a whole bunch of data in a whole bunch of tables that we have absolutely zero access to. With that in mind, the only problem I'm going to solve is the problem of accurately counting the GapMinutes and the OverlapMinutes. To do that easily, we're going to need a working table to make up for the fact that we have no ROW_NUMBER() or LAG() function in SQL Server 2000. Then, you can join to that working table or query it or whatever you need to do.
First, here's some readily consumable test data that should work in SQLServer 2000. I added another "persref" so that you understand you can do the whole shebang in one "swell-foop" when we get to the solution. 😀
--=====================================================================================================================
-- Create the sample time sheet table and data.
-- This is NOT a part of the solution. We're just setting up a work/test environment here.
--=====================================================================================================================
--===== If the test table already exists, drop it
IF OBJECT_ID('tempdb..#TimeSheet','U') IS NOT NULL DROP TABLE #TimeSheet;
GO
--===== Create and populate the #TimeSheet table on the fly.
SELECT persref = CONVERT(CHAR(5),d.persref)
,travel_start = CONVERT(DATETIME,d.travel_start)
,travel_end = CONVERT(DATETIME,d.travel_end)
INTO #TimeSheet
FROM (
SELECT 'GIPAQ','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 08:45:00.000', '2021-09-19 09:45:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 09:45:00.000', '2021-09-19 09:55:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 10:05:00.000', '2021-09-19 10:35:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 10:35:00.000', '2021-09-19 11:35:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 11:35:00.000', '2021-09-19 12:00:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 12:00:00.000', '2021-09-19 13:00:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 13:00:00.000', '2021-09-19 13:50:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 13:50:00.000', '2021-09-19 14:20:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 14:20:00.000', '2021-09-19 15:15:00.000' UNION ALL
SELECT 'GIPAQ','2021-09-19 15:05:00.000', '2021-09-19 16:00:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 07:30:00.000', '2021-09-19 08:45:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 08:45:00.000', '2021-09-19 09:35:00.000' UNION ALL --Added 10 minute gap to what existed
SELECT 'JULUP','2021-09-19 09:45:00.000', '2021-09-19 09:55:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 10:05:00.000', '2021-09-19 10:35:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 10:35:00.000', '2021-09-19 11:35:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 11:35:00.000', '2021-09-19 12:00:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 12:00:00.000', '2021-09-19 13:00:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 13:00:00.000', '2021-09-19 14:10:00.000' UNION ALL --Added 20 minute overlap to what existed
SELECT 'JULUP','2021-09-19 13:50:00.000', '2021-09-19 14:20:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 14:20:00.000', '2021-09-19 15:15:00.000' UNION ALL
SELECT 'JULUP','2021-09-19 15:05:00.000', '2021-09-19 16:00:00.000'
)d(persref,travel_start,travel_end)
;
--===== Display the content of the #TimeSheet table to confirm things worked as expected.
SELECT *
FROM #TimeSheet
ORDER BY persref, travel_start
;Heh... and now a very old, tried and true solution that will work in SQLServer 2000. Again, join to the resulting working table or do whatever you want with it. It contains the original data and the GapMinutes and OverlapMinutes where both are returned in separate columns as positive numbers. If you want the OverlapMinutes to be negative, just remove the ABS() function.
--=====================================================================================================================
-- Solve the problem using an updated self-join
--=====================================================================================================================
--===== If the work table already exists, drop it.
IF OBJECT_ID('tempdb..#WorkTable','U') IS NOT NULL DROP TABLE #WorkTable;
GO
--===== Create the work table with the same columns as the #TimeSheet table plus some special columns.
CREATE TABLE #WorkTable
(
SortOrder INT IDENTITY(1,1) PRIMARY KEY CLUSTERED
,persref CHAR(5)
,travel_start DATETIME
,travel_end DATETIME
,GapMinutes INT DEFAULT 0
,OverLapMinutes INT DEFAULT 0
)
;
--===== Now, populate the working table from the timesheet table in the correct order.
-- We need to do this to create a joinable "SortOrder" column and the required
-- GapMinutes and OverlapMinutes columns.
INSERT INTO #WorkTable WITH (TABLOCK)
(persref, travel_start, travel_end)
SELECT persref, travel_start, travel_end
FROM #TimeSheet
ORDER BY persref, travel_start
OPTION (MAXDOP 1)
;
--===== Calculate and Update the working table with all gaps and overlaps by row.
UPDATE lo
SET GapMinutes = CASE
WHEN DATEDIFF(mi,lo.travel_end,hi.travel_start) > 0
THEN DATEDIFF(mi,lo.travel_end,hi.travel_start)
ELSE 0
END
,OverLapMinutes = CASE
WHEN DATEDIFF(mi,lo.travel_end,hi.travel_start) < 0
THEN ABS(DATEDIFF(mi,lo.travel_end,hi.travel_start))
ELSE 0
END
FROM #WorkTable lo
FULL JOIN #WorkTable hi ON lo.SortOrder+1 = hi.SortOrder
AND lo.persref = hi.persref
;
--===== Display the content of the #WorkTable table to confirm things worked as expected.
SELECT *
FROM #WorkTable
ORDER BY persref, travel_start
;That returns the following working table...
An example of what I'm talking about is if you only want to see rows that have a Gap or Overlap, then the following code will do it for you.
--===== Display the content of the #WorkTable table to confirm things worked as expected
-- displaying only those rows that have a Gap or Overlap.
SELECT *
FROM #WorkTable
WHERE GapMinutes <> 0 OR OverLapMinutes <> 0
ORDER BY persref, travel_start
;... and that returns the following...
Like I said, once that working table is created, knock yourself out with however you want to join to it and use it. We don't have your other data to test with.
--Jeff Moden
RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.Change is inevitable... Change for the better is not.
Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Viewing 7 posts - 16 through 21 (of 21 total)
You must be logged in to reply to this topic. Login to reply