Query mit Datumsberechnung vereinfachen

Hallo,

an folgender Aufgabe zerbröselt es mich bzw. die Query wird furchtbar
unübersichtlich:

das voraussichtliche Enddatum einer Aktivität soll für die Selektion
von Daten verwendet werden.

Das voraussichtliche Enddatum ist dabei NOW() + dem Restaufwand. Der
Restaufwand ist der geschätzte Aufwand - bereits geleisteten Stunden.
Allerdings soll der Restaufwand nicht negativ werden (also das vor.
Enddatum in die Vergangenheit schieben), wenn bereits mehr geleistet
als geschätzt wurde - in diesem Fall soll 0 als Restaufwand genommen
werden.

So schaut es:

Der Restaufwand:
last_det.est_effort * 8 - ( SELECT SUM(ln.dauer) FROM ln WHERE
ln.lk=3D0 AND ln.pts_call_id =3D pts_call.call_id )

Der Restaufwand der nicht kleiner 0 wird:
IF((last_det.est_effort * 8 - ( SELECT SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id )) <0 ,0,
(last_det.est_effort * 8 - ( SELECT SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id )))

Auch muss 0 geliefert werden, wenn der Select SUM() ein null liefert:
IF((last_det.est_effort * 8 - ( SELECT SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id )) <0 || ( SELECT
SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id ) is null ,0,
(last_det.est_effort * 8 - ( SELECT SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id )))


Jetzt muss noch der Restaufwand zu NOW() addiert werden

DATE_ADD(NOW(), INTERVAL (IF((last_det.est_effort * 8 -
( SELECT SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id )) <0 || ( SELECT
SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id ) is null,0,
(last_det.est_effort * 8 - ( SELECT SUM(ln.dauer)
FROM ln
WHERE ln.lk=3D0
AND ln.pts_call_id =3D pts_call.call_id )))) HOUR) AS
calculated_end_date

und nun kann ich in der WHERE Klausel prüfen, ob das
calculated_end_date hinter dem zugesicherten Fertigstellungsdatum
liegt.

Funktionieren tut es, aber ich halte es für sehr unschön und schlecht
wartbar (in der Query kommen ohnehin noch einige INNER und LEFT JOIN
vor).

Über Anregungen wie die Query besser formuliert werden könnte würde
ich mich freuen (sorry wegen der unübersichtlichen Formatierung, aber
ist im Browser schwer ordentlich zu formatieren).

Danke und Gruß
Thomas

Re: Query mit Datumsberechnung vereinfachen

On Mon, 21 May 2007 07:14:12 -0700, Thomas wrote:

> Hallo,
>
> an folgender Aufgabe zerbröselt es mich bzw. die Query wird furchtbar
> unübersichtlich:
>
> das voraussichtliche Enddatum einer Aktivität soll für die Selektion von
> Daten verwendet werden.
>
> Das voraussichtliche Enddatum ist dabei NOW() + dem Restaufwand. Der
> Restaufwand ist der geschätzte Aufwand - bereits geleisteten Stunden.
> Allerdings soll der Restaufwand nicht negativ werden (also das vor.
> Enddatum in die Vergangenheit schieben), wenn bereits mehr geleistet als
> geschätzt wurde - in diesem Fall soll 0 als Restaufwand genommen werden.
>
> So schaut es:
>
> Der Restaufwand:
>
>
> Der Restaufwand der nicht kleiner 0 wird:

GREATEST(bla, 0)

> Auch muss 0 geliefert werden, wenn der Select SUM() ein null liefert:

Irgendwas minus NULL ist NULL, also reicht ein einfaches COALESCE um
gleich alles zu 0 zu machen

> Jetzt muss noch der Restaufwand zu NOW() addiert werden

NOW() + INTERVAL bla HOUR sieht IMHO schicker aus

Insgesamt heisst das:

NOW() + INTERVAL GREATEST(0,
COALESCE(
last_det.est_effort * 8 - (
SELECT
SUM(ln.dauer)
FROM
ln
WHERE
ln.lk=0 AND ln.pts_call_id = pts_call.call_id
),
0
)
) HOUR

