Suchanfrage weiter eingrenzen

Entwicklung Programmierung
1

Hallo zusammen,

ich erstelle gerade eine App wo man sehen kann wann Mitarbeiter krank sind/waren.

So soll das aussehen:

|nummer|name |krank_tage|au_intern|unfall|covid|abteilung |erster_tag|letzer_tag|
|308   |Maier|NULL     |NULL     |3.7   |NULL |Elektriker|20250916  |20250919  |

So sieht’s aus:

|nummer|name |krank_tage|au_intern|unfall|covid|abteilung |erster_tag|letzer_tag|
|308   |Maier|NULL     |NULL     |3.7   |NULL |Elektriker|20250916  |20250930  |

Man kann in der App das Datum eingrenzen, von bis, und da liegt mein Problem, ich weiß nicht wie ich der Abfrage beibringen kann daß in der Spalte “erster_tag” und “letzter_tag” nur das tatsächliche Datum wann er krank war auch angezeigt wird.

So sieht die Abfrage aus:

Hier suche ich nach dem MA 308 und das Datum 16.09.25 - 30.09.25, dieser MA war auch krank vom 16.09.25 - 19.09.25, aber als Ergebnis bekomme ich bei “letzter_tag” den 30.09.25

SELECT zbpenr as nummer, 
psnavo||' '||psnafa as name,
SUM(CAST(CAST(NULLIF(ZBKRKT,0) AS VARCHAR(50)) AS DOUBLE PRECISION)) as krank_tage, 
SUM(CAST(CAST(NULLIF(ZBSON2,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/MAX(zbreaz) as au_intern,  
SUM(CAST(CAST(NULLIF(ZBSON3,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/MAX(zbreaz) as unfall,
SUM(CAST(CAST(NULLIF(ZBSON4,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/MAX(zbreaz) as covid, 
SUM(CAST(CAST(NULLIF(ZBSON5,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/MAX(zbreaz) as kind_krank,
a6beme as abteilung,
FIRST_VALUE(MIN(zbdat8)) OVER (PARTITION BY COUNT(zbpenr)) AS erster_tag,
LAST_VALUE(MAX(zbdat8)) OVER (PARTITION BY COUNT(zbpenr)) AS letzter_tag
FROM VEZEIT
inner join vepsta on zbfirm=psfirm and zbpenr=pspenr
inner join veabte on zbfirm=a6firm and psabte=a6abte
where zbpenr='308' and pseitm>'0' and psatd8='0' and zbdat8 between 20250916 and 20250930
and zbfirm='1' and psagrp='GW'
group by zbpenr,psnavo,psnafa,abteilung
order by zbpenr

vielleicht denke ich dabei zu kompliziert und ich sehe den Wald nicht vor lauter Bäume.

Danke und Gruß

Gregorio

2

Warum first_value() und last_value()?

Du gruppierst doch bereits, sollte da ein “normales” min/max nicht ausreichen?

(Unabhängig von der Frage finde ich die viele CAST aufrufe sehr fraglich - das ist für mich immer ein Alarmsignal, dass da Spalten mit den falschen Datentypen definiert wurden)

3

Die Felder mit dem Cast haben ja keine Probleme bereitet, klar, ich hätte folgendes machen können:

SUM(NULLIF(ZBKRKT,0)) as krank_tage, 
SUM(NULLIF(ZBSON2,0))/MAX(zbreaz) as au_intern,

die tests die ich gemacht habe mit min/max geben mir leider immer das ausgewählte Datum “von - bis” und nicht das tatsächliche Datum wann dieser MA wirklich krank war.

Hier:

MIN(zbdat8) as erster_tag,
MAX(zbdat8) as letzter_tag

erster_tag = 20250916 - richtig

letzter_tag = 20250930 - falsch

4

Könnte es sein, dass der Wert 20250930 zu einem anderen Krankheitseintrag gehört? Sprich: ein Mitarbeiter war zweimal in dem Zeitraum krank (einmal von 20250916 bis 20250919 und in zweiter im Zeitraum ??? bis 20250930)

Das kann man natürlich nur unterscheiden, wenn auch die Einträge in der Tabelle da irgendein Kriterium haben, mit dem man die zusammengehörigen Einträge auch erkennen kann.

5

nein, ich habe ja explizit MA = 308 eingeben

was funktioniert hat war folgendes, der abfrage sagen such nur wenn der wert bei unfall(son3) größer ist als 0.00

or (zbson3)>0.00

aber ich muss die anderen variablen dazu nehmen da mehr MA gibt als nur die 308 und mehr krank möglichkeiten gibt als nur unfall(son3)

das hier gibt mir dann division durch null error

or (zbkrkt)>0.00 or (zbson2)>0.00 or (zbson3)>0.00 or (zbson4)>0.00 or (zbson5)>0.00
6

Das habe ich verstanden, aber wenn der MA = 308 von 20250916 bis 20250919 krank geschrieben ist, und dann nochmal von z.B. 20250925 bis 20250930, dann werden beide Zeiträume wie einer behandelt, und Du kriegst als ersten Krankheitstag 20250916 und als letzten 20250930 - ich vermute, diese Situation wird auch Deine neue Bedingung nicht ändern. Es könnten ja zwei nicht zusammenhängende Zeiträume diese Bedingung erfüllen.

Das kannst Du vermeiden indem Du den Zähler zu NULL konvertierst wenn er 0 ist:

/nullif(MAX(zbreaz),0)
7

das funktioniert soweit ohne diesen error, aber dafür wird mir jetzt jeder MA angezeigt in dem diese variablen, in diesem Zeitraum größer als 0.00 ist/waren, angezeigt, also das zbpenr=‘308’ greift garnicht zu, mist.

SELECT zbpenr as nummer, 
psnavo||' '||psnafa as name,
SUM(CAST(CAST(NULLIF(ZBKRKT,0) AS VARCHAR(50)) AS DOUBLE PRECISION)) as krank_tage, 
SUM(CAST(CAST(NULLIF(ZBSON2,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/NULLIF(MIN(zbreaz),0) as au_intern,  
SUM(CAST(CAST(NULLIF(ZBSON3,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/NULLIF(MIN(zbreaz),0) as unfall,
SUM(CAST(CAST(NULLIF(ZBSON4,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/NULLIF(MIN(zbreaz),0) as covid, 
SUM(CAST(CAST(NULLIF(ZBSON5,0) AS VARCHAR(50)) AS DOUBLE PRECISION))/NULLIF(MIN(zbreaz),0) as kind_krank,
a6beme as abteilung,
MIN(zbdat8) as erster_tag,
MAX(zbdat8) AS letzter_tag
FROM VEZEIT
inner join vepsta on zbfirm=psfirm and zbpenr=pspenr
inner join veabte on zbfirm=a6firm and psabte=a6abte
where zbpenr='308' and pseitm>'0' and psatd8='0' and zbdat8 between 20250916 and 20250930
and zbfirm='1' and psagrp='GW'
or zbkrkt>0.00 or zbson2>0.00 or zbson3>0.00 or zbson4>0.00 or zbson5>0.00 
group by zbpenr,psnavo,psnafa,abteilung
order by zbpenr
8

ich muss mir was anderes überlegen da diese Abfrage, so wie ich sie gerne hätte, garnicht funktionieren kann, es sei denn es gibt noch eine Zauberformel die ich einbauen kann, fällt jemanden evtl. noch eine Möglichkeit ein?

9

Ohne eine Beschreibung der beteiligten Tabellen und Spalten finde ich es schwierig einen vernünftigen Vorschlag abzugeben.
Noch besser wären zusätzliche Testdaten.

1 „Gefällt mir“
10

Das vermute ich auch. Schlimmer wäre noch die Möglichkeit, dass ein Join fehlt.

11

Es sieht nicht so schlecht aus, nah dran sozusagen.
Aber Du solltest nicht zu viel von der Einbildungskraft der Forenteilnehmer erwarten.
Wenn Du die Tabellenbeschreibung lieferst, auf die sich die Abfrage bezieht, wäre schon geholfen. Wenn das zu geheim ist, müsstest Du es umschreiben.
Alternativ würde es hier und überhaupt helfen, ausgiebigen Gebrauch von Table Alias’ in der Abfrage zu machen. Das würde dann zu jeder Spalte immerhin die Tabellenherkunft verraten.
Sprechende Spaltennamen wären ebenso ein Ding.

12

Dazu bräuchten wir auch die Daten in der Tabelle. Derzeit sehen wir nur das (falsche) Ergebnis, und das ist nicht sehr hilfreich um zu Wissen, was man wie umschreiben müsste

13

ZBSON2 bis ZBSON5, sowie ZBREAZ sieht ein wenig nach Key Value Tabelle aus bzw. nach einer generellen Ereignisliste für alles. Wenn das so ist, hat Castorp Recht. Dann vermischt Du mit dem Aggregat zwischen min() und max() wahrscheinlich verschiedene Einträge.
Würdest Du von den Ereignissen eine ID mit ausgeben, könnte das helfen, führt aber vielleicht zu redundanten Mehrfachausgaben (bis auf die eindeutige Event ID).
Wahrscheinlich müsstest Du die Ereignisse inhaltlich und zeitlich vorfiltern, bevor Du sie an den MA joinst.

14

Hallo zusammen,

nein, am 20250930 ist definitiv nichts bei keinem MA in zbson3 eingetragen, aber dafür in den anderen Spalten.

Ich erstelle hier kurz provisorische Tabellen die das Bild ein wenig vereinfachen.

Tabelle vepsta

|psfirm|pspenr|psnavo|psnafo|psabte|
|1     |29    |Maria |Müller|CON   |
|1     |268   |Klaus |Kinski|VKF   |
|1     |308   |Markus|Maier |ELK   |

Tabelle veabte

|a6firm|a6abte|a6beme     |
|1     |ELK   |Elektriker |
|1     |CON   |Controlling|
|1     |VKF   |Verkauf    |

Tabelle vezeit

|zbfirm|zbpenr|zbreaz|zbkrkt|zbson2|zbson3|zbson4|zbson5|zbdat8  |
|1     |29    |7.50  |1.0   |0.00  |0.00  |0.00  |0.00  |20250916|
|1     |29    |7.50  |1.0   |0.00  |0.00  |0.00  |0.00  |20250917|
|1     |29    |7.50  |1.0   |0.00  |0.00  |0.00  |0.00  |20250918|
|1     |268   |7.50  |1.0   |0.00  |0.00  |0.00  |0.00  |20250915|
|1     |268   |7.50  |1.0   |0.00  |0.00  |0.00  |0.00  |20250916|
|1     |308   |8.00  |0.0   |0.00  |5.98  |0.00  |0.00  |20250916|
|1     |308   |8.00  |0.0   |0.00  |8.00  |0.00  |0.00  |20250917|
|1     |308   |8.00  |0.0   |0.00  |8.00  |0.00  |0.00  |20250918|
|1     |308   |8.00  |0.0   |0.00  |8.00  |0.00  |0.00  |20250919|
|1     |308   |8.00  |1.0   |0.00  |0.00  |0.00  |0.00  |20250920|

Jetzt zu den Abfragen:

Gebe ich das hier ein:

SELECT zbpenr as nummer, 
psnavo||' '||psnafa as name,
SUM(NULLIF(ZBKRKT,0)) as krank, 
SUM(NULLIF(ZBSON2,0))/MAX(zbreaz) as au,  
SUM(NULLIF(ZBSON3,0))/MAX(zbreaz) as unfall,
SUM(NULLIF(ZBSON4,0))/MAX(zbreaz) as covid, 
SUM(NULLIF(ZBSON5,0))/MAX(zbreaz) as kind,
a6beme as abteilung,
MIN(zbdat8) AS first,
MAX(zbdat8) AS last
FROM VEZEIT
inner join vepsta on zbfirm=psfirm and zbpenr=pspenr
inner join veabte on zbfirm=a6firm and psabte=a6abte
where zbpenr='308' and zbdat8 between 20250916 and 20250930 and zbfirm='1'
group by zbpenr,psnavo,psnafa,a6beme
order by zbpenr

bekomme ich als Antwort

|nummer|name|krank|au  |unfall|covid|kind|abteilung|first   |last    |
|1     |MM  |1.0  |null|3.74  |null |null|Elektrik |20250916|20250930|

Grenze ich ein mit “and zbson3>0.00” bekomme ich die richtige formatierung, sogar der Tag krank am 20250920 wird ausgeblendet

SELECT zbpenr as nummer, 
psnavo||' '||psnafa as name,
SUM(NULLIF(ZBKRKT,0)) as krank, 
SUM(NULLIF(ZBSON2,0))/MAX(zbreaz) as au,  
SUM(NULLIF(ZBSON3,0))/MAX(zbreaz) as unfall,
SUM(NULLIF(ZBSON4,0))/MAX(zbreaz) as covid, 
SUM(NULLIF(ZBSON5,0))/MAX(zbreaz) as kind,
a6beme as abteilung,
MIN(zbdat8) AS first,
MAX(zbdat8) AS last
FROM VEZEIT
inner join vepsta on zbfirm=psfirm and zbpenr=pspenr
inner join veabte on zbfirm=a6firm and psabte=a6abte
where zbpenr='308' and zbdat8 between 20250916 and 20250930 and zbfirm='1'
and zbson3>0.00
group by zbpenr,psnavo,psnafa,a6beme
order by zbpenr	

|nummer|name|krank|au  |unfall|covid|kind|abteilung|first   |last    |
|1     |MM  |null |null|3.74  |null |null|Elektrik |20250916|20250919|

und genau da liegt das Problem, für eine Abfrage zum testen kann ich das so eingrenzen, aber nicht in der App, die App soll einfach die MA zeigen, in den Spalten für zbkrkt → zbson5 was zeigen wenn in der Tabelle vezeit auch was eingetragen war und zusätzlich nur das Datum “first - last” anzeigen, unabhängig von der Datums-Range die man ausgewählt hat.

Wenn ich statt mit “and” dann mit “or” versuche einzugrenzen, dann zeigt er mir absolut jeden MA der jemals in irgendeiner dieser Spalten was eingetragen hat.

ich habe bestimmt einen Denkfehler dabei oder so eine komplexe Abfrage ist garnicht möglich

15

Könntest du mir das näher erläutern, evtl. mit einem Beispiel?

16

Deine Beispieldaten bestätigen meine Vermutung nicht richtig.

Ich bin grad nur am Handy und das einzige was mir auffällt sind die “Zeitketten”. Die Datumsangaben sehen so aus, als würde für jeden Tag AU ein Eintrag gemacht. AU würde also 1-n Einträge erzeugen?

17

Das erste Problem ist, dass Du zusammenhängen Tage als “Einheit” behandeln musst, und dafür musst Du die identifizieren. Das ist bekann als “Gaps and Islands” Problem: eine Abwesenheit ist definiert als die Liste der Tage ohne “Lücken”.

Mit numerischen Werten ist dies mit einem Trick ganz gut zu erreichen, in Deinem Fall muss man die Zahl in ein “richtiges” Datum umwandeln (weil z.B. die Differenz zwischen 20251001 und 20250930 71 ist, aber als “Datum” 1 ist)

select zbfirm,zbpenr,zbreaz,zbkrkt,zbson2,zbson3,zbson4,zbson5,zbdat8, 
       zbdat8::text::date - row_number() over(partition by zbpenr order by zbdat8) nr
from vezeit

Der Trick ist der Wert der für “nr” erzeugt wird: zusammenhängende Tage bekommen alle die gleiche “nr” - der Wert ist unerheblich, wichtig ist nur, dass man so die zusammenhängenden gruppieren kann um den Start und das Ende korrekt ermitteln zu können:

Ich mache sowas gerne mit “common table expressions” um die einzelnen Schritte nachvollziehen zu können:

with clean as (
  select zbfirm,zbpenr,zbreaz,zbkrkt,zbson2,zbson3,zbson4,zbson5,
         to_date(zbdat8::text, 'yyyymmdd') as zbdat8
  from vezeit
), gruppen as (
  select zbfirm,zbpenr,zbreaz,zbkrkt,zbson2,zbson3,zbson4,zbson5,zbdat8, 
        zbdat8 - (row_number() over (partition by zbpenr order by zbdat8))::int abwesenheit
  from clean
), agg as (
  select zbfirm, 
         zbpenr, 
         nullif(max(zbreaz),0) as zbreaz,
         nullif(sum(zbkrkt),0) as krank,
         nullif(sum(zbson2),0) as au,
         nullif(sum(zbson3),0) as unfall,
         nullif(sum(zbson4),0) as covid,
         nullif(sum(zbson5),0) as kind,
         min(zbdat8) as anfang,
         max(zbdat8) as ende,
         daterange(min(zbdat8), max(zbdat8), '[]') as tage
  from gruppen
  group by zbfirm, zbpenr, abwesenheit
)
select *
from agg  
order by zbpenr, anfang

Das erste “clean” habe ich nur verwendet, damit ich die Konvertierung Zahl → Datum nicht ständig wiederholen muss. Die Abfrage “gruppe” generiert dann die entsprechenden “Marker” für zusammenhängende Tage (=abwesenheit). Danach wird darüber gruppiert.

Das finale Ergebnis lässt sich dann leicht über die daterange filtern, z.B: mit

....
select *
from agg  
where tage && daterange('2025-09-16', '2025-09-30', '[]')
order by zbpenr, anfang

Der “overlaps” operator liefert dabei jede Abwesenheit die in diesen Zeitraum fällt, also vollständig in dem Zeitraum liegt, oder in diesem Zeitraum beginnt oder endet. Wenn Du nur Abwesenheiten willst, die vollständig in diesem Zeitraum liegen, nimm <@

Das Dividieren durch zbreaz und die Joins zu den anderen Tabellen kannst Du dann im finalen SELECT integrieren.

Ich habe mal ein Beispiel erstellt: Postgres 17 | db<>fiddle

Aber: Ich habe derzeit keine Lösung für Abwesenheiten die über ein Wochenende (oder Feiertag) gehen, da ja dann wieder Lücken in den Werten für zbdat8 auftreten, die aber in Wirklichkeit keine sind, und somit der Marker “abwesenheit” falsch erzeugt wird.

18

Schon mal vielen Dank, sieht gut aus, ich müßte es testen wenn ich wieder am PC bin.

Probleme sehe ich eher an dieser Stelle

select *
from agg  
where tage <@ daterange('2025-09-14', '2025-09-30', '[]')
order by zbpenr, anfang
;

da ich in der Originalabfrage das Datum aus 2 Auswahlkästen hole, und die Variablen vordefiniert habe

int datevon = new XDate(this.ZBDAT8Von.getDate()).getDate8();
int datebis = new XDate(this.ZBDAT8Bis.getDate()).getDate8();

where zbpenr>'1' and pseitm>'0' and psatd8='0' and zbdat8 between " + datevon + " and " + datebis + "  and zbfirm=" + this.firma + this.getFilters() + " "

da muß ich mir überlegen wie ich da daterange umbaue weil das hier wird vermutlich nicht funktionieren

select *
from agg  
where tage <@ daterange('datevon', 'datebis', '[]')
order by zbpenr, anfang
;
19

Hängt von der Programmiersprache ab.

Wenn Du Parameter übergeben kannst, sollte so etwas gehen:

where tage <@ daterange($1, $2, '[]')

Wenn Du die SQL Abfrage im Code “zusammenbaust”, dann kannst Du ja auch die Datumswerte direkt als Wert reinschreiben (wie in meinem Beispiel)

20

Die Programmiersprache ist Java.

Schade daß ich das am Smartphone nicht testen kann.

Gibt es eigentlich nicht so ein https://dbfiddle.uk für Java und Konsorten?