We had similar peaks at regular intervals, can't remember the exact interval, it might have been about 15 mins.

I think it might be to do with having a clustered pair...

I think you want an UPDATE followed...

Unless I'm misreading the given solution, it won't properly calculate the time difference correctly for entries that span a date boundary.   This is one of the many reasons...

Instead of 112 you need 105

SELECT CONVERT(DATE,MAX(dato),105) FROM dbo.dutyrostershift WHERE employeeid = 11

https://docs.microsoft.com/en-us/sql/t-sql/functions/cast-and-convert-transact-sql?view=sql-server-2017

• This reply was modified 7 years, 4 months ago by Jonathan AC Roberts.

You should check how to post questions with consumable data before posting a question:

https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help

;WITH CTE AS
(
    SELECT * FROM (VALUES
    (34,1,1003,convert(time,'06:43:00'),convert(date,'01.04.2019'),101),
...

Change the MERGE to be an UPDATE followed by an INSERT. This will probably give you a 40% performance improvement.

Check if the merge is updating any indexes, as this could...

Declare @TheTable table (ID int, V1 varchar(1), V2 varchar(2), V3 varchar(1), V4 varchar(1))
Insert into @TheTable values (1, 'A', NULL, NULL, NULL)
Insert into @TheTable values (2, 'B', NULL,...

It will exist with a count of zero.
You need to replace 'COUNT(*)' with '*'