Hilfe bei (für mich) komplexer Datenbank mit Berechnungen

[RobertG]

Hallo Milan,

wenn der gepostete Code Dein Originalcode ist, dann fehlen da zwei Kommas. Da Du aber das Datum ja jetzt bereits in der Ansicht über den Filter einschränkst, musst Du nicht in der Abfrage auch noch mit einer Filterung arbeiten.

Wenn Du auch die vorherigen und nachfolgenden Zeitbereiche trotz der Filterung der Ansicht in der Abfrage darstellen willst, dann kopiere einfach die Ansicht und nehme aus der Kopie die Filterung heraus. Dann kannst Du dort die Gesamtsumme oder die Summe bis zu einem gewissen Zeitpunkt auslesen.

Gruß

Robert

[bassimin]

Hallo Robert

Danke.
Das hat sich jetzt überschnitten, hab die ganze Nacht an der Bank geschraubt
Mir ist dann auch klargeworden, dass ich doppelt am filtern bin, den Abfragecode hab ich zuerst hinbekommen.
Hab zwei Typen Abfragen gemacht, einmal mit (für Summierung über den gefilterten Datumsbereich)

Code: Alles auswählen

AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ) 

und einmal mit (für "laufend", von Anfang an)

Code: Alles auswählen

AND "a"."Datum" BETWEEN ( SELECT MIN( "Datum" ) FROM "Ansicht_compo" WHERE "mensch" = "a"."mensch" ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ) 

Hab dann die Filterung aus der Ansicht wieder rausgenommen.

Bei den Abfragen auf die (ungefilterte) Ansicht funktioniert jetzt aber etwas mit der Summierung nicht, und ich verstehe nicht warum - sie summiert mehr auf, als in den gefilterten Datensätzen drinsteht:

Filterung mit der Filtertabelle - sollte nur die auch ausgegebenen Datensätze summieren ?! - summiert aber alles durch:

Code: Alles auswählen

"abf_SUM_compo_fltrD"
 
