Bookmarks

Yahoo Gmail Google Facebook Delicious Twitter Reddit Stumpleupon Myspace Digg

Search queries

dbf2mysql parameter, WWWXXXAPC, wwwxxxAPC, How to unsubscrube from dategen spam, docmd.close 2585, WWWXXXDOCO, nu vot, dhcpd lease file "binding state", WWWXXXDOCO, how to setup procmail to process html2text

Links

XODOX
Impressum

#1: Query mit Datumsberechnung vereinfachen

Posted on 2007-05-21 16:14:12 by thomas

Hallo,

an folgender Aufgabe zerbr=F6selt es mich bzw. die Query wird furchtbar
un=FCbersichtlich:

das voraussichtliche Enddatum einer Aktivit=E4t soll f=FCr die Selektion
von Daten verwendet werden.

Das voraussichtliche Enddatum ist dabei NOW() + dem Restaufwand. Der
Restaufwand ist der gesch=E4tzte 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=E4tzt 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=FCfen, ob das
calculated_end_date hinter dem zugesicherten Fertigstellungsdatum
liegt.

Funktionieren tut es, aber ich halte es f=FCr sehr unsch=F6n und schlecht
wartbar (in der Query kommen ohnehin noch einige INNER und LEFT JOIN
vor).

=DCber Anregungen wie die Query besser formuliert werden k=F6nnte w=FCrde
ich mich freuen (sorry wegen der un=FCbersichtlichen Formatierung, aber
ist im Browser schwer ordentlich zu formatieren).

Danke und Gru=DF
Thomas

Report this message

#2: Re: Query mit Datumsberechnung vereinfachen

Posted on 2007-05-21 18:52:49 by B.Steinbrink

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

Report this message

#3: Re: Query mit Datumsberechnung vereinfachen

Posted on 2007-05-22 10:12:13 by thomas

Hallo Bj=F6rn,

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=FCr die M=FChe, die Du Dir gemacht hast. COALESCE() war mir
bislang noch nicht =FCber den Weg gelaufen - das ist nat=FCrlich sehr
elegant. Mich st=F6rte insbesondere, dass in meinem Ansatz der gleiche
SELECT SUM() dreimal vorkommt. Da sind bei Anpassungen schnell mal
zwei ge=E4ndert und der dritte =FCbersehen...

Nochmals vielen Dank - das ist wirklich eine sch=F6ne L=F6sung.

Gru=DF
Thomas

Report this message

#4: Re: Query mit Datumsberechnung vereinfachen

Posted on 2007-05-22 11:03:16 by thomas

Hallo Bj=F6rn,

kleine Korrektur. Keine Kritik - schlie=DFlich musstest Du es "trocken"
aufschreiben.

Anstelle

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

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

sein. Ansonsten w=FCrde, wenn geleistet null ergibt immer null als
Ergebnis herauskommen. Sprich, der Restaufwand w=E4re 0 wenn noch nichts
geleistet wurde.

Gru=DF
Thomas

Report this message

#5: Re: Query mit Datumsberechnung vereinfachen

Posted on 2007-05-22 11:26:50 by B.Steinbrink

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

Report this message

#6: Re: Query mit Datumsberechnung vereinfachen

Posted on 2007-05-22 11:57:52 by thomas

Hallo Bj=F6rn,

> Die angepasste L=F6sung sieht schon sinnvoller aus, aber ich glaube sie i=
st
> nicht das, was deine urspr=FCngliche Anfrage ausgedr=FCckt 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 =FCbersetzt.

Ich war bei einem Test angenehm =FCberrascht - ein richtiger
Mainzelm=E4nnchen-Effekt: In fast alle Projekten gibt es zu den
Einzelaktivit=E4ten keinen Restaufwand mehr, obwohl noch niemand etwas
gemacht hat :-) Die Realit=E4t 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=E4ttern
und schauen, ob ich nicht noch andere Nettigkeiten wie COALESCE()
bislang ignoriert habe.

Nochmals vielen Dank
Thomas

Report this message