nixverstehen
Goto Top

Berechnung Nachtzulage aus Daten einer CSV-Datei

Guten Morgen zusammen,

in unserem kleinen Transportunternehmen zeichnen die LKW-Fahrer ihre Arbeitszeiten noch wie vor 100 Jahren auf einem monatlichen Stundenblatt auf. Natürlich ist das nicht mehr zeitgemäß. Vor allem die Berechnung der Zuschläge für Nachtarbeit bereitet hier einen hohen Arbeitsaufwand, da unsere Damen in der Lohnbuchhaltung die Zuschläge komplett händisch berechnen müssen. Hier mal ein Blankomuster des Stundenblattes:

stundenblatt_muster

Eigentlich könnte man das automatisieren. Als Transportunternehmen sind wir ja verpflichtet, die Daten der Fahrerkarten unserer Fahrer als auch die Massenspeicher der LKW regelmäßig auszulesen und digital zu archivieren. Das wird auch getan und klappt problemlos. Die dort hinterlegten Daten entsprechen den Arbeitszeiten der Mitarbeiter. Ich habe die Möglichkeit, die Daten sehr einfach aus dieser Anwendung in eine CSV-Datei je Mitarbeiter zu exportieren. Das sieht da so aus:

tachodaten_csv

Die Spalten sind vom Anbieter der webbasierten Anwendung (DAKO) vorgegeben und können nicht geändert werden. Aus den Spalten Datum, Beginn und Ende in der CSV-Datei benötige ich automatisch berechnet, wie im ersten Bild dargestellt, die Werte der Spalten 10 bis 12:

1. Stunden im Dezimalformat für die Zeit von 20:00 Uhr bis 00:00 Uhr. (Zulage 25%)
2. Stunden im Dezimalformat für die Zeit von 00:00 Uhr bis 04:00 Uhr. (Zulage 40%, wenn vor 00:00 Uhr Arbeitsanfang, ansonsten 25%)
3. Stunden im Dezimalformat für die Zeit von 04:00 Uhr bis 06:00 Uhr. (Zulage 25%)
4. Summe der Stunden für die Zulage 25% aus 1. bis 3. im Dezimalformat
5. Summe der Stunden für die Zulage 40% aus 2. im Dezimalformat

Die automatisierte Berechnung sollte dann ein Format haben, das ich ausdrucken kann.

Bei der Umsetzung bin ich relativ offen, d.h. die Lösung kann eine Excel-Berechnungstabelle sein, in die man die notwendigen Werte aus der CSV-Datei mit einem Klick rein kopiert oder eine komplette Anwendung in welcher Form auch immer. Natürlich bin ich auch bereit, die Aufgabe an einen Dienstleister zu vergeben und dafür eine angemessene Summe auszugeben. Das rechnet sich ja durchaus durch die Einsparung von Arbeitszeit in der Lohnbuchhaltung. Bei 40 Fahrern mit Nachtzulage x 10 Min. Bearbeitungszeit je Liste sind das ca. 6,5 Std. Arbeitszeit je Monat.

Wie würdet ihr an die Sache herangehen? Ist das in Excel realisierbar? Oder wäre da ein anderer Ansatz besser?

Gruß und einen schönen Freitag
Arno

Content-Key: 1246107788

Url: https://administrator.de/contentid/1246107788

Printed on: February 29, 2024 at 08:02 o'clock

Member: ukulele-7
ukulele-7 Sep 10, 2021 at 07:14:26 (UTC)
Goto Top
Ich würde es mit SQL machen weil ich mag SQL face-wink Aber das macht natürlich nur Sinn wenn man diese Excel Berichte an irgendeinem Punkt importieren kann oder liegen die Daten schon in SQL und du hast auch DB Zugriff?
Member: NixVerstehen
NixVerstehen Sep 10, 2021 at 07:40:39 (UTC)
Goto Top
Zitat von @ukulele-7:

