Howto calculate effective drivetime minus activity times when they exist
-
Hallo,
I have a business need to calculate the effective drivetimes based on tachodata
from witch i need to filter out the times that a truck was within an activity of within cmr
the cmr part i have not included cause i assume that when there is a way found the logic would be likewise.
Now because all the data needed resides on the sqlserver it would be nice to come up with a t-sql way to calculate the total drivetime for 1 driver on a certain day.
Now i really like t-sql and have already managed to write some fine stuff learned from examples here on the forum but working with dates on this level, thats still beyond my head.
I anyone would be so kind to help me out that would be really appreciated.
/*
Calculate the effective drivetimes for a truck based on tacho data
minus the drivetime that would be happening within an activity
activity is paid for on other aggreeements so there would not be a double payment for the drivetimes
*/
declare @tDriveTimes TABLE (drtid int, starttime DATETIME, endtime DATETIME, drivetimeminutes INT)
INSERT INTO @tDriveTimes
( drtid ,starttime , endtime, drivetimeminutes )
SELECT 1, '2009-11-24 05:00','2009-11-24 06:00',60
UNION ALL
SELECT 2, '2009-11-24 06:15','2009-11-24 06:45',30
UNION ALL
SELECT 3, '2009-11-24 07:25','2009-11-24 07:50',25
UNION ALL
SELECT 4, '2009-11-24 08:00','2009-11-24 10:00',120
DECLARE @tActivities TABLE (actid INT, astarttime DATETIME, astoptime DATETIME, acttime INT)
INSERT INTO @tActivities ( actid ,astarttime ,astoptime,acttime)
SELECT 1, '2009-11-24 05:30', '2009-11-24 05:45',15
UNION ALL
SELECT 2, '2009-11-24 06:30', '2009-11-24 07:15',45
UNION ALL
SELECT 3, '2009-11-24 07:40', '2009-11-24 09:00',80
SELECT * FROM @tDriveTimes
SELECT * FROM @tActivities
/*
From this result i would need the effective drivetime minus the time that an activity was busy
drtid1 : From 05 till 06 has actid1 completely in himself so the total time of the activity would be subtracted from the drivetime
giving 60 minutes - 15 minutres activity time = 45
drtid2 : 6:15 till 6:45 is overlapped with activity 2 from 6:30 till 7:15, witch means that only the time from 6:15 till starttime activity 6:30 may be counted for = 15 minutes
drtid3 starts from 07:25 till 07:50 but i have an activity that starts at 07:40 so i may online counting : 07:25 till 07:40 = 15minutes
and now a special one
drtid4 starts while an activity is busy so its actual startpoint would be the endpoint of the activity eg:09:00 and the drivetime ends at 10:00 so it would count for 60 minutes
Now, i have some ways to handle this in Vb.net but because al needed data resides already on sqlserver i was wondering whether it is possible to handle this via t-sql
There is no real time pressure on the calculation because it would only run at night. so obtaining the fastest way is not an issue here,
*/
Wkr
Eddy
-
Probably a bit overcomplex, but seems to give the correct results.
WITH Starts(drtid,starttime) AS (SELECT b.drtid,
b.starttime
FROM @tDriveTimes b
WHERE NOT EXISTS(SELECT * FROM @tActivities a
WHERE b.starttime BETWEEN a.astarttime AND a.astoptime)
UNION ALL
SELECT b.drtid,
a.astoptime
FROM @tActivities a
INNER JOIN @tDriveTimes b ON a.astoptime >= b.starttime
AND a.astoptime < b.endtime),
Ends(drtid,endtime) AS (
SELECT b.drtid,
b.endtime
FROM @tDriveTimes b
WHERE NOT EXISTS(SELECT * FROM @tActivities a
WHERE b.endtime BETWEEN a.astarttime AND a.astoptime)
UNION ALL
SELECT b.drtid,
a.astarttime
FROM @tActivities a
INNER JOIN @tDriveTimes b ON a.astarttime > b.starttime
AND a.astarttime <= b.endtime),
StartAndEnds(drtid,starttime,endtime) AS (
SELECT a.drtid,
a.starttime,
MIN(b.endtime)
FROM Starts a
INNER JOIN Ends b ON b.drtid=a.drtid AND b.endtime>a.starttime
GROUP BY a.drtid,a.starttime)
SELECT drtid,
SUM(DATEDIFF(minute,starttime,endtime)) AS totalTime
FROM StartAndEnds
GROUP BY drtid
ORDER BY drtid;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537
-
Hi Mark,
Tnx for your help,
The provided code seems to do the trick, for witch i send you a lot of thanks,
I'm really not strong when dates come into play.
The code is idd a bit complex but i can manage to read it, now trying to understand the whole process.
Im currently busy setting up al data and code for the final workout.
Hope that i can understand the code sufficient enough to port it to the whole system,
cause its offcourse more to it than the provided example.
Do you think i could link this cte on another table (Drivers) so it would count the data per driver per day or should i change the cte towards a function.?
Wkr,
Eddy
-
eddy-644184 (11/24/2009)
Hi Mark,Tnx for your help,
The provided code seems to do the trick, for witch i send you a lot of thanks,
I'm really not strong when dates come into play.
The code is idd a bit complex but i can manage to read it, now trying to understand the whole process.
Im currently busy setting up al data and code for the final workout.
Hope that i can understand the code sufficient enough to port it to the whole system,
cause its offcourse more to it than the provided example.
Do you think i could link this cte on another table (Drivers) so it would count the data per driver per day or should i change the cte towards a function.?
Wkr,
Eddy
I would start by trying to join the CTE to the Drivers table. Post back if you're having difficulty.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Hi Mark,
When i try your code with real production data it seems not to be correct
When i see Activity DAT_ID:2 fields: DAT_BEGIN_DATE & DAT_END_DATE
i see that the driver was in activity between 08:00 till 08:15
If i look at Drivinttime DRT_ID:4 fields DRT_STARTTIME & DRT_STOPTIME & KF_ACT_TYPE = 1 (mean drivingtime)
from 08:11 till 08:14 , witch means the driver had been driven for 4 minutes whilst busy in an activity
So those 4 minutes have to been dropped
Only the drivingtimes where KF_ACT_TYPE = 1 may be taken into consideration
If i run the code and take into consideration that only act_type = 1 have to be counted for, it tells me that 0 minutes have been dropped
that does not seem to be correct
my table structure and sample data (part of the productiondata)
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DIGIACTIVITEIT](
[DAT_ID] [int] IDENTITY(1,1) NOT NULL,
[WKN_ID] [int] NULL,
[DAT_RITNR] [int] NOT NULL,
[DAT_PAR_ID_DISC] [int] NOT NULL,
[DAT_BAC] [datetime] NOT NULL,
[DAT_EAC] [datetime] NULL,
[DAT_BCMR] [datetime] NULL,
[DAT_ECMR] [datetime] NULL,
[DAT_BEGIN_DATE] AS (case when [DAT_BAC]<[DAT_BCMR] then [DAT_BAC] else [DAT_BCMR] end),
[DAT_END_DATE] AS (case when [DAT_EAC]>[DAT_ECMR] then [DAT_EAC] else [DAT_ECMR] end),
[DAT_TYPE_ACTIVITEIT] [int] NOT NULL,
[DAT_BEREKEN_STATUS] [int] NOT NULL,
[DAT_REGISTRATIE_DATUM] [datetime] NULL,
[DAT_ACT_SOURCE_DATA] [varchar](50) NOT NULL,
[DAT_BASSIRELATIE] [varchar](15) NOT NULL,
CONSTRAINT [PK_DIGIACTIVITEIT] PRIMARY KEY CLUSTERED
(
[DAT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET IDENTITY_INSERT [dbo].[DIGIACTIVITEIT] ON
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (1, 7, 9287899, 866, CAST(0x00009CBC006A04A0 AS DateTime), CAST(0x00009CBC00761A60 AS DateTime), CAST(0x00009CBC0066FF30 AS DateTime), CAST(0x00009CBC007779F0 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (2, 7, 9287899, 866, CAST(0x00009CBC0083D600 AS DateTime), CAST(0x00009CBC00857BE0 AS DateTime), CAST(0x00009CBC0083D600 AS DateTime), CAST(0x00009CBC0087F4B0 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (3, 7, 9287901, 866, CAST(0x00009CBC009523B0 AS DateTime), CAST(0x00009CBC009523B0 AS DateTime), CAST(0x00009CBC0095B050 AS DateTime), CAST(0x00009CBC00986F70 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (4, 7, 9287901, 866, CAST(0x00009CBC00A36BF0 AS DateTime), CAST(0x00009CBC00A8A3E0 AS DateTime), CAST(0x00009CBC00A36BF0 AS DateTime), CAST(0x00009CBC00A8EA30 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (5, 7, 9287902, 866, CAST(0x00009CBC00B35A10 AS DateTime), CAST(0x00009CBC00B58C90 AS DateTime), CAST(0x00009CBC00B3E6B0 AS DateTime), CAST(0x00009CBC00B80560 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (6, 7, 9287902, 866, CAST(0x00009CBC00C15C00 AS DateTime), CAST(0x00009CBC00CDB810 AS DateTime), CAST(0x00009CBC00C301E0 AS DateTime), CAST(0x00009CBC00CDFE60 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (7, 7, 9287903, 866, CAST(0x00009CBC00D70EB0 AS DateTime), CAST(0x00009CBC00E81610 AS DateTime), CAST(0x00009CBC00D79B50 AS DateTime), CAST(0x00009CBC00E975A0 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (8, 7, 9287903, 866, CAST(0x00009CBC00F47220 AS DateTime), CAST(0x00009CBC00F7BDE0 AS DateTime), CAST(0x00009CBC00F47220 AS DateTime), CAST(0x00009CBC00F9F060 AS DateTime), 1, 10, CAST(0x00009CCD00AB7224 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (9, 7, 9287904, 866, CAST(0x00009CBC010300B0 AS DateTime), CAST(0x00009CBC01034700 AS DateTime), CAST(0x00009CBC01038D50 AS DateTime), CAST(0x00009CBC01064C70 AS DateTime), 1, 10, CAST(0x00009CCD00AB7229 AS DateTime), N'Progress', N'dSBK00001106')
INSERT [dbo].[DIGIACTIVITEIT] ([DAT_ID], [WKN_ID], [DAT_RITNR], [DAT_PAR_ID_DISC], [DAT_BAC], [DAT_EAC], [DAT_BCMR], [DAT_ECMR], [DAT_TYPE_ACTIVITEIT], [DAT_BEREKEN_STATUS], [DAT_REGISTRATIE_DATUM], [DAT_ACT_SOURCE_DATA], [DAT_BASSIRELATIE]) VALUES (10, 7, 9287904, 866, CAST(0x00009CBC0110BC50 AS DateTime), CAST(0x00009CBC0110BC50 AS DateTime), CAST(0x00009CBC010FE960 AS DateTime), CAST(0x00009CBC01140810 AS DateTime), 1, 10, CAST(0x00009CCD00AB7229 AS DateTime), N'Progress', N'dSBK00001106')
SET IDENTITY_INSERT [dbo].[DIGIACTIVITEIT] OFF
/****** Object: Table [dbo].[DIGITACHOTISRIJTIJD] Script Date: 11/26/2009 11:16:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[DIGITACHOTISRIJTIJD](
[DRT_ID] [int] IDENTITY(1,1) NOT NULL,
[WKN_ID] [int] NULL,
[KF_CHART_CODE] [int] NOT NULL,
[DRT_STARTTIME] [datetime] NOT NULL,
[DRT_STOPTIME] [datetime] NOT NULL,
[KF_ACT_DURATION] [int] NOT NULL,
[KF_ACT_TYPE] [int] NOT NULL,
[DRT_REGDATE] [datetime] NOT NULL,
CONSTRAINT [PK_DIGITACHORIJTIJD] PRIMARY KEY CLUSTERED
(
[DRT_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[DIGITACHOTISRIJTIJD] ON
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (1, 18, 87, CAST(0x00009CBC007B5250 AS DateTime), CAST(0x00009CBC0082BCC0 AS DateTime), 27, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (2, 18, 87, CAST(0x00009CBC0082BCC0 AS DateTime), CAST(0x00009CBC0084A8F0 AS DateTime), 7, 2, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (3, 18, 87, CAST(0x00009CBC0084A8F0 AS DateTime), CAST(0x00009CBC0086DB70 AS DateTime), 8, 4, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (4, 18, 87, CAST(0x00009CBC0086DB70 AS DateTime), CAST(0x00009CBC0087AE60 AS DateTime), 3, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (5, 18, 87, CAST(0x00009CBC0087AE60 AS DateTime), CAST(0x00009CBC008FEBC0 AS DateTime), 30, 4, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (6, 18, 87, CAST(0x00009CBC008FEBC0 AS DateTime), CAST(0x00009CBC00907860 AS DateTime), 2, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (7, 18, 87, CAST(0x00009CBC00907860 AS DateTime), CAST(0x00009CBC00970FE0 AS DateTime), 24, 2, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (8, 18, 87, CAST(0x00009CBC00970FE0 AS DateTime), CAST(0x00009CBC00982920 AS DateTime), 4, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (9, 18, 87, CAST(0x00009CBC00982920 AS DateTime), CAST(0x00009CBC0098FC10 AS DateTime), 3, 2, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (10, 18, 87, CAST(0x00009CBC0098FC10 AS DateTime), CAST(0x00009CBC00A85D90 AS DateTime), 56, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (11, 18, 87, CAST(0x00009CBC00A85D90 AS DateTime), CAST(0x00009CBC00A93080 AS DateTime), 3, 2, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (12, 18, 87, CAST(0x00009CBC00A93080 AS DateTime), CAST(0x00009CBC00AB6300 AS DateTime), 8, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (13, 18, 87, CAST(0x00009CBC00AB6300 AS DateTime), CAST(0x00009CBC00ABEFA0 AS DateTime), 2, 2, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (14, 18, 87, CAST(0x00009CBC00ABEFA0 AS DateTime), CAST(0x00009CBC00AD08E0 AS DateTime), 4, 1, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (15, 18, 87, CAST(0x00009CBC00AD08E0 AS DateTime), CAST(0x00009CBC00B00E50 AS DateTime), 11, 2, CAST(0x00009CCD00B97CAA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (16, 18, 87, CAST(0x00009CBC00B00E50 AS DateTime), CAST(0x00009CBC00B3A060 AS DateTime), 13, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (17, 18, 87, CAST(0x00009CBC00B3A060 AS DateTime), CAST(0x00009CBC00BB0AD0 AS DateTime), 27, 4, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (18, 18, 87, CAST(0x00009CBC00BB0AD0 AS DateTime), CAST(0x00009CBC00BCB0B0 AS DateTime), 6, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (19, 18, 87, CAST(0x00009CBC00BCB0B0 AS DateTime), CAST(0x00009CBC00BDC9F0 AS DateTime), 4, 2, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (20, 18, 87, CAST(0x00009CBC00BDC9F0 AS DateTime), CAST(0x00009CBC00D52280 AS DateTime), 85, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (21, 18, 87, CAST(0x00009CBC00D52280 AS DateTime), CAST(0x00009CBC00D94130 AS DateTime), 15, 4, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (22, 18, 87, CAST(0x00009CBC00D94130 AS DateTime), CAST(0x00009CBC00D98780 AS DateTime), 1, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (23, 18, 87, CAST(0x00009CBC00D98780 AS DateTime), CAST(0x00009CBC00DA1420 AS DateTime), 2, 2, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (24, 18, 87, CAST(0x00009CBC00DA1420 AS DateTime), CAST(0x00009CBC00E0F1F0 AS DateTime), 25, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (25, 18, 87, CAST(0x00009CBC00E0F1F0 AS DateTime), CAST(0x00009CBC00EA8EE0 AS DateTime), 35, 4, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (26, 18, 87, CAST(0x00009CBC00EA8EE0 AS DateTime), CAST(0x00009CBC00FC22E0 AS DateTime), 64, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (27, 18, 87, CAST(0x00009CBC00FC22E0 AS DateTime), CAST(0x00009CBC00FCF5D0 AS DateTime), 3, 2, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (28, 18, 87, CAST(0x00009CBC00FCF5D0 AS DateTime), CAST(0x00009CBC00FFFB40 AS DateTime), 11, 1, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (29, 18, 87, CAST(0x00009CBC00FFFB40 AS DateTime), CAST(0x00009CBC0103D3A0 AS DateTime), 14, 4, CAST(0x00009CCD00B97CAE AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (30, 18, 87, CAST(0x00009CBC0103D3A0 AS DateTime), CAST(0x00009CBC010419F0 AS DateTime), 1, 1, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (31, 18, 87, CAST(0x00009CBC010419F0 AS DateTime), CAST(0x00009CBC0104ECE0 AS DateTime), 3, 2, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (32, 18, 87, CAST(0x00009CBC0104ECE0 AS DateTime), CAST(0x00009CBC01060620 AS DateTime), 4, 1, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (33, 18, 87, CAST(0x00009CBC01060620 AS DateTime), CAST(0x00009CBC0107F250 AS DateTime), 7, 2, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (34, 18, 87, CAST(0x00009CBC0107F250 AS DateTime), CAST(0x00009CBC011A9F90 AS DateTime), 68, 1, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (35, 18, 87, CAST(0x00009CBC011A9F90 AS DateTime), CAST(0x00009CBC011BB8D0 AS DateTime), 4, 4, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (36, 18, 87, CAST(0x00009CBC011BB8D0 AS DateTime), CAST(0x00009CBC011BFF20 AS DateTime), 1, 1, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (37, 18, 87, CAST(0x00009CBC011BFF20 AS DateTime), CAST(0x00009CBC011E31A0 AS DateTime), 8, 4, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (38, 18, 87, CAST(0x00009CBC011E31A0 AS DateTime), CAST(0x00009CBC01236990 AS DateTime), 19, 1, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (39, 18, 87, CAST(0x00009CBC01236990 AS DateTime), CAST(0x00009CBC01236990 AS DateTime), 0, 5, CAST(0x00009CCD00B97CB3 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (40, 7, 230, CAST(0x00009CBC005CD5A0 AS DateTime), CAST(0x00009CBC0066FF30 AS DateTime), 37, 1, CAST(0x00009CCD00B97CB8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (41, 7, 230, CAST(0x00009CBC0066FF30 AS DateTime), CAST(0x00009CBC00681870 AS DateTime), 4, 2, CAST(0x00009CCD00B97CBD AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (42, 7, 230, CAST(0x00009CBC00681870 AS DateTime), CAST(0x00009CBC00685EC0 AS DateTime), 1, 1, CAST(0x00009CCD00B97CBD AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (43, 7, 230, CAST(0x00009CBC00685EC0 AS DateTime), CAST(0x00009CBC00742E30 AS DateTime), 43, 4, CAST(0x00009CCD00B97CC1 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (44, 7, 230, CAST(0x00009CBC00742E30 AS DateTime), CAST(0x00009CBC0074BAD0 AS DateTime), 2, 1, CAST(0x00009CCD00B97CC1 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (45, 7, 230, CAST(0x00009CBC0074BAD0 AS DateTime), CAST(0x00009CBC0076A700 AS DateTime), 7, 2, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (46, 7, 230, CAST(0x00009CBC0076A700 AS DateTime), CAST(0x00009CBC008462A0 AS DateTime), 50, 1, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (47, 7, 230, CAST(0x00009CBC008462A0 AS DateTime), CAST(0x00009CBC0084EF40 AS DateTime), 2, 2, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (48, 7, 230, CAST(0x00009CBC0084EF40 AS DateTime), CAST(0x00009CBC0095B050 AS DateTime), 61, 1, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (49, 7, 230, CAST(0x00009CBC0095B050 AS DateTime), CAST(0x00009CBC00968340 AS DateTime), 3, 2, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (50, 7, 230, CAST(0x00009CBC00968340 AS DateTime), CAST(0x00009CBC00A02030 AS DateTime), 35, 1, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (51, 7, 230, CAST(0x00009CBC00A02030 AS DateTime), CAST(0x00009CBC00A0F320 AS DateTime), 3, 2, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (52, 7, 230, CAST(0x00009CBC00A0F320 AS DateTime), CAST(0x00009CBC00A3F890 AS DateTime), 11, 1, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (53, 7, 230, CAST(0x00009CBC00A3F890 AS DateTime), CAST(0x00009CBC00A48530 AS DateTime), 2, 2, CAST(0x00009CCD00B97CC6 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (54, 7, 230, CAST(0x00009CBC00A48530 AS DateTime), CAST(0x00009CBC00A511D0 AS DateTime), 2, 1, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (55, 7, 230, CAST(0x00009CBC00A511D0 AS DateTime), CAST(0x00009CBC00A93080 AS DateTime), 15, 4, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (56, 7, 230, CAST(0x00009CBC00A93080 AS DateTime), CAST(0x00009CBC00B3A060 AS DateTime), 38, 1, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (57, 7, 230, CAST(0x00009CBC00B3A060 AS DateTime), CAST(0x00009CBC00B42D00 AS DateTime), 2, 2, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (58, 7, 230, CAST(0x00009CBC00B42D00 AS DateTime), CAST(0x00009CBC00B58C90 AS DateTime), 5, 4, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (59, 7, 230, CAST(0x00009CBC00B58C90 AS DateTime), CAST(0x00009CBC00B61930 AS DateTime), 2, 1, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (60, 7, 230, CAST(0x00009CBC00B61930 AS DateTime), CAST(0x00009CBC00B7BF10 AS DateTime), 6, 2, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (61, 7, 230, CAST(0x00009CBC00B7BF10 AS DateTime), CAST(0x00009CBC00C53460 AS DateTime), 49, 1, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (62, 7, 230, CAST(0x00009CBC00C53460 AS DateTime), CAST(0x00009CBC00CE44B0 AS DateTime), 33, 4, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (63, 7, 230, CAST(0x00009CBC00CE44B0 AS DateTime), CAST(0x00009CBC00D79B50 AS DateTime), 34, 1, CAST(0x00009CCD00B97CCA AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (64, 7, 230, CAST(0x00009CBC00D79B50 AS DateTime), CAST(0x00009CBC00D8B490 AS DateTime), 4, 2, CAST(0x00009CCD00B97CCF AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (65, 7, 230, CAST(0x00009CBC00D8B490 AS DateTime), CAST(0x00009CBC00D8FAE0 AS DateTime), 1, 1, CAST(0x00009CCD00B97CD4 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (66, 7, 230, CAST(0x00009CBC00D8FAE0 AS DateTime), CAST(0x00009CBC00E32470 AS DateTime), 37, 4, CAST(0x00009CCD00B97CD4 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (67, 7, 230, CAST(0x00009CBC00E32470 AS DateTime), CAST(0x00009CBC00E4CA50 AS DateTime), 6, 1, CAST(0x00009CCD00B97CD4 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (68, 7, 230, CAST(0x00009CBC00E4CA50 AS DateTime), CAST(0x00009CBC00E6B680 AS DateTime), 7, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (69, 7, 230, CAST(0x00009CBC00E6B680 AS DateTime), CAST(0x00009CBC00E74320 AS DateTime), 2, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (70, 7, 230, CAST(0x00009CBC00E74320 AS DateTime), CAST(0x00009CBC00E8A2B0 AS DateTime), 5, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (71, 7, 230, CAST(0x00009CBC00E8A2B0 AS DateTime), CAST(0x00009CBC00F58B60 AS DateTime), 47, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (72, 7, 230, CAST(0x00009CBC00F58B60 AS DateTime), CAST(0x00009CBC00F73140 AS DateTime), 6, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (73, 7, 230, CAST(0x00009CBC00F73140 AS DateTime), CAST(0x00009CBC01038D50 AS DateTime), 45, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (74, 7, 230, CAST(0x00009CBC01038D50 AS DateTime), CAST(0x00009CBC01053330 AS DateTime), 6, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (75, 7, 230, CAST(0x00009CBC01053330 AS DateTime), CAST(0x00009CBC011102A0 AS DateTime), 43, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (76, 7, 230, CAST(0x00009CBC011102A0 AS DateTime), CAST(0x00009CBC01118F40 AS DateTime), 2, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (77, 7, 230, CAST(0x00009CBC01118F40 AS DateTime), CAST(0x00009CBC01126230 AS DateTime), 3, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (78, 7, 230, CAST(0x00009CBC01126230 AS DateTime), CAST(0x00009CBC01133520 AS DateTime), 3, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (79, 7, 230, CAST(0x00009CBC01133520 AS DateTime), CAST(0x00009CBC011567A0 AS DateTime), 8, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (80, 7, 230, CAST(0x00009CBC011567A0 AS DateTime), CAST(0x00009CBC01163A90 AS DateTime), 3, 2, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (81, 7, 230, CAST(0x00009CBC01163A90 AS DateTime), CAST(0x00009CBC0122DCF0 AS DateTime), 46, 1, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (82, 7, 230, CAST(0x00009CBC0122DCF0 AS DateTime), CAST(0x00009CBC01243C80 AS DateTime), 5, 4, CAST(0x00009CCD00B97CD8 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (83, 7, 230, CAST(0x00009CBC01243C80 AS DateTime), CAST(0x00009CBC0124C920 AS DateTime), 2, 1, CAST(0x00009CCD00B97CDD AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (84, 7, 230, CAST(0x00009CBC0124C920 AS DateTime), CAST(0x00009CBC01259C10 AS DateTime), 3, 2, CAST(0x00009CCD00B97CDD AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (85, 7, 230, CAST(0x00009CBC01259C10 AS DateTime), CAST(0x00009CBC012628B0 AS DateTime), 2, 1, CAST(0x00009CCD00B97CE2 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (86, 7, 230, CAST(0x00009CBC012628B0 AS DateTime), CAST(0x00009CBC0126FBA0 AS DateTime), 3, 2, CAST(0x00009CCD00B97CE2 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (87, 7, 230, CAST(0x00009CBC0126FBA0 AS DateTime), CAST(0x00009CBC0127CE90 AS DateTime), 3, 1, CAST(0x00009CCD00B97CE2 AS DateTime))
INSERT [dbo].[DIGITACHOTISRIJTIJD] ([DRT_ID], [WKN_ID], [KF_CHART_CODE], [DRT_STARTTIME], [DRT_STOPTIME], [KF_ACT_DURATION], [KF_ACT_TYPE], [DRT_REGDATE]) VALUES (88, 7, 230, CAST(0x00009CBC0127CE90 AS DateTime), CAST(0x00009CBC0127CE90 AS DateTime), 0, 5, CAST(0x00009CCD00B97CE2 AS DateTime))
SET IDENTITY_INSERT [dbo].[DIGITACHOTISRIJTIJD] OFF
/****** Object: Default [DF_DIGIACTIVITEIT_DAT_BEREKEN_STATUS] Script Date: 11/26/2009 11:16:16 ******/
ALTER TABLE [dbo].[DIGIACTIVITEIT] ADD CONSTRAINT [DF_DIGIACTIVITEIT_DAT_BEREKEN_STATUS] DEFAULT ((10)) FOR [DAT_BEREKEN_STATUS]
GO
/****** Object: Default [DF_DIGIACTIVITEIT_DAT_REGISTRATIE_DATUM] Script Date: 11/26/2009 11:16:16 ******/
ALTER TABLE [dbo].[DIGIACTIVITEIT] ADD CONSTRAINT [DF_DIGIACTIVITEIT_DAT_REGISTRATIE_DATUM] DEFAULT (getdate()) FOR [DAT_REGISTRATIE_DATUM]
GO
/****** Object: Default [DF_DIGITACHOTISRIJTIJD_DRT_REGDATE] Script Date: 11/26/2009 11:16:16 ******/
ALTER TABLE [dbo].[DIGITACHOTISRIJTIJD] ADD CONSTRAINT [DF_DIGITACHOTISRIJTIJD_DRT_REGDATE] DEFAULT (getdate()) FOR [DRT_REGDATE]
GO
The calculation has to be based on: The DAY of the activities compared to the DAY of the drivingtime (witch i provide as a parameter)
and the WKN_ID (driver id) witch is also a parameter
In this case they would be : WKN_ID = 7 and Day=2009-11-09
The calculation have to be done for several WKN_ID a day but for simplicity (witch i really need when dates are into game) i have limited the data to 2 drivers and 1 day, so we can see its only taken into account WKN_ID:7
The total drivingtime for this driver = 530 minutes
SELECT SUM(KF_ACT_DURATION) AS total FROM dbo.DIGITACHOTISRIJTIJD WHERE KF_ACT_TYPE = 1 AND WKN_ID = 7
witch seems to match with the real TachoGraph Data, so al the drivetimes are present for this driver
What we need to see is:
how many minutes have been deleted for driven while in activity
What is the new total drivingtime that will be payed for.
Your help is really appreciated to solve this puzzle
Wkr,
Eddy
-
I've had a quick look at this, I think that when the total drive time becomes zero, such when
an activity completely overlaps the drivetime, the drivetime isn't displayed.
You can see this in your original query by changing
SELECT 3, '2009-11-24 07:40', '2009-11-24 09:00',80
to
SELECT 3, '2009-11-24 07:40', '2009-11-24 19:00',80
This can be fixed by changing the query to this
WITH Starts(drtid,starttime) AS (SELECT b.drtid,
b.starttime
FROM @tDriveTimes b
WHERE NOT EXISTS(SELECT * FROM @tActivities a
WHERE b.starttime BETWEEN a.astarttime AND a.astoptime)
UNION ALL
SELECT b.drtid,
a.astoptime
FROM @tActivities a
INNER JOIN @tDriveTimes b ON a.astoptime >= b.starttime
AND a.astoptime < b.endtime),
Ends(drtid,endtime) AS (
SELECT b.drtid,
b.endtime
FROM @tDriveTimes b
WHERE NOT EXISTS(SELECT * FROM @tActivities a
WHERE b.endtime BETWEEN a.astarttime AND a.astoptime)
UNION ALL
SELECT b.drtid,
a.astarttime
FROM @tActivities a
INNER JOIN @tDriveTimes b ON a.astarttime > b.starttime
AND a.astarttime <= b.endtime),
StartAndEnds(drtid,starttime,endtime) AS (
SELECT a.drtid,
a.starttime,
MIN(b.endtime)
FROM Starts a
INNER JOIN Ends b ON b.drtid=a.drtid AND b.endtime>a.starttime
GROUP BY a.drtid,a.starttime)
SELECT a.drtid,
a.starttime,
a.endtime,
DATEDIFF(minute,a.starttime,a.endtime) AS originalTime,
DATEDIFF(minute,a.starttime,a.endtime) -
COALESCE(SUM(DATEDIFF(minute,s.starttime,s.endtime)),0) AS deletedTime,
COALESCE(SUM(DATEDIFF(minute,s.starttime,s.endtime)),0) AS newDriveTime
FROM @tDriveTimes a
LEFT OUTER JOIN StartAndEnds s ON s.drtid=a.drtid
GROUP BY a.drtid,a.starttime,a.endtime
ORDER BY a.drtid;
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Mark,
THANK YOU VERRY VERRY MUCH!
This returns the correct numbers,
I have manualy checked every record in the provided detail and calculated myself first then referenced to the results and they match 100%
Now i can continue to write the logic to handle a driver a time for a certain day,
I know it is possible to do them al at ones but there are some other restrictions that i have to calculate on some drivers (based on the sort of work they did on that day) before i can build the calculation table.
Tnx again for al your help,
If we meet someday, be sure to receive a lot of beer/cocktails or whatever you like to drink.
You have saved me from days of headache in this case :hehe:
Still one little question:
I have now the deleted time, witch is great for the nex calculations.
But can it be done to have also as a result: the total deleted time based on the Activity (actid from @tActivities) instead of based on the drivingtime (drtid from @tDriveTimes)
Cause when a driver comes for some explanation, he or she will want to see witch times have been subtracted for some ammount based on an activity that was busy, and thats what the driver knows, witch activity have a done on a certain date.
But in my result i can not tell witch activity was the offending one that lead to the subtraction.
Its just like some other view on it.
Wkr,
Eddy
-
Thanks for the feedback. One thing I noticed however, is that the query may not produce correct results if the activities overlap. It can be fixed, but may not be a part of your requirements.
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
Hi Mark,
Actually i should need to detect wheter there is some kinda overlap,
If it is (true/false) then i can not proceed with the calculation but give some message back to the calling application.
Is it easy to detect overlaps .?
wkr,
Eddy
-
This will give you a list of all overlapping activities
SELECT a.actid,b.actid AS Overlapped_actid
FROM @tActivities a
INNER JOIN @tActivities b ON b.actid>a.actid
AND a.astarttime<b.astoptime
AND b.astarttime<a.astoptime
ORDER BY a.actid,b.actid
____________________________________________________
Deja View - The strange feeling that somewhere, sometime you've optimised this query before
How to get the best help on a forum
http://www.sqlservercentral.com/articles/Best+Practices/61537 -
tnx Mark
This idd did the trick 🙂
Seems to me that this puzzle has been solved.
tnx a lot,
Wkr,
Eddy
Viewing 11 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply