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

#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, 1 month ago by Timm.
  • This reply was modified 5 years, 1 month ago by Timm.
  • This reply was modified 5 years, 1 month ago by Timm.
  • This reply was modified 5 years, 1 month ago by Timm.