Reply To: SQL: Datediff nur in POB-ServiceHours

Welcome to the POB User Group Online Community! Forums PUG DACH SQL: Datediff nur in POB-ServiceHours Reply To: SQL: Datediff nur in POB-ServiceHours

#3326
Stefan Reichelt
Participant

Aktueller Stand: Ich habe eine funktionierende Formel (sogar ohne Loops), aber die Performance ist lausig. Als SQl-Funktion kann ich es ganz vergessen (braucht ewig; nach einer solchen Spalte sortieren geht gar nicht), als ausgelagerte View ist es gerade noch benutzbar. Damit kann ich das reine Alter eines Falles ohne Berücksichtigung der Aussetzungen anzeigen. Wenn ich aber nun die gleiche Berechnung nochmal für die Aussetzungszeilen des Falles durchführe, um das Ergebnis vom errechneten Alter abzuziehen, sind die Zeiten inakzeptabel. Das geht in der Applikation gefühlt schneller, wenn die Summierungsfelder aktualisiert werden. Mal sehen, ob ich mich dem Verhalten der Applikation weiter annähern kann.

Mein bisheriges Ergebnis als Funktion:

USE [POBG6_DB]
GO

/****** Object:  UserDefinedFunction [dbo].[POBDateDiffSH_m]    Script Date: 15.03.2016 13:39:16 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

-- =============================================
-- Author:		Stefan Reichelt
-- Create date: 2016-03-15
-- Description:	Returns a datediff value in POB worktime minutes
-- =============================================
CREATE FUNCTION [dbo].[POBDateDiffSH_m]
(
	@StartDate datetime, @EndDate datetime, @ServiceHoursPK int
)
RETURNS int
WITH EXECUTE AS CALLER
AS
BEGIN
	DECLARE @POBDateDiffSH_m int;
	SET @POBDateDiffSH_m =	
	(
	select 
SUM(
DATEDIFF(MINUTE,
	CONCAT(convert(nvarchar(10),cal.day_date,120),' ',
	iif(@StartDate > CONCAT(convert(nvarchar(10),@StartDate,120), ' ', RIGHT(convert(nvarchar(20),tt.start_time_1,120),8)) and
		@StartDate < CONCAT(convert(nvarchar(10),@StartDate,120), ' ', RIGHT(convert(nvarchar(20),tt.end_time_1,120),8)) and
		convert(nvarchar(10),@startdate,120)=convert(nvarchar(10),cal.day_date,120),
		RIGHT(convert(nvarchar(20),@StartDate,120),8),
		RIGHT(convert(nvarchar(20),tt.start_time_1,120),8)
	)), 
	CONCAT(convert(nvarchar(10),cal.day_date,120),' ',
	iif(@EndDate <= CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.end_time_1,120),8)) and
		@StartDate < CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.end_time_1,120),8)),
		RIGHT(convert(nvarchar(20),@EndDate,120),8),
		iif(@StartDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.end_time_1,120),8)),
			RIGHT(convert(nvarchar(20),tt.start_time_1,120),8),
			RIGHT(convert(nvarchar(20),tt.end_time_1,120),8))
	)) 
))
+
SUM(
DATEDIFF(MINUTE,
	CONCAT(convert(nvarchar(10),cal.day_date,120),' ',
		iif(@EndDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', isnull(RIGHT(convert(nvarchar(20),tt.start_time_2,120),8),'23:59:59')) and
			@EndDate < CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', isnull(RIGHT(convert(nvarchar(20),tt.end_time_2,120),8),'23:59:59')),
			isnull(RIGHT(convert(nvarchar(20),@StartDate,120),8),'00:00:00'),
			iif(@StartDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', isnull(RIGHT(convert(nvarchar(20),tt.start_time_2,120),8),'23:59:59')),
			isnull(RIGHT(convert(nvarchar(20),@StartDate,120),8),'00:00:00'),
			iif(@EndDate < CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', isnull(RIGHT(convert(nvarchar(20),tt.start_time_2,120),8),'23:59:59')),
				'00:00:00',
				isnull(RIGHT(convert(nvarchar(20),tt.start_time_2,120),8),'00:00:00')))
	)),
	CONCAT(convert(nvarchar(10),cal.day_date,120),' ',
	iif(@EndDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.start_time_2,120),8)) and
		@EndDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.end_time_2,120),8)),
		isnull(RIGHT(convert(nvarchar(20),tt.end_time_2,120),8),'00:00:00'),
		iif(@EndDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.end_time_2,120),8)),
			isnull(RIGHT(convert(nvarchar(20),tt.end_time_2,120),8),'00:00:00'),
			iif(@EndDate < CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', isnull(RIGHT(convert(nvarchar(20),tt.end_time_2,120),8),'23:59:59')),
				'00:00:00',
				RIGHT(convert(nvarchar(20),@EndDate,120),8)))
	)) 
))
from Calendar cal with(nolock)
inner join Day_Type dt with(nolock) on cal.day_type_pk=dt.day_type_pk
inner join Time_Table tt with(nolock) on cal.day_type_pk=tt.day_type_pk
where 
cal.id between CONVERT(nvarchar(8),@StartDate,112) and CONVERT(nvarchar(8),@EndDate,112)
and tt.service_hours_pk=@ServiceHoursPK
and tt.start_time is not null
and @EndDate > CONCAT(convert(nvarchar(10),cal.day_date,120), ' ', RIGHT(convert(nvarchar(20),tt.start_time_1,120),8))
	);
	RETURN (@POBDateDiffSH_m);
END

GO