Wochenendekorrekturen

[Takidoso]

Hallo,
ich bin gerade dabei bei meinem SQL-Statement, dass für Ermittlung der Zeitplanung einzelner Aufgaben in Projekten zuständig ist eine Wochenend-Korrektur einzubauen.
Habe daher eine kleine Stored Function gebaut, die die dazu drauf zurechenenden Tage ermittelt.
Die Korrekturen von Start- und Ende-“Datümer” ist insofern doch weniger leicht, als ich zunächst annahm, da zum einen das vorangegangene korrigierte Datum eine weitere Korrektur des Nachfolgers nach sich zieht, zum anderen in einem Select leider eine gerade neu gebaute Spalte sich nicht innerhalb des selben Select-Levels addressieren lässt. Bin nun gar nicht sicher, ob ich zu betriebsblind bin, oder es so ohne weiteres gar nicht in SQL geht.
Hier ist mein bisheriges Statement

select ordnungszahl
     , projekt_id
	 , start_datum
	 , mitarbeiter_id
	 , aufgaben_id
	 , aufwand_geschaetzt
	 , aufgabenzeit_geplant_pre
	 , count_weekend_days(aufgaben_startdatum, aufgaben_enddatum) weekend_days
	 , aufgaben_startdatum_pre 
	 , aufgaben_enddatum_pre 
	 , aufgaben_startdatum 
	 , aufgaben_enddatum 
	 , aufgaben_startdatum + count_weekend_days(aufgaben_startdatum_pre, aufgaben_enddatum_pre) start_korr
	 , aufgaben_enddatum + count_weekend_days(aufgaben_startdatum, aufgaben_enddatum) end_korr
from (
select ordnungszahl
     , projekt_id
	 , start_datum
	 , mitarbeiter_id
	 , aufgaben_id
	 , aufwand_geschaetzt
	 , aufgabenzeit_geplant_pre
	-- , count_weekend_days(aufgaben_startdatum, aufgaben_enddatum) weekend_days
	 , lag(aufgaben_startdatum,1) over (partition by projekt_id) aufgaben_startdatum_pre
	 , aufgaben_startdatum
	 , lag(aufgaben_enddatum,1) over (partition by projekt_id) aufgaben_enddatum_pre
	 , aufgaben_enddatum
from(
select ordnungszahl
     , zonk2.projekt_id
	 , start_datum
	 , mitarbeiter_id
	 , aufgaben_id
	 , aufwand_geschaetzt
	 , aufgabenzeit_geplant_pre
	 , start_datum + sum(aufgabenzeit_geplant_pre) over (partition by zonk2.projekt_id 
													     order by ordnungszahl)::integer aufgaben_startdatum
	 , start_datum - 1 + sum(aufgabenzeit_geplant) over (partition by zonk2.projekt_id 
													     order by ordnungszahl)::integer aufgaben_enddatum
  from
  (select ordnungszahl
     , zonk1.projekt_id
	 , start_datum
     , mitarbeiter_id
	 , am.aufgaben_id
	 , aufwand_geschaetzt
	 , aufgabenzeit_geplant
	 ,lag(aufgabenzeit_geplant,1, 0) over (partition by zonk1.projekt_id
									  order by ordnungszahl) aufgabenzeit_geplant_pre
	from (
	  select  
		 ordnungszahl, projekt_id, aufgaben_id, aufwand_geschaetzt,
	     case when aufwand_geschaetzt > (select greatest(0, sum(aufwand_geplant))
    	                                  from aufgabe_mitarbeiter 
         	                             where aufgaben_id = a1.aufgaben_id)
	          then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
	                                                       from aufgabe_mitarbeiter 
                                                          where aufgaben_id = a1.aufgaben_id))
  		     else (select max(aufwand_geplant)
	    	         from aufgabe_mitarbeiter 
	  	    	    where aufgaben_id = a1.aufgaben_id)
   	     end ::integer as aufgabenzeit_geplant
 	   from aufgabe a1

	 order by projekt_id, ordnungszahl				  
	) as zonk1
	inner join projekt
	on zonk1.projekt_id = projekt.projekt_id
    inner join aufgabe_mitarbeiter am
    on  zonk1.aufgaben_id = am.aufgaben_id
   where projekt.start_datum  between '2022-02-01' and '2022-03-28'
      or  projekt.end_datum   between '2022-02-01' and '2023-03-28'
	order by projekt_id, ordnungszahl
	) as zonk2
) as zonk3
) as zonk4

Und meine daraus resultierend Ausgabe ist folgende:

Schon in der 2. Ausgabezeile fällt in der Spalte end_korr (Korrigiertes Enddatum) auf, dass es nicht richtig ist, was ich da tue. Natürlich muss man hier den Inhalt von “end_korr” für die nachfolgenden Wert von “end_korr” verwenden, so dass nicht zum 24.02.2022 sondern 27.02.2022 kommt.
Hat jemand eine Idee wie dies bewerkstelligt werden kann?

Mit nachdenklichen Grüßen

Takidoso

[akretschmer]

das select etc. hab ich mir nicht angeschaut, aber als Hinweis: Du kannst nicht auf einen Alias im selben Level zugreifen, aber du kannst den Ausdruck, der das Feld berechnet, durchaus wieder verwenden.

Was sich auch gut macht sind CTE-Abfragen, zumal diese (in aktuellen Versionen) nun auch besser optimiert werden als früher.

[pogomips]

@Takidoso

Schon in der 2. Ausgabezeile fällt in der Spalte end_korr (Korrigiertes Enddatum) auf, dass es nicht richtig ist

Dir fällt es offensichtlich auf. Es ist für Dritte relativ mühsam, solch eine Feststellung und sogar die Korrektur allein anhand eines Select Statements ohne Datenmodell und ohne Daten zu treffen.

Neben dem Vorschlag mit den CTE kannst Du auch einfach eine Selekt Ebene “höher” ansetzen. So kannst Du komplexe Ausdrücke “verstecken”, sogar wörtlich, wenn Du die vorige Ebene als View definierst.

Beispiel

Select * from 
      (Select <Feldliste>, <komlexer Ausdruck1> as ka1, komplexer Ausdruck 2> as ka2 
         from <viele Joins>
           on <viele Join Kriterien>) KomplexAlias
 where KomplexAlias.ka1 > KONSTANT
          .... usw.

Dabei gehst Du so vor, dass Du innen alle Werte selektierst oder berechnest, die Du außen oder noch später benötigst und die im inneren Select auf Anhieb “erreichbar” bzw. berechenbar sind.
Nächste Ebene dann mit diesen Daten weiter arbeiten und z.B. die neuen Werte für ein UPDATE berechnen.

Eigentlich ist es nicht viel anders als CTE, u.U. kompatibler, auf jeden Fall historisch älter und überall gangbar und in einigen Fällen nicht so performant / schlechter optimierbar durch die DB.