SELECT "a".*, 
  ( SELECT SUM( "tagesverdienst" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ) 
AS "VERDIENST_fltrD",
 
 ( SELECT SUM( "AUSBEZAHLT" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) ) 
AS "AUSBEZ_fltrD",
 
 ( SELECT SUM( "tagesverdienst" ) - SUM( "AUSBEZAHLT" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) ) 
AS "UEBRIG_fltrD",
 
 ( SELECT SUM( "h/Tag" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ) 
AS "STUNDEN_fltrD" 

FROM "Ansicht_compo" AS "a"  
WHERE "Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) 
                  AND ( SELECT "Enddatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE )

Und bemerkt, dass ich mit diesem Schnipsel achtlos war.

Code: Alles auswählen

AND "Datum" <= "a"."Datum"

Wenn ich ihn wieder hinzufüge, filtert es, wie ich von der Abfrage mit

Code: Alles auswählen

BETWEEN ( SELECT MIN( "Datum" ) FROM ....

erwartet hätte und summiert alles Stück für Stück dazu (à la Kontostand) - berücksichtigt aber das Startdatum nicht als Filterkriterium (summiert vom ersten Datensatz an auf):

Code: Alles auswählen

"abf_SUM_compo_fltrD"
 
SELECT "a".*, 
  ( SELECT SUM( "tagesverdienst" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "Datum" <= "a"."Datum"
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ) 
AS "VERDIENST_fltrD",
 
 ( SELECT SUM( "AUSBEZAHLT" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "Datum" <= "a"."Datum"
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) ) 
AS "AUSBEZ_fltrD",
 
 ( SELECT SUM( "tagesverdienst" ) - SUM( "AUSBEZAHLT" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch" 
AND "Datum" <= "a"."Datum"
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) ) 
AS "UEBRIG_fltrD",
 
 ( SELECT SUM( "h/Tag" ) FROM "Ansicht_compo"  
WHERE "mensch" = "a"."mensch"
AND "Datum" <= "a"."Datum" 
AND "a"."Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) 
                    AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ) 
AS "STUNDEN_fltrD" 

FROM "Ansicht_compo" AS "a"  
WHERE "Datum" BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE ) 
                  AND ( SELECT "Enddatum" FROM "z_filter_datum"  WHERE "ID_flt" = TRUE )

Bin jetzt etwas verwirrt &
Ich hänge mal die neuste Version an.

[RobertG]

Hallo Milan,

Du filterst weiterhin nicht die Ansicht. Deshalb darf so etwas wie

Code: Alles auswählen

... BETWEEN ( SELECT MIN( "Datum" ) FROM "Ansicht_compo" WHERE "mensch" = "a"."mensch" ) AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ...

nicht dort stehen. Das erste Datum ist dann das kleinste Datum der Ansicht, nur das zweite Datum das Enddatum aus dem Filter.

So müsste der Code der Ansicht sein, damit Du Dir alle anderen Klimmzüge sparen kannst. Dann ist die Ansicht bereits nach dem Datum gefiltert. Mache einfach eine "Ansicht_compo_gefiltert" mit diesem Code und schau Dir das an:

Code: Alles auswählen

SELECT "alletage"."Datum", "alletage"."mensch", "menschen"."vn", "menschen"."nn", "x_status"."STATUS", "x_wieheute"."wieheute", 
"alletage"."VON", "alletage"."BIS", "x_status"."LOHN", DATEDIFF( 'mi', "VON", "BIS" ) / 60.0 AS "h/Tag", 
( DATEDIFF( 'mi', "VON", "BIS" ) * "x_status"."LOHN" ) / 60 AS "tagesverdienst", "alletage"."STUNDEN", "alletage"."StandALT", "alletage"."VERDIENT", 
"alletage"."AUSBEZAHLT", "alletage"."StandNEU", "alletage"."ID-tage", "menschen"."IDmensch", "x_einsatz"."ID-einsatz", "x_status"."ID-status", 
"x_wieheute"."ID-dajanein", "x_wir"."ID-wir" FROM "alletage", "menschen", "x_status", "x_wir", "x_wieheute", "x_einsatz" 
WHERE "alletage"."mensch" = "menschen"."IDmensch" 
AND "menschen"."status" = "x_status"."ID-status" 
AND "alletage"."werhatbezahlt" = "x_wir"."ID-wir" 
AND "alletage"."WIE" = "x_wieheute"."ID-dajanein" AND "x_einsatz"."ID-einsatz" = "alletage"."Einsatzort" 
AND "alletage"."Datum" 
BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE )
 AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) 
ORDER BY "alletage"."Datum" ASC, "menschen"."vn" ASC

Gruß

Robert

[bassimin]

Hallo Robert
Danke weiterhin!

Hab inzwischen die Datenbank "durchgeputzt" - Feld- und Tabellennamen systematisiert, drei Ansichten und entsprechende Abfragen gemacht. Nach einiger Tüftelei im Formular schien mir alles korrekt zu laufen, aber... ein Formularfeld summiert mit eigentümlichen Sprüngen verzögert, während die Abfrage darunter die richtigen Werte für jeden Tag ausgibt. Die Summe hinkt bei der ausgewählten Konstellation um vier Stunden hinterher, lässt einen Schritt aus - addiert unverständlicherweise 0 dann 3,5 dann 0,5 dann 8 anstatt 4+0+4+4 und ist immer noch um 4 zu niedrig. Entsprechend erfolgt ein völlig unverständlicher Aussetzer im parallelen Feld zu Auszahlungen, dort dürfte sich gar nichts bewegen.
Wenn du's dir anschauen willst, im Formular habe ich entsprechende Kommentare reingeschrieben.

UPDATE: Sehe gerade, dass die Felder nicht auf die richtige ID-Mensch Bezug nehmen, forsche...

Ansonsten nimmt das Projekt Gestalt an! Die Hauptfrage, die ich noch gerne lösen würde, ist die von Übertrag bei Wochen- oder Monatsabschluss.

Viele Grüße!
Milan

[bassimin]

...das habe ich lösen können, die Verknüpfungen im Formular mussten sich auf Datum UND Mensch beziehen.

Dabei bin ich auf eine Schwierigkeit mit dem Bezug nach Datum gestoßen: Ich habe das Datum als Timestamp angelegt, will aber meistens nur nach dem Datumsanteil vergleichen. Hab das mit diesem Code auszubügeln versucht, der produziert aber einen Syntax-Error:
(syntax error, unexpected $end, expecting BETWEEN or IN or SQL_TOKEN_LIKE)

Code: Alles auswählen

SELECT "alletage".*, "menschen"."ID-mensch", "menschen"."vn", "menschen"."nn" FROM "alletage", "menschen" 
WHERE "alletage"."mensch-id" = "menschen"."ID-mensch" 
AND 
SELECT CAST("Datum" FROM "alletage" AS DATE) AS "NurDatum"
WHERE "NurDatum"
BETWEEN ( SELECT "Startdatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) AND ( SELECT "Enddatum" FROM "z_filter_datum" WHERE "ID_flt" = TRUE ) ORDER BY "menschen"."vn" ASC

Trotz vieler Suche und dem Handbuch, das übrigens außerordentlich toll und detailliert ist, komme ich nicht weiter...
Wie wäre das zu lösen?

Viele Grüße
Milan

[RobertG]

Hallo Milan,

gut, dass Du das erste Problem alleine lösen konntest. Ich habe erst einmal Schwierigkeiten überhaupt herauszufinden, was wo gerechnet werden sollte. Außerdem dann auch noch das Problem, dass ich einige Felder mangels Kontrast überhaupt nicht lesen kann. Vermutlich hast Du einen sehr kontrastreichen und hellen Bildschirm und bessere Augen als ich ...

Zu dem Code:

Code: Alles auswählen

... AND 
SELECT CAST("Datum" FROM "alletage" AS DATE) AS "NurDatum"
WHERE "NurDatum" BETWEEN...

Da hast Du eine Select-Anweisung in eine andere geschrieben, dazu noch mit einer Umwandlung eines Datums, mit einem Verweis auf eine Tabelle geziert ist ... Als korrekte Select-Anweisung müsste das so lauten:

SELECT CAST("Datum" AS DATE) FROM "alletage"

Dann ergäbe das aber immer noch eine Unmenge an Datensätzen und nicht ein eindeutiges Datum, das in Unterabfragen vorkommen darf. Bei Dir müsste vermutlich der folgende Code stattdessen zielführend sein:

Code: Alles auswählen

... AND 
CAST("Datum" AS DATE) BETWEEN...

Gruß

Robert

[bassimin]

Danke, das ging ja schnell. Ich sehe beim ständigen Suchen, dass du ja unglaublich viel Hilfe leistest, außerdem (mit-?)verantwortlich für das Handbuch zeichnest, Hut mehrmals ab!
Vielleicht liegt die seltsame Farbigkeit für dich am anderen System und Theme, ich hätte schon anfangs mal schreiben können, dass ich LO Version: 5.4.4.2
Build-ID: 1:5.4.4~rc2-0ubuntu0.16.04.1~lo1
CPU-Threads: 8; BS: Linux 4.4; UI-Render: Standard; VCL: gtk2;
Gebietsschema: de-DE (de_DE.UTF-8); Calc: group
benutze, und ich habe ein dunkles Theme. Meine Augen zeigen auch schon Alterungserscheinungen! Bildschirmfoto anbei. Allerdings wundere ich mich, dass ich die Schriftfarbe in den Formulareinstellungen zwar ändern kann, das sich aber nicht auswirkt.

Ich weiß nicht genau, wie ich jetzt weitermachen soll, im Grunde läuft die Bank vor allem durch deine Hilfe ja jetzt schon, für den täglichen Gebrauch und Nutzerkomfort habe ich noch einige Schritte vor.
Vor allem beschäftigt mich die Frage mit den Überträgen.
Und weitere Fragen, bei denen es dann doch auch auf Makros rauslaufen wird - glaub ich auf jeden Fall. Ich hab dazu schon viel im Handbuch gelesen, und auch angefangen, ein Gerüst für mein ursprünglich intendiertes zu machen. Aber bei den Feinheiten, zB wie ich aus welchem Datensatz die Variablen belege und wieder woanders hinschreibe, bin ich noch gut überfordert.

Bist du einverstanden, wenn ich hier noch weiter Fragen poste?
Oder besser in neuen Threads?
Die ToDo-Liste hab ich mir ja im Formular notiert, da erschließt sich vielleicht auch am besten der Zusammenhang:

• Sicherheitsabfragen: wenn anwesend BIS-VON>0! (Hab schon coalesce >=0 angewandt)
  sonst 0

• keine Überschneidungen pro Mensch

• Errechnen & "Verbuchen": 4 Fehlstunden pro Tag unentsch/entsch/krank, Ausgabe pro Monat

• Buttons um den Datumszeitraum auf eine Woche/Monat/Jahr zu setzen/verschieben

• vorausgefüllte Tagestabelle bereitstellen - (per Button - Makro?)

• Klären, warum das Schriftfarbenformat nicht wirkt ...

Viele Grüße
Milan

[RobertG]

Hallo Milan,

der Thread ufert im Moment schon sehr aus. Vielleicht schreibst Du mir das Weitere einfach per privater Mail? Mailadresse findest Du ja auf meiner Homepage.

Gruß

Robert

[mhonline]

Moinsen

Ich finde es gar nicht schlecht, ein rel. komplexes Projekt hier "öffentlich" weiter zu entwickeln, aber das müßt ihr beiden selbst entscheiden.
Mir fällt allerdings auf, daß mir die Idee dahinter nicht ganz klar wird, sie ergibt sich jedenfalls nicht sofort aus der Datenstruktur, die Tabelle "alleTage" ist irgendwie überfrachtet, während anderes unklar bleibt.
1.
"alleTage" ist sozusagen ein Kalender, welcher aus Sicht des Projektes Verbindlichkeiten auslöst. Dann sollte es auch eine Zahlungsliste geben, welche die Verbindlichkeiten durch nachvollziehbare Zahlungen auflöst, gewissermassen die minimalste Form einer dokumentierten Auflösung in Soll und Haben.
Datensätze wie in Tabelle ANS_Serie_VLG sind da eher hinderlich.
2.
Unklar ist ebenfalls die Projekt-Dokumentation, etwa wenn die Frage wäre, wieviele Arbeitsstunden in welcher Tätigkeit für welches Teil-Projekt geleistet wurden bzw. ob die angefallenen Löhne noch im Budget-Rahmen liegen, usf.
Kurz, da geht noch ein bißchen was, einige Erweiterungen könnten die Sache anfänglich verkomplizieren, letztlich aber Übersichtlichkeit schaffen.

denkt sich

martin

[RobertG]

Hallo Martin,

im Sinne eines Forums ist es besser, klar abgegrenzte Fragen in einem Thread zu erörtern. Dieser Thread begann (leider) bereits mit einer kompletten Datenbank, zu der sich sicher viel hätte schreiben lassen. Da haben wir jetzt die Hauptpunkte behoben (Filterung nach Datumswerten). Für jede neue Problemstellung sollte ein neuer Thread aufgemacht werden. Sonst finden andere Nutzer nichts. Und je klarer die Datenbank ist, die zu dem Problem führt, desto besser. Sonst liest das Ganze nachher kein Mensch.

Gruß

Robert