SQL: Kosten anteilig auf Jahre aufteilen

[DCoderHH]

Ich habe Nutzungszeiten und Kosten von Gegenständen. Die Kosten sollen nun pro Jahr dargestellt werden. Dabei ist zu beachten, dass die Nutzungszeiten über keine oder beliebig viele Jahresgrenzen gehen können. Die Kosten sollen prozentual nach der Nutzung in einem Jahr aufgeteilt werden. D.h. wenn z.B. 20 % des Nutzungszeitraumes eines Gegenstandes im Jahr 2018 liegen, sollen auch 20% seiner Kosten diesem Jahr zugeordnet werden.


Ich habe diese Tabelle:

Code:

Nutzungszeiten
--------------
ID       Integer
Von      Date
Bis      Date
Kosten   Numeric(18,4)

Mit diesen Beispieldaten:

Code:

ID   VON          BIS          KOSTEN
1   01.07.2017  01.08.2018  120
2   22.12.2017  10.01.2018  100
3   07.02.2019  09.03.2021  500

Und möchte diese Auswertung erhalten:

Code:

JAHR   KOSTENANTEIL   (Erklärung)
2017  170           (100 % von ID1 120 + 50 % von ID2 100) 
2018   50           (50 % von ID2 100)
2019  215           (43 % von ID3 500)  328 Tage / 762 Tage = 43 %
2020  240           (48 % von ID3 500)  366 Tage / 762 Tage = 48 %
2021   45           ( 9 % von ID3 500)  68 / 762 Tage = 9 %

Rundungsdifferenzen sollen zum letzten Zeitraum addiert werden. Es darf nichts verloren gehen.

Wie fange ich am Besten an? Brauche ich dafür irgendwie eine zweite Tabelle mit den Jahren/Tagen? Oder geht das alleine mit Group by? Danke!

[himitsu]

* MIN und MAX der Jahre aller Datensätze
* für die Abfrage der Daten der jeweiligen Jahre:
eine Serie über MIN..MAX ( http://www.delphipraxis.net/188280-l...-finden-2.html )
oder einfach nur alle Jahre der Datensätze (DISTINCT ORDER BY und so)
* jeweils TAGE zwischen VON und BIS (+1, also inkl. des letzten Tages)
und TAGE zwischen Jahresanfang Max(JAHR, YearOf(VON))::DATE und Jahresende (Min(JAHR, YearOf(BIS))+1)::DATE-'1 day' ... auch +1
> damit kannst du schonmal den Anteile ausrechnen
* und nun erstmal die abgerundeten anteiligen Kosten ausrechnen

so, das am Besten gleich mal in nen VIEW, weil du das nochmal brauchst (oder du rechnest halt doppelt)

SELECT auf den VIEW und wenn YearOf(BIS) = YAHR, dann die Gesamtkosten des Datensatzes abzüglich der Kosten der Teiljahre und das Ergebnis (Rundungsdifferenzen) mit dazurechnen
und am Ende nochmal ein GROUP BY Jahr drüber (oder ohne GROUP, um zu sehn was von wo kommt)

[jobo]

Zitat von himitsu:

Code:

.. YearOf(VON))::DATE .. (Min(JAHR, YearOf(BIS))+1)::DATE-'1 day' ... auch +1 ..

ist postgres Syntax und müsste an Firebird angepasst werden.

[himitsu]

Ist mehr ein gekürzter DB-unabhängiger Pseudocode.

Das "Min" dort heißt auch anders, aber das hätte das DBMS an der Stelle auch bemängelt und wäre bestimmt wer auf die Idee gemommen die richtigen "Kleinster/Größter" zu verwenden.

[jobo]

Ich würde es eher "DB abhängiger Pseudo Code" nennen
Aber egal, guter Stoff mit vielen nützlichen Hinweisen!
Und wir sind ja in einem Forum, man kann im allergrößten Notfall nachfragen.