HTH
Björn

Re: Query mit Datumsberechnung vereinfachen

Hallo Björn,

yes, that helps.

> GREATEST(bla, 0)
>
> > Auch muss 0 geliefert werden, wenn der Select SUM() ein null liefert:
>
> Irgendwas minus NULL ist NULL, also reicht ein einfaches COALESCE um
> gleich alles zu 0 zu machen
>
> > Jetzt muss noch der Restaufwand zu NOW() addiert werden
>
> NOW() + INTERVAL bla HOUR sieht IMHO schicker aus

Vielen Dank für die Mühe, die Du Dir gemacht hast. COALESCE() war mir
bislang noch nicht über den Weg gelaufen - das ist natürlich sehr
elegant. Mich störte insbesondere, dass in meinem Ansatz der gleiche
SELECT SUM() dreimal vorkommt. Da sind bei Anpassungen schnell mal
zwei geändert und der dritte übersehen...

Nochmals vielen Dank - das ist wirklich eine schöne Lösung.

Gruß
Thomas

Re: Query mit Datumsberechnung vereinfachen

Hallo Björn,

kleine Korrektur. Keine Kritik - schließlich musstest Du es "trocken"
aufschreiben.

Anstelle

rest =3D COALESCE( aufwand - geleistet, 0 )

muss es
rest =3D aufwand - COALESCE( geleistet, 0 )

sein. Ansonsten würde, wenn geleistet null ergibt immer null als
Ergebnis herauskommen. Sprich, der Restaufwand wäre 0 wenn noch nichts
geleistet wurde.

Gruß
Thomas

Re: Query mit Datumsberechnung vereinfachen

On Tue, 22 May 2007 02:03:16 -0700, Thomas wrote:

> Hallo Björn,
>
> kleine Korrektur. Keine Kritik - schließlich musstest Du es "trocken"
> aufschreiben.
>
> Anstelle
>
> rest = COALESCE( aufwand - geleistet, 0 )
>
> muss es
> rest = aufwand - COALESCE( geleistet, 0 )
>
> sein. Ansonsten würde, wenn geleistet null ergibt immer null als
> Ergebnis herauskommen. Sprich, der Restaufwand wäre 0 wenn noch nichts
> geleistet wurde.

Hm, letzteres deckt sich aber nicht mehr mit deinem Original, oder? Das
war ja (angenommen ich habe mich nicht im Dschungel der Klammern verirrt):
IF (aufwand - geleistet < 0 || geleistet IS NULL, 0, aufwand - geleistet)

Deine angepasste COALESCE() Variante wäre ja:
aufwand - IF(aufwand - geleistet < 0 || geleistet IS NULL, 0, geleistet)

Die angepasste Lösung sieht schon sinnvoller aus, aber ich glaube sie ist
nicht das, was deine ursprüngliche Anfrage ausgedrückt hat? Oder hab ich
mich da doch in den Klammern verirrt? ;)

Björn

Re: Query mit Datumsberechnung vereinfachen

Hallo Björn,

> Die angepasste Lösung sieht schon sinnvoller aus, aber ich glaube sie i=
st
> nicht das, was deine ursprüngliche Anfrage ausgedrückt hat? Oder hab =
ich
> mich da doch in den Klammern verirrt? ;)

hast Du nicht - meine Formulierung war nicht korrekt. Du hast es dann
in ein korrektes SQL übersetzt.

Ich war bei einem Test angenehm überrascht - ein richtiger
Mainzelmännchen-Effekt: In fast alle Projekten gibt es zu den
Einzelaktivitäten keinen Restaufwand mehr, obwohl noch niemand etwas
gemacht hat :-) Die Realität sieht aber leider doch etwas anders aus.

Eigentlich dachte ich, dass ich den SQL-Sprachumfang recht gut kenne -
aber so ist das wohl, wenn man erstmal mit einem liebgewonnenen
Befehlsumfang unterwegs ist. Ich werde mal eine Referenz durchblättern
und schauen, ob ich nicht noch andere Nettigkeiten wie COALESCE()
bislang ignoriert habe.

Nochmals vielen Dank
Thomas