SQL: Jahresanteile des Vertags berechnen?

[nahpets]

Hallo,

hier mal ein Versuch:

Folgende Annahme:

Die Monate werden immer vollständig berechnet und nicht taggenau.

Beispiel: Für Anfangsdatum 01.08.2012 bei einer Vertragsdauer von 6 Monaten entfallen 5 Monate auf das Jahr 2012.
Beispiel: Für Anfangsdatum 31.08.2012 bei einer Vertragsdauer von 6 Monaten entfallen 5 Monate auf das Jahr 2012.

Für den August entfallen auf das Jahr 2012 bei einer Dauer von
1 = 1 Monat
3 = 3 Monate
6 = 5 Monate
12 = 5 Monate.

Dies entspricht dem kleineren Wert von Vertragsdauer und (12 - Monat + 1).
Auf das Jahr 2013 entfallen demnach 12 - diesem Wert.

(Das Beispiel ist nicht getestet sondern nur im Editor so hingeschrieben. Das SQL-Statement bitte von innen nach außen lesen.)

Code:

/* Die Summen für alle Verträge, bei denen der Vertrag im Jahr beginnt oder endet. */
select
  sum(BetragStartJahr) as BetragStartJahr,
  sum(BetragEndeJahr)  as BetragEndeJahr
from (
  /* Nun die Anteile der bezahlten Beträge für das Beginnjahr und das Endejahr berechnen. */
  select
    Anfangsdatum,
    Vertragsdauer,
    BezahlterBetrag,
    Monat,
    StartJahr,
    EndeJahr,
    DauerStartJahr,
    DauerEndeJahr,
    BezahlterBetrag / 12 * DauerStartJahr as BetragStartJahr,
    BezahlterBetrag / 12 * DauerEndeJahr  as BetragEndeJahr
  from (
    /* Feststellen, wieviele Monate auf das Jahr des Vertragsbeginnes entfallen */
    /* bzw. auf das Jahr des Vertragsendes. */
    select
      Anfangsdatum,
      Vertragsdauer,
      BezahlterBetrag,
      Monat,
      StartJahr,
      EndeJahr,
      min(Vertragsdauer,(12 - Monat + 1)) as DauerStartJahr,
      12 - min(Vertragsdauer,(12 - Monat + 1)) as DauerEndeJahr
    from (
      /* Zuerst Monat und Jahr des Vertragsbeginnes bzw. Endes ermitteln. */
      select
        Anfangsdatum,
        Vertragsdauer,
        BezahlterBetrag,
        month(Anfangsdatum) as Monat,
        year(Anfangsdatum)  as StartJahr,
        year(Anfangsdatum) + 1 as EndeJahr
      from testtabelle
      /* Eventuell hier schon das Jahr per SQL einschränken? */
      /* where year(anfangsdatum) between :ParameterJahr - 1 and :ParameterJahr + 1 */
    ) a
  ) b
  /* Auswahlmenge auf das gewünschte Jahr einschränken. */
  where StartJahr = :ParameterJahr
  or    EndeJahr  = :ParameterJahr
) c

Wenn's denn funktionieren sollte, so funktioniert es auch noch in 100 Jahren, da die Jahreszahl per Parameter an das SQL übergeben wird.

Eventuell geht's auch noch etwas kürzer, aber nicht zwingend lesbarer (und durch redundante Berechnungen eventuell langsamer?):

Code:

/* Die Summen für alle Verträge, bei denen der Vertrag im Jahr beginnt oder endet. */
select
  sum(BetragStartJahr) as BetragStartJahr,
  sum(BetragEndeJahr)  as BetragEndeJahr
from (
  /* Monat und Jahr des Vertragsbeginnes bzw. Endes ermitteln. */
  /* Feststellen, wieviele Monate auf das Jahr des Vertragsbeginnes entfallen */
  /* bzw. auf das Jahr des Vertragsendes. */
  /* Die Anteile der bezahlten Beträge für das Beginnjahr und das Endejahr berechnen. */
  select
    Anfangsdatum,
    Vertragsdauer,
    BezahlterBetrag,
    year(Anfangsdatum)  as StartJahr,
    year(Anfangsdatum) + 1 as EndeJahr,
    BezahlterBetrag / 12 * (min(Vertragsdauer,(12 - month(Anfangsdatum) + 1))) as BetragStartJahr,
    BezahlterBetrag / 12 * (12 - min(Vertragsdauer,(12 - month(Anfangsdatum) + 1)) as BetragEndeJahr
  from testtabelle
  where year(anfangsdatum) between :ParameterJahr - 1 and :ParameterJahr + 1
) a
/* Auswahlmenge auf das gewünschte Jahr einschränken. */
where StartJahr = :ParameterJahr
or    EndeJahr  = :ParameterJahr