Ich würde es mit SQL machen weil ich mag SQL face-wink Aber das macht natürlich nur Sinn wenn man diese Excel Berichte an irgendeinem Punkt importieren kann oder liegen die Daten schon in SQL und du hast auch DB Zugriff?

@ukulele-7: Die Daten werden im Rechenzentrum des Anbieters (DAKO in Jena, tachoweb.eu) gespeichert, vermutlich in einer SQL-Datenbank. Direkt an die SQL-Datenbank kommt man natürlich nicht dran. Wir greifen über die Internet-Plattform auf die Daten zu und haben nur die Möglichkeit, diese als CSV-Datei (wie im Bild 2) herunterzuladen.
Member: em-pie
em-pie Sep 10, 2021 at 08:11:45 (UTC)
Goto Top
Moin,
Zitat von @NixVerstehen:
Zitat von @ukulele-7:
Ich würde es mit SQL machen weil ich mag SQL face-wink Aber das macht natürlich nur Sinn wenn man diese Excel Berichte an irgendeinem Punkt importieren kann oder liegen die Daten schon in SQL und du hast auch DB Zugriff?
@ukulele-7: Die Daten werden im Rechenzentrum des Anbieters (DAKO in Jena, tachoweb.eu) gespeichert, vermutlich in einer SQL-Datenbank. Direkt an die SQL-Datenbank kommt man natürlich nicht dran. Wir greifen über die Internet-Plattform auf die Daten zu und haben nur die Möglichkeit, diese als CSV-Datei (wie im Bild 2) herunterzuladen.

Und was spricht dagegen, dass deine Damen (oder Herren) die CSV herunterladen und in einem definierten Ordner abspeichern? Du kannst dir dann die Datei schnappen und via PS-Script in eine DB schieben.
Ab dann brauchst du ja nur noch eine "GUI". Ob es ein Excel, Access oder eine Website ist, ist ja egal.
Die Logik baust du dort ein und bist fertig.

Irgendein Script wirst du ja ohnehin benötigen, um die CSV ins Excel zu schieben...

Gruß
em-pie
Member: TheJoker2305
TheJoker2305 Sep 10, 2021 at 08:25:24 (UTC)
Goto Top
Hallo zusammen,

es freut mich dass ich mit dem Thema nicht allein bin. Auch ich bin in einem Transportunternehmen als Datenbankprogrammierer neben vielen anderen Themen auch mit den Nachtzuschlägen in Berührung gekommen.

Ich habe dies auch über eine Datenbank gelöst - ok, die Ursprungsdaten kommen schon digital an, aber die Nachtzuschläge müssen ja trotzdem berechnet werden.

Für dich heißt es als ersten Schritt die Daten in eine Datenbank bekommen. Eine kleine lokale MS Express DB sollte dafür reichen.
Der einfachheit halber habe ich mir das Startdatum und die Startzeit sowie das Enddatum und die Endzeit jeweils in ein Feld zusammengeszogen:

Mitarbeiter Beginn Ende
Max Mustermann 02.01.2021 17:40 02.01.2021 05:20

Wenn die Datenbanktabelle so aussieht, kannst du direkt mit einer Abfrage alle Daten abziehen:

with cte as (

select 
	beginn, ende,
	personalnummer psnr,
	a.name + ', ' + a.vorname mitarbeiter,  
	convert(date,beginn) datum,
	case when convert(date, beginn) < convert(date,ende) and convert(time,ende) > convert(time,'20:00:00') then dateadd(day,1,convert(date,beginn)) end datum2,  

	datediff(minute,beginn,ende) praesenzzeit,
			
	case when convert(time,beginn) <= convert(time,'04:00:00') and convert(time,ende) >= convert(time,'00:00:00')  and convert(time,ende) > convert(time,'04:00:00') then   
			datediff(minute,beginn,dateadd(hour,4,convert(datetime,convert(date,beginn)))) else 0 end NZ_0_4_A,
	case when convert(time,beginn) <= convert(time,'04:00:00') and convert(time,ende) >= convert(time,'00:00:00')  and convert(time,ende) <= convert(time,'04:00:00') then   
			datediff(minute,beginn,ende) else 0  end NZ_0_4_B,
	
	case when convert(time,beginn) <= convert(time,'05:00:00') and convert(time,ende) > convert(time,'04:00:00')  and convert(time,beginn)  >= convert(time,'04:00:00') and convert(time,ende) <= convert(time,'05:00:00') then  
			datediff(minute,beginn,ende) else 0  end NZ_4_5_A,
	case when convert(time,beginn) <= convert(time,'05:00:00') and convert(time,ende) > convert(time,'04:00:00')  and convert(time,beginn) < convert(time,'04:00:00') and convert(time,ende) < convert(time,'05:00:00') then  
			datediff(minute,convert(datetime,'04:00:00'),convert(datetime,convert(time,ende))) else 0  end NZ_4_5_B,			    
	case when convert(time,beginn) <= convert(time,'05:00:00') and convert(time,ende) > convert(time,'04:00:00')  and convert(time,beginn) >= convert(time,'04:00:00') and convert(time,ende) > convert(time,'05:00:00')   then  
			datediff(minute,convert(datetime,convert(time,beginn)),convert(datetime,'05:00:00')) else 0 end NZ_4_5_C,			    
	case when convert(time,beginn) <= convert(time,'05:00:00') and convert(time,ende) > convert(time,'04:00:00')  and convert(time,beginn) < convert(time,'04:00:00') and convert(time,ende) > convert(time,'05:00:00')   then  
			datediff(minute,convert(datetime,'04:00:00'),convert(datetime,'05:00:00')) else 0 end NZ_4_5_D,	  
    
	case when convert(date, beginn) = convert(date,ende) and convert(time,beginn) < convert(time,'20:00:00') and convert(time,ende) > convert(time,'20:00:00') then  
			datediff(minute,convert(time,'20:00:00'),convert(time,ende)) else 0 end NZ_20_0_A,  
	case when convert(date, beginn) = convert(date,ende) and convert(time,beginn) <= convert(time,'20:00:00') and convert(time,ende) > convert(time,'20:00:00') then  
			datediff(minute,convert(time,'20:00:00'),convert(time,ende)) else 0 end NZ_20_0_B,  
	case when convert(date, beginn) < convert(date,ende) and convert(time,beginn) >= convert(time,'20:00:00') and convert(time,ende) < convert(time,'20:00:00') then  
			datediff(minute,convert(time,beginn),dateadd(hour,4,convert(datetime,convert(time,'20:00:00')))) else 0 end NZ_20_0_C,  
	case when convert(date, beginn) < convert(date,ende) and convert(time,beginn) < convert(time,'20:00:00') and convert(time,ende) < convert(time,'20:00:00') then  
			datediff(minute,dateadd(hour,20,convert(datetime,convert(date,beginn))),convert(datetime,convert(date,ende))) else 0 end NZ_20_0_D,
	case when convert(date, beginn) < convert(date,ende) and convert(time,beginn) < convert(time,ende) and convert(time,beginn) < convert(time,'20:00:00') then   
			datediff(minute,dateadd(hour,20,convert(datetime,convert(date,beginn))), dateadd(hour,24,convert(datetime,convert(date,beginn)))) else 0 end NZ_20_0_E,

	case when convert(date, beginn) < convert(date,ende) and convert(time,ende) <= convert(time,'04:00:00') then   
			datediff(minute,convert(time,'00:00:00'),convert(time,ende)) else 0 end NZ2_0_4_A,  
	case when convert(date, beginn) < convert(date,ende) and convert(time,ende) > convert(time,'04:00:00') then   
			datediff(minute,convert(time,'00:00:00'),convert(time,'04:00:00')) else 0 end NZ2_0_4_B,  

	case when convert(date, beginn) < convert(date,ende) and convert(time,ende) >= convert(time,'04:00:00')  and convert(time,ende) <= convert(time,'05:00:00') then   
			datediff(minute,convert(datetime,'04:00:00'),convert(datetime,convert(time,ende))) else 0 end NZ2_4_5_A,  
	case when convert(date, beginn) < convert(date,ende) and convert(time,ende) >= convert(time,'04:00:00')  and convert(time,ende) > convert(time,'05:00:00') then   
			datediff(minute,convert(datetime,'04:00:00'),convert(datetime,'05:00:00')) else 0  end NZ2_4_5_B,  

	case when convert(date, beginn) < convert(date,ende) and convert(time,ende) > convert(time,'20:00:00') then  
			datediff(minute,convert(time,'20:00:00'),convert(time,ende)) else 0 end NZ2_20_0_x  

from 
import_rohdaten_zeiten a
left join [dbo].[v_stammdaten_ma] b on a.personalnummer = b.persnr2
where convert(date,beginn) >= b.eintritt and (convert(date,beginn) <= b.austritt or b.austritt is null)

) 

Sieht viel aus - ist es auch. Die Zielstellung war, die jeweiligen Nachtzuschläge dem richtigen Tag ( dem Beginntag oder den Ende Tag, je nacheem wo die Schicht liegt - da wir ständig wechselnde Schichten haben) zuzuordnen.

Die Felder mit einem A am Ende des Namens stehen für den ersten Tag, B für den zweiten Tag.

ich habe dies über eine Common Table Expression zusammengefasst und dann hiermit summiert:

select 
	convert(date,datum) datum,
	psnr,
	mitarbeiter,
	sum(praesenzzeit) praesenzzeit,
	sum(NZ_04) as NZ_04,
	sum(NZ_45) as NZ_45,
	sum(NZ_200) as NZ_200
	from (
select
	convert(date,datum) datum,
	psnr,
	mitarbeiter,
	sum(praesenzzeit) praesenzzeit,
	sum(NZ_0_4_A + NZ_0_4_B) NZ_04,
	sum(NZ_4_5_A + NZ_4_5_B + NZ_4_5_C + NZ_4_5_D) NZ_45,
	sum(NZ_20_0_A + NZ_20_0_B + NZ_20_0_C + NZ_20_0_D) NZ_200
from cte
where datum is not null

group by convert(date,datum), psnr,
	mitarbeiter,
	praesenzzeit
union 
select 
	case when NZ2_0_4_A > 0 or NZ2_0_4_A > 0 or NZ2_4_5_A > 0 or NZ2_4_5_B > 0 or NZ2_20_0_x > 0 then convert(date,dateadd(day,1,datum)) else convert(date,datum) end as datum,
	psnr,
	mitarbeiter,
	0 praesenzzeit,
	sum(NZ2_0_4_A + NZ2_0_4_B) NZ_04,
	sum(NZ2_4_5_A + NZ2_4_5_B) NZ_45,
	sum(NZ2_20_0_x) NZ_200
from cte

group by case when NZ2_0_4_A > 0 or NZ2_0_4_A > 0 or NZ2_4_5_A > 0 or NZ2_4_5_B > 0 or NZ2_20_0_x > 0 then dateadd(day,1,datum) else datum end, psnr,mitarbeiter
) c

group by c.datum,
	c.psnr,
	c.mitarbeiter

Geht auch alles bestimmt einfacher, schlanker, komprimierter, aber mir war es erstmal wichtig, dass es funktioniert. Aufräumen kann man das wenn mal Zeit dafür ist face-smile

Wenn die Daten dann mithilfe dieses Scripts in einer View abgelegt sind, kann der Export entweder manuell erfolgen oder automatisiert per Datenbankverbindung nach Excel. Ich habe alles in einen Talend Open Studio Job gepackt, der läuft seit Monaten gut und schnell und macht was er soll.

Ich hoffe es hilft ein wenig.. face-smile

Greetz,
thejoker2305
Member: TheJoker2305
TheJoker2305 Sep 10, 2021 updated at 08:36:59 (UTC)
Goto Top
Gern erstelle ich ein Programm, welches die Anforderung komplett umsetzt.

- Datenablage inkl. Archivierung der Rohdaten für Recherchen
- Datenimport
- Datenaufbereitung
- Berechnungen
- Export nach Excel und /oder PDF
- inkl. automatischem Ausdruck auf Mitarbeiterbasis (Jeder Mitabeiter erhält seine Monatsübersicht)
- optionaler automatischer Versand der PDf Datei an den Mitarbeiter
- Report für GF als Überblick mit den kumulieren Zeiten und Beträgen

Die Ausführungsumgebung erfordert Java und die Möglichkeit eine Batchdatei ausführen zu dürfen.
Unter Umständen gehts auch ohne installiert Datenbank (die bringt das Programm dann nur für die interne Verwendung mit.)

Greetz,
thejoker2305

Weiteres gern per PN.
Member: NixVerstehen
NixVerstehen Sep 10, 2021 at 08:54:48 (UTC)
Goto Top
Und was spricht dagegen, dass deine Damen (oder Herren) die CSV herunterladen und in einem definierten Ordner abspeichern? Du kannst dir dann die Datei schnappen und via PS-Script in eine DB schieben.
Gruß
em-pie
Da spricht nichts dagegen. Das müsste sogar über den Berichtsgenerator von DAKO Tachoweb automatisiert gehen, d.h. ich könnte z.B. am 5. des Folgemonats für jeden Mitarbeiter die Arbeitszeiten als CSV-Datei bekommen.

@TheJoker2305: Ah....ein Leidensgenosse aus dem Logistikbereich face-wink So groß sind wir natürlich nicht, das wir einen eigenen Programmierer beschäftigen können. Wir haben nur ca. 70 Mitarbeiter, 6 im kaufmännischen Bereich und der Rest sind Fahrer. Ich bin der Prokurist und kümmere mich eben auch um die IT. Nichts Großes, ein Windows-Netz mit ein paar Servern, einer Handvoll Clients und dem üblichen Geräte-Zoo. Auf dem Gebiet bewege ich mich sicher und alles läuft geschmeidig. Bei Datenbanken und Programmierung bin ich raus, dazu fehlt mir das Fachwissen.

Ich hatte schon mehrfach Kontakt mit den Entwicklern von DAKO. Die weisen in der CSV-Datei ja die Nachtzeit von 23:00 Uhr bis 06:00 Uhr aus. Sie haben unseren Wunsch nach Splittung in die zulageberechtigten Zeiten aufgenommen. D.h. irgendwann mal in ferner Zukunft kommt da vielleicht was.....

Ich schick dir heute Nachmittag mal meine Kontaktdaten per PN. Dann können wir uns über Details und einen Auftrag unterhalten.

Gruß Arno
Member: ukulele-7
ukulele-7 Sep 10, 2021 at 10:48:26 (UTC)
Goto Top
Das selbst zu importieren, verarbeiten und wieder lesbar an den Benutzer zurück zu geben ist natürlich schon Arbeit und dann auch alles von der Gründlichkeit der Mitarbeiter abhängig, also können Fehler passieren. Vielleicht kannst du mit den DAKO Leuten einen Deal machen das sie dir das Ergebnis einer von dir gelieferten SQL Auswertung (natürlich nur nach Prüfung durch DAKO) exportierbar machen. Viele OnPremise Lösungen bieten sowas, die Cloud natürlich eher nicht. Aber viele dieser Plattformen versuchen ja tatsächlich Funktionen auszubauen.

Was SQL angeht kann man mit CTE etc. tolle Sachen machen, ich würde nur versuchen dieses CASE-Konstrukt eleganter zu lösen face-smile Ich habe mal in einem anderen Forum Produktionsmengen aus beliebigen Zeiträumen auf KWs aufgeteilt, das ist eigentlich nicht viel anders als Stunden auf Zeiträume mit Zuschlägen aufzudröseln.