Summe von einer Spalte auf 0 zurücksetzen

Hallo (und noch schönesneues Jahr :slight_smile: )
ich habe mir ein recht komplexes SQL-Statement gebastelt und brauche noch einen guten Tip, um das was ich damit bewerkstelligen möchte zu erreichen.
Zum Verständnis: ein Projekt kann viele Aufgaben haben. Diese Aufgaben haben so etwas wie einen geplanten Zeitwert in Tagen, der mittels nicht ganz untrivialem SQL ermittelt wird. Im Grunde funktioniert es bis auf einen kleinen Teil schon ganz gut und zwar meine ich den Teil, wo ich ein sogenanntes “running total” fabriziere, dass ich gerne bei einem Wechsel auf ein neues “Projekt” zurück auf 0 setzen möchte.
Mein Ergebnis sieht zur Zeit mit dem Beispiel von 2 Projekten wie folgt aus:

Mein Ziel ist es das Anfangsdatum als auch das Enddatum einer Aufgabe in die Ausgabe zu bringen. Irgendwie brauche ich Denkanstöße,da ich nicht genau weiß wie ich es gestalten muss.

Hier ist mein aktuelles Statement:

select ordnungszahl
     , zonk.projekt_id
	 , start_datum
	 , aufgaben_id
	 , aufwand_geschaetzt
	 , aufgabenzeit_geplant
	 ,sum(aufgabenzeit_geplant) over (order by zonk.projekt_id,ordnungszahl 
					     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
	 , start_datum + sum(aufgabenzeit_geplant) over (order by zonk.projekt_id, ordnungszahl 
					     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )::integer aufgaben_enddatum 
from (
select  
	ordnungszahl, projekt_id, aufgaben_id, aufwand_geschaetzt,
    case when aufwand_geschaetzt > (select greatest(0, sum(aufwand_geplant))
                                      from "projekt-skill-verwalter".aufgabe_mitarbeiter 
                                     where aufgaben_id = a1.aufgaben_id)
         then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
	                                           from "projekt-skill-verwalter".aufgabe_mitarbeiter 
                                              where aufgaben_id = a1.aufgaben_id))
  	    else (select max(aufwand_geplant)
	            from "projekt-skill-verwalter".aufgabe_mitarbeiter 
	  	       where aufgaben_id = a1.aufgaben_id)
    end ::integer as aufgabenzeit_geplant
 from "projekt-skill-verwalter".aufgabe a1
where aufgaben_id in (select aufgaben_id 
                        from "projekt-skill-verwalter".aufgabe a2
                       where a2.projekt_id in (select projekt_id
											     from "projekt-skill-verwalter".projekt)
                         and a2.ordnungszahl <= (select ordnungszahl
												   from "projekt-skill-verwalter".aufgabe
												  where projekt_id = a1.projekt_id
												    and aufgaben_id 
												    in (select aufgaben_id 
													      from "projekt-skill-verwalter".projekt
													     where ordnungszahl =  
															(select max(ordnungszahl) 
															 from "projekt-skill-verwalter".aufgabe
															 where projekt_id = a1.projekt_id
															)
													   ) 
												)
					  )
order by projekt_id, ordnungszahl				  
) as zonk
inner join "projekt-skill-verwalter".projekt
on zonk.projekt_id = projekt.projekt_id
order by projekt_id, ordnungszahl

Nebenbei habe ich bei einigen Experimenten festgestellt, dass ich offenbar die Spalten, mit denen ich diese Additionen mache trotz Aliass-Benennungen nicht weiter im Selektstatement refferenzieren kann. Bekomme dann immer den Fehler er würde die Spalte nicht kennen… Gibt es da ein Mittel für?
Aber wichtiger ist natürlich eine Idee für Zurücksetzen der Summe bei wechsel des Projekts in der Ergebnistabelle.

vielen Dank im Voraus und nette Grüße

Takidoso

für das ‘zurücksetzen’ gibt es bei Window-Funktionen das PARTITION BY, hier nach Deiner Projekt_id. Auf Aliase, die Du im Select vergibst, kannst Du NICHT im selben Level der Abfrage zugreifen. Das ist so per SQL-Spec. Nutze Subselects oder CTEs - oder wiederhole den Ausdruck einfach.

Ja, danke für den Hinweis. Habe eben auch nochmal gegoogelt und bin auf das zauberwort PARTITION gestoßen, und siehe da ich bin ein Schritt weiter.
Aktuell ist mein Statement nun so aus:

select ordnungszahl
     , zonk.projekt_id
	 , start_datum
	 , aufgaben_id
	 , aufwand_geschaetzt
	 , aufgabenzeit_geplant
	 ,sum(aufgabenzeit_geplant) over (partition by zonk.projekt_id 
					     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )
	 , start_datum + sum(aufgabenzeit_geplant) over (partition by zonk.projekt_id 
					     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW )::integer aufgaben_enddatum 
from (
select  
	ordnungszahl, projekt_id, aufgaben_id, aufwand_geschaetzt,
    case when aufwand_geschaetzt > (select greatest(0, sum(aufwand_geplant))
                                      from "projekt-skill-verwalter".aufgabe_mitarbeiter 
                                     where aufgaben_id = a1.aufgaben_id)
         then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
	                                           from "projekt-skill-verwalter".aufgabe_mitarbeiter 
                                              where aufgaben_id = a1.aufgaben_id))
  	    else (select max(aufwand_geplant)
	            from "projekt-skill-verwalter".aufgabe_mitarbeiter 
	  	       where aufgaben_id = a1.aufgaben_id)
    end ::integer as aufgabenzeit_geplant
 from "projekt-skill-verwalter".aufgabe a1
where aufgaben_id in (select aufgaben_id 
                        from "projekt-skill-verwalter".aufgabe a2
                       where a2.projekt_id in (select projekt_id
											     from "projekt-skill-verwalter".projekt)
                         and a2.ordnungszahl <= (select ordnungszahl
												   from "projekt-skill-verwalter".aufgabe
												  where projekt_id = a1.projekt_id
												    and aufgaben_id 
												    in (select aufgaben_id 
													      from "projekt-skill-verwalter".projekt
													     where ordnungszahl =  
															(select max(ordnungszahl) 
															 from "projekt-skill-verwalter".aufgabe
															 where projekt_id = a1.projekt_id
															)
													   ) 
												)
					  )
order by projekt_id, ordnungszahl				  
) as zonk
inner join "projekt-skill-verwalter".projekt
on zonk.projekt_id = projekt.projekt_id
order by projekt_id, ordnungszahl

Nun frage ich mich allerdings noch wie ich es erreichen kann, dass ich anstelle des Anfangsdatums der nächsten Aufgabe, das Startdatum der aktuellen Aufgabe bekomme. Irgendwie müsste ich dann in der Window-Funktion sagen, dass eine Zeile später der Wert zu addieren sei. :thinking:
Bin bei weiterem Stöbern auf LAG() gestoßen. Aber müsste man dann nicht LAG mit SUM kombinieren? Wie ginge das syntaktisch?

Ahh ich glaube ich wurde nun doch zu diesem Thema hier fündig.

Das probiere ich mal aus und berichte hier, ob es funktioniert hat.

Hier ist mein Stand mit Start- und Enddatum. Wochenenden werden hier z.Z. noch nicht berücksichtigt, das wäre dann ein späterer Schritt, dass die Aufgabenendzeitpunkte entsprechend der Wochenenden innerhalb der Aufgabenzeitspanne nach hinten verlegt werden müssten.

select ordnungszahl
     , zonk2.projekt_id
	 , start_datum
	 , 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
	 , 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 "projekt-skill-verwalter".aufgabe_mitarbeiter 
        	                             where aufgaben_id = a1.aufgaben_id)
	         then CEILING (aufwand_geschaetzt::float / (select greatest(1, count(*))
	                                           from "projekt-skill-verwalter".aufgabe_mitarbeiter 
                                              where aufgaben_id = a1.aufgaben_id))
  		    else (select max(aufwand_geplant)
	    	        from "projekt-skill-verwalter".aufgabe_mitarbeiter 
	  	    	   where aufgaben_id = a1.aufgaben_id)
   	    end ::integer as aufgabenzeit_geplant
 	  from "projekt-skill-verwalter".aufgabe a1
	 where aufgaben_id in (select aufgaben_id 
     	                     from "projekt-skill-verwalter".aufgabe a2
        	                where a2.projekt_id in (select projekt_id
												      from "projekt-skill-verwalter".projekt)
                	          and a2.ordnungszahl <= (select ordnungszahl
							 						    from "projekt-skill-verwalter".aufgabe
							 						   where projekt_id = a1.projekt_id
													     and aufgaben_id 
												          in (select aufgaben_id 
													            from "projekt-skill-verwalter".projekt
										 		 		       where ordnungszahl =  
																(select max(ordnungszahl) 
															  	   from "projekt-skill-verwalter".aufgabe
															  	  where projekt_id = a1.projekt_id
																)
													   		) 
													)
					  )
			order by projekt_id, ordnungszahl				  
	) as zonk1
	inner join "projekt-skill-verwalter".projekt
	on zonk1.projekt_id = projekt.projekt_id
	order by projekt_id, ordnungszahl
	) as zonk2
1 „Gefällt mir“

Dieses Thema wurde automatisch 60 Tage nach der letzten Antwort geschlossen. Es sind keine neuen Antworten mehr erlaubt.