SQL: Datediff nur in POB-ServiceHours

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

Tagged: 

Viewing 5 posts - 1 through 5 (of 5 total)
  • Author
    Posts
  • #3313
    Stefan Reichelt
    Participant

    Hallo allerseits,

    ich habe auch mal wieder eine knifflige Frage:
    Ich muss sehr öft in Berichten etc. Zeiten pro Ticket summieren. Nun komme ich nicht mehr um eine Nachbildung der POB-Logik herum, bei der nur die Zeiträume innerhalb der per Service Hours vorgegebenen Arbeitszeiten berücksichtigt werden dürfen.

    Ergo: Am Ticket sind die Bürozeiten 8-18 Uhr Montag bis Freitag hinterlegt. Feiertage sind arbeitsfrei. Nun gilt es auszurechnen, wie viel Arbeitszeit seit Eröffnung des Tickets vergangen ist. Ich brauche also eine SQL-Anweisung, die quasi wie DateDiff arbeitet, aber über die Kalendertabellen nur jene Zeiten verwendet, die sich nicht an einem arbeitsfreien Tag und nur innerhalb der Bürozeiten befinden. Ich bekomme es bestimmt noch hin, mir eine Ergebnismenge zu verschaffen, die alle betroffenen Arbeitstage und deren Arbeitszeiten auflistet. Aber wie summiere ich diese Zeiten dann sequentiell auf?

    #3314
    Timm
    Member

    Moin Stefan,

    Ich habe einen Change dazu laufen, das bitte eine Funktion aufgenommen wird dies genau das ermöglicht (wahlweise mit Aussetzung oder ohne) …

    Wir haben diese Logik auf unserem Reporting Server implementiert, da diese Angabe innerhalb von PoB nicht weiter verwendet wird.

    Vlt. kann ja Christian mal was zum RfC sagen: 127726 Worktime – Function

    Du hättest noch die Möglichkeit dir diese Berechnung als Custom SQL Function bauen zu lassen ?

    Ich habe mal für den Fall das du die SQL Variante nehmen willst meine MySQL Function angefügt für MSSQL müsste diese entsprechend angepasst werden:

    BEGIN
    /*
    
    created by: 	T. Abraham 
    updated: 		2014-11-11
    comment:		WorkTime Calculation
    
    */ 
    
        DECLARE DayInPeriod INT;
    	DECLARE DaysInExc INT;
    	DECLARE WTSecs INT;
    	DECLARE debug_message VARCHAR(32);
    	DECLARE DayWTStart TIME;
    	DECLARE DayWTEnd TIME;
    	DECLARE d1 DATE;
    	DECLARE d2 DATE;
    	DECLARE t1 TIME;
    	DECLARE t2 TIME;
    
    	SET d1 = cast(StartTimestamp as DATE);
    	SET d2 = cast(EndTimestamp as DATE);
    
    	SET t1 = cast(StartTimestamp as TIME);
    	SET t2 = cast(EndTimestamp as TIME);
    
        SET DayInPeriod=0;
    	SET DaysInExc=0;
    
    	#WHILE Loop für jeden Tag
        WHILE d1<=d2 DO
    
        # Prüfen ob der Tag per Ausnahme behandelt wird
    	IF d1 NOT IN (SELECT day FROM csd_global.all_teams_exception_days WHERE team_id = tid AND exc_open = 0) THEN
     
    		# Prüfen ob der Tag per DOW geöffnet ist
    		IF DAYOFWEEK(d1) IN (SELECT day_of_week FROM csd_global.all_teams_opentimes WHERE team_id = tid AND dow_open = 1) THEN
    
    		#Holen von Start und Ende
    		SET DayWTStart 	= (SELECT dow_start FROM csd_global.all_teams_opentimes WHERE team_id = tid AND day_of_week = DAYOFWEEK(d1));
    		SET DayWTEnd 	= (SELECT dow_end 	FROM csd_global.all_teams_opentimes WHERE team_id = tid AND day_of_week = DAYOFWEEK(d1));
    
    			#Der erste Tag ?
    			IF DayInPeriod = 0 THEN
    
    				#Tag1 kleiner Tag2 (Mehrere Tage) ?
    				IF d1 < d2 THEN
     
    					#Tag1 Beginn innerhalb der Servicezeiten
    					IF  t1 <= DayWTEnd AND t1 >= DayWTStart THEN SET WTSecs = TIMESTAMPDIFF(SECOND,t1,DayWTEnd); END IF;
    
    					#Tag1 Beginn vor der Servicezeit
    					IF  t1 <= DayWTStart THEN SET WTSecs = TIMESTAMPDIFF(SECOND,DayWTStart,DayWTEnd); END IF;
    
    					#Tag1 Beginn ist größer als Arbeitsende
    					IF  t1 >= DayWTEnd THEN SET WTSecs = 0; END IF;
    
    				ELSE
    					#Erster Tag ist auch der letze Tag (Ein Tag)
    
    					#Tag Beginn innerhalb der Servicezeiten
    					IF  t1 <= DayWTEnd AND t1 >= DayWTStart AND t1 < t2 THEN SET WTSecs = TIMESTAMPDIFF(SECOND,t1,t2); END IF;
    
    					#Tag Beginn vor der Servicezeit
    					IF  t1 <= DayWTStart AND t2 >= DayWTStart THEN SET WTSecs = TIMESTAMPDIFF(SECOND,DayWTStart,t2); END IF;
    
    					#Tag Beginn und Ende sind kleiner als Arbeitsbeginn
    					IF  t1 <= DayWTStart AND t2 <= DayWTStart  THEN SET WTSecs = 0; END IF;
    
    					#Tag Beginn ist größer als Arbeitsende
    					IF  t1 >= DayWTEnd THEN SET WTSecs = 0; END IF;
    
    				END IF;
    
    			#Jeder geöffnete Tag der nicht der erste ist
    			ELSE
    				
    			#Letzter Tag
    			IF 	d1 = d2 THEN
    			IF  t2 <= DayWTEnd AND t2 >= DayWTStart THEN SET WTSecs = WTSecs + TIMESTAMPDIFF(SECOND,DayWTStart,t2); END IF;
    			IF  t2 > DayWTEnd  AND t2 >= DayWTStart THEN SET WTSecs = WTSecs + TIMESTAMPDIFF(SECOND,DayWTStart,DayWTEnd); END IF;
    			
    			#Jeder geöffnete Tag der nicht der erste Tag und nicht der letzte Tag ist
    			ELSE
    			SET WTSecs = WTSecs + TIMESTAMPDIFF(SECOND,DayWTStart,DayWTEnd); 
    			END IF;
    
    			END IF;
    
    		#ENDE DOW ist geöffnet
    		
    		END IF;
    
    	#ENDE Tag ist KEINE Ausnahme
    	END IF;
    
    	#Nächster Tag	
    	SET d1=d1+INTERVAL 1 day;
    	SET DayInPeriod=DayInPeriod+1;
        END WHILE;
    
        RETURN WTSecs;
    END
    • This reply was modified 5 years, 6 months ago by Timm.
    • This reply was modified 5 years, 6 months ago by Timm.
    • This reply was modified 5 years, 6 months ago by Timm.
    • This reply was modified 5 years, 6 months ago by Timm.
    #3319
    Stefan Reichelt
    Participant

    Danke, das hilft mir sicher erstmal weiter. Ich schau mal, ob ich das in T-SQL übersetzt bekomme.
    In POB direkt brauche ich die Funktion gar nicht; eigentlich nur im Rahmen einer berechneten virtuellen Spalte, also als reine Anzeige. Deshalb genügt SQL vollkommen.

    #3323
    ms
    Keymaster

    Ich habe die Berechnung mal mit C# nachgebaut und über eine CLR Function in den SQL Server integriert. Über CLR könnt ihr ganz praktisch selbstgeschrieben C# Klassen einbinden und dann inkl. Parameter in einem SQL SELECT aufrufen.

    https://msdn.microsoft.com/en-us/library/w2kae45k%28v=vs.90%29.aspx

    Ich hatte damals die Verfügbarkeit von CIs und SLAs berechnet.

    PS: Bzgl. dem Change Request werde ich nachhacken.

    #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
    
Viewing 5 posts - 1 through 5 (of 5 total)
  • You must be logged in to reply to this topic.