Tabellen verknüpfen/Sichten?

Ouvert

Benutzer
Beiträge
7
Hallo,
Ich habe folgendes Problem:

Bei der Zeiterfassung mit Hilfe eines Terminal-PC`s werden die erfassten Daten in Microsoft Navision gespeichert unter einer speziellen Sicht("viewNAV_BDE") auf der MSSQL Datenbank.
Daraufhin werden die Arbeitsbeginn/Ende Zeiten auf 15 Minuten hoch/runter gerundet.
Dies soll bei einer bestimmten Gruppe von Personen nicht mehr passieren! (Zeitmodell: Gleitzeit (ID:1 und 2))
(Es gibt ebenfalls eine Tabelle, in der das Zeitmodell mit PersonalNr. hinterlegt ist.)

Diese Rundung wird von dem Zeiterfassungsprogramm ausgeführt(Stored Procedure) und nennt sich spAutoSyncAnwesenheitRev.

Dabei wird in der SQL Tabelle: tAnwesenheit die Werte aus der Sicht("viewNAV_BDE") eingeschrieben mit der speziellen Rundung.
Wie lässt es sich nun am besten realisieren, dass diese Prozedur nur rundet, wenn die Personen nicht zur ZeitmodellID 1 oder 2 gehören?

Anbei die Prozedur:
CREATE PROCEDURE [spAutoSyncAnwesenheitRev]
AS
BEGIN
DECLARE @Round AS INT = ( SELECT [WertInt] FROM [tParameter] WHERE [Name] = 'RundungsInterval' );

INSERT INTO [tAnwesenheit]
( [BDE_ID], [PersonalNr], [Name], [Vorname], [Datum], [Zeit], [Meldeart], [MeldeartText] )
SELECT [ID],
[PersonalNR],
[Name],
[Vorname],
[Datum],
CASE [Meldungsart]
WHEN 6 THEN DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] - '00:00:59' ) / @Round * @Round ) , 0 )
WHEN 7 THEN DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] + '00:00:59' ) / @Round * @Round ) - ( CASE @Round WHEN 1 THEN 0 ELSE @ROUND END ), 0 )
WHEN 1 THEN [Zeit]
WHEN 2 THEN [Zeit]
END AS [Zeit],
[Meldungsart] AS [Meldeart],
CASE [Meldungsart]
WHEN 6 THEN 'Kommt'
WHEN 7 THEN 'Geht'
WHEN 1 THEN 'Pause'
WHEN 2 THEN 'PauseEnde'
END AS [MeldeartText]
FROM [viewNAV_BDE]
WHERE ( [Meldungsart] = 6 OR [Meldungsart] = 7 OR [Meldungsart] = 1 OR [Meldungsart] = 2 ) AND
[ID] NOT IN( SELECT [BDE_ID] FROM [tAnwesenheit] )
ORDER BY [ID];
END
 
Werbung:

ukulele

Datenbank-Guru
Beiträge
4.702
Ich würde vielleicht gar nicht die SP ändern sondern die View. In etwa so (pseudo Code):
Code:
SELECT (CASE WHEN mitarbeitergruppe = 1 THEN zeit ELSE spAutoSyncAnwesenheitRev(zeit) END) AS zeit,* FROM tabellen
Das hätte den Vorteil das die SP nach wie vor das tut was der ursprüngliche Author im Sinn hatte und gar nicht erst läuft wenn sie nichts zu tun hat. Ich muss allerdings auch gestehen das mir die Vorgehensweise hier in der SP nicht ganz einleuchtet.
 

Ouvert

Benutzer
Beiträge
7
Ich würde vielleicht gar nicht die SP ändern sondern die View. In etwa so (pseudo Code):
Code:
SELECT (CASE WHEN mitarbeitergruppe = 1 THEN zeit ELSE spAutoSyncAnwesenheitRev(zeit) END) AS zeit,* FROM tabellen
Das hätte den Vorteil das die SP nach wie vor das tut was der ursprüngliche Author im Sinn hatte und gar nicht erst läuft wenn sie nichts zu tun hat. Ich muss allerdings auch gestehen das mir die Vorgehensweise hier in der SP nicht ganz einleuchtet.


Schon mal Danke für den Tipp.
Nun stellt sich nur noch die Frage wie man am besten die mitarbeitergruppe(ZeitmodellID) ermitteln könnte, eventuell fremdschlüssel einschieben?
Weil in der Sicht direkt ist die ZeitmodellID auch nicht angegeben. Sie ist in einer anderen Tabelle zu finden, die PersonalNr, Name, Vorname, ZeitmodellID und Zeitmodell enthält :)

Was meinst du mit: nicht genau einleuchtet? :)

Liebe Grüße
 

ukulele

Datenbank-Guru
Beiträge
4.702
Nun stellt sich nur noch die Frage wie man am besten die mitarbeitergruppe(ZeitmodellID) ermitteln könnte, eventuell fremdschlüssel einschieben?
Weil in der Sicht direkt ist die ZeitmodellID auch nicht angegeben. Sie ist in einer anderen Tabelle zu finden, die PersonalNr, Name, Vorname, ZeitmodellID und Zeitmodell enthält :)
Left-Join der Information in der Abfrage.
Was meinst du mit: nicht genau einleuchtet? :)
Ich checks nicht. Vor allem warum man, um Werte zu runden, irgendwo Daten in eine Tabelle schreibt.
 

Ouvert

Benutzer
Beiträge
7
Left-Join der Information in der Abfrage.

Ich checks nicht. Vor allem warum man, um Werte zu runden, irgendwo Daten in eine Tabelle schreibt.


Nochmal vielen Dank für den Tipp, den werde ich sicher dann gleich ausprobieren :)


Da die Daten aus einem anderen Programm genommen werden und nicht gerundet sind.
Die Tabelle tAnwesenheit und tAnwesenheit dient dann der monatlichen Lohnrechnung.
Es wäre zwar möglich den Parameter einfach auf 1 zu setzen, aber es sollen ja unterschiedliche Zeitmodelle funktionieren.
Bsp:
Das 3 Schicht System soll weiterhin auf 15 Minuten runden.. Damit sind sie "gezwungen" pünktlich an der Maschine zu stehen, um ihre 8 Stunden einzuhalten. Wenn dies nicht der Fall wäre könnten sie einfach 8.04 Uhr anfangen und würden dem Nachfolger eventuell 4 Minuten stehlen(weil er ja 8h arbeiten soll) - der aber pünktlich anfangen will.
Bei Gleitzeit ist es etwas anderes, da soll man flexibler sein :)
Verstehst du jetzt was ich meine? :)
 

ukulele

Datenbank-Guru
Beiträge
4.702
Also ich verstehe wie die Prozedur rundet und das sie das Anhand verschiedener Zeitmodelle tut (je nach [Meldungsart] 1,2,6 oder 7). Ich verstehe nur nicht warum man das nicht in einer Sicht tut sondern über eine SP aus einer Sicht (es gibt also schon eine!) in eine weitere Tabelle schreibt (kann sinnvoll sein, muss aber nicht).

Eventuell fehlt dir nur eine etwas umgebaute CASE-Schleife, da kann man nämlich statt
CASE spalte WHEN 1 THEN
auch anders vorgehen:
CASE WHEN spalte = 1 THEN
Das hat Vorteile. Unter anderem andere Operatoren als Gleich und man kann andere Spalten als Bedingung einbeziehen:
Code:
SELECT   [ID],
     [PersonalNR],
     [Name],
     [Vorname],
     [Datum],
     (   CASE
       WHEN   [Meldungsart] = 6
       THEN   DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] - '00:00:59' ) / @Round * @Round ) , 0 )
       WHEN   [Meldungsart] = 7
       THEN   DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] + '00:00:59' ) / @Round * @Round ) - ( CASE @Round WHEN 1 THEN 0 ELSE @ROUND END ), 0 )
       WHEN   [Meldungsart] IN ( 1,2 )
       OR     t.[ZeitmodellID] IN ( 1,2)
       THEN   [Zeit]
       END ) AS [Zeit],
     [Meldungsart] AS [Meldeart],
     (   CASE   [Meldungsart]
       WHEN 6 THEN 'Kommt'
       WHEN 7 THEN 'Geht'
       WHEN 1 THEN 'Pause'
       WHEN 2 THEN 'PauseEnde'
       END ) AS [MeldeartText]
FROM   [viewNAV_BDE]
LEFT JOIN andere_tabelle t
ON     [viewNAV_BDE].[PersonalNR] = t.PersonalNr
WHERE ( [Meldungsart] = 6 OR [Meldungsart] = 7 OR [Meldungsart] = 1 OR [Meldungsart] = 2 )
AND [ID] NOT IN( SELECT [BDE_ID] FROM [tAnwesenheit] )
ORDER BY [ID];
 

Ouvert

Benutzer
Beiträge
7
Also ich verstehe wie die Prozedur rundet und das sie das Anhand verschiedener Zeitmodelle tut (je nach [Meldungsart] 1,2,6 oder 7). Ich verstehe nur nicht warum man das nicht in einer Sicht tut sondern über eine SP aus einer Sicht (es gibt also schon eine!) in eine weitere Tabelle schreibt (kann sinnvoll sein, muss aber nicht).

Eventuell fehlt dir nur eine etwas umgebaute CASE-Schleife, da kann man nämlich statt
CASE spalte WHEN 1 THEN
auch anders vorgehen:
CASE WHEN spalte = 1 THEN
Das hat Vorteile. Unter anderem andere Operatoren als Gleich und man kann andere Spalten als Bedingung einbeziehen:
Code:
SELECT   [ID],
     [PersonalNR],
     [Name],
     [Vorname],
     [Datum],
     (   CASE
       WHEN   [Meldungsart] = 6
       THEN   DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] - '00:00:59' ) / @Round * @Round ) , 0 )
       WHEN   [Meldungsart] = 7
       THEN   DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] + '00:00:59' ) / @Round * @Round ) - ( CASE @Round WHEN 1 THEN 0 ELSE @ROUND END ), 0 )
       WHEN   [Meldungsart] IN ( 1,2 )
       OR     t.[ZeitmodellID] IN ( 1,2)
       THEN   [Zeit]
       END ) AS [Zeit],
     [Meldungsart] AS [Meldeart],
     (   CASE   [Meldungsart]
       WHEN 6 THEN 'Kommt'
       WHEN 7 THEN 'Geht'
       WHEN 1 THEN 'Pause'
       WHEN 2 THEN 'PauseEnde'
       END ) AS [MeldeartText]
FROM   [viewNAV_BDE]
LEFT JOIN andere_tabelle t
ON     [viewNAV_BDE].[PersonalNR] = t.PersonalNr
WHERE ( [Meldungsart] = 6 OR [Meldungsart] = 7 OR [Meldungsart] = 1 OR [Meldungsart] = 2 )
AND [ID] NOT IN( SELECT [BDE_ID] FROM [tAnwesenheit] )
ORDER BY [ID];

Naja das Programm für die Zeiterfassung wurde vom damaligen Administrator geschrieben. Warum er das so gemacht hat kann ich dir auch nicht sagen :)

Aber der Tipp mit dem Left Join war echt hilfreich. Ich hatte es heute noch anders probiert.
Vor der Select Abfrage eine Abfrage nach der ZeitmodellID und mit Hilfe einer IF Bedingung den Rundungsparameter anders gesetzt.. Dabei ist mir nur aufgefallen, dass wenn in kurzer Zeit mehrere Buchungen reinkommen, der das noch nicht geändert hat.

Deine Methode werde ich morgen dann gleich ausprobieren, da dies dann wahrscheinlich nicht mehr Auftritt.

Ich danke dir für deine großzügige Hife und melde mich dann wieder :)
Bis dahin, viele Grüße
 

Ouvert

Benutzer
Beiträge
7
Aber die Meldungsart spiegelt nicht das Zeitmodell wieder.
Sie gibt an was gebucht wurde.

Müsste man da in dem Case nicht vorher fragen welches Zeitmodell der Mitarbeiter hat und dann auf der Information aufbauen runden Bzw nicht?
 

ukulele

Datenbank-Guru
Beiträge
4.702
Also Meldungsart kommt aus deiner ursprünglichen SP da habe ich eigentlich nur die CASE Schleife umgestellt aber nichts am Ergebnis geändert. Ich habe nur ZeitmodellID = 1 oder 2 als weitere Option eingefügt um das Runden zu unterbinden.

Natürlich kannst du auch mit einem weiteren CASE arbeiten, mir fehlt sowieso etwas der Durchblick wie ich Anfangs schon sagte. Dazu müsste ich mir das sehr viel genauer ansehen und bräuchte Beispieldatensätze und was raus kommen soll.
 
Werbung:

Ouvert

Benutzer
Beiträge
7
Also Meldungsart kommt aus deiner ursprünglichen SP da habe ich eigentlich nur die CASE Schleife umgestellt aber nichts am Ergebnis geändert. Ich habe nur ZeitmodellID = 1 oder 2 als weitere Option eingefügt um das Runden zu unterbinden.

Natürlich kannst du auch mit einem weiteren CASE arbeiten, mir fehlt sowieso etwas der Durchblick wie ich Anfangs schon sagte. Dazu müsste ich mir das sehr viel genauer ansehen und bräuchte Beispieldatensätze und was raus kommen soll.




Ich habe es jetzt so gelöst und es funktioniert bestens.

Code:
ALTER PROCEDURE [dbo].[spAutoSyncAnwesenheitRev]
AS
BEGIN

DECLARE @Round AS INT = ( SELECT [WertInt] FROM [tParameter] WHERE [Name] = 'RundungsInterval' );
INSERT INTO [tAnwesenheit]
( [BDE_ID], [PersonalNr], [Name], [Vorname], [Datum], [Zeit], [Meldeart], [MeldeartText] )
SELECT viewNAV_BDE.[ID],
viewNAV_BDE.[PersonalNR],
viewNAV_BDE.[Name],
viewNAV_BDE.[Vorname],
[Datum],
(CASE
WHEN [Meldungsart] = 6 AND tMitarbeiterAbteilungZeitmodell.[ZeitmodellID] > 2 THEN DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] - '00:00:59' ) / @Round * @Round ) , 0 )
WHEN [Meldungsart] = 7 AND tMitarbeiterAbteilungZeitmodell.[ZeitmodellID] > 2 THEN DATEADD ( MINUTE, ( DATEDIFF( MINUTE, 0,[Zeit] + '00:00:59' ) / @Round * @Round ) - ( CASE @Round WHEN 1 THEN 0 ELSE @ROUND END ), 0 )
WHEN [Meldungsart] = 6 AND tMitarbeiterAbteilungZeitmodell.[ZeitmodellID] <= 2 THEN [Zeit]
WHEN [Meldungsart] = 7 AND tMitarbeiterAbteilungZeitmodell.[ZeitmodellID] <= 2 THEN [Zeit]
WHEN [Meldungsart] IN (1,2) THEN [Zeit]
END) AS [Zeit],
[Meldungsart] AS [Meldeart],
CASE [Meldungsart]
WHEN 6 THEN 'Kommt'
WHEN 7 THEN 'Geht'
WHEN 1 THEN 'Pause'
WHEN 2 THEN 'PauseEnde'
END AS [MeldeartText]

FROM [viewNAV_BDE] LEFT JOIN tMitarbeiterAbteilungZeitmodell ON viewNAV_BDE.PersonalNR = tMitarbeiterAbteilungZeitmodell.PersonalNR
WHERE ( [Meldungsart] = 6 OR [Meldungsart] = 7 OR [Meldungsart] = 1 OR [Meldungsart] = 2 ) AND
viewNAV_BDE.[ID] NOT IN( SELECT [BDE_ID] FROM [tAnwesenheit] )
ORDER BY [ID];
END

Nochmals vielen Dank für deine großzügige Hilfe !!
Hat wunderbar geklappt :)
 
Oben