SQL: Jahresanteile des Vertags berechnen?

[romber]

Hallo!

Ich habe eine Testtabelle, in der über 100.000 Verträge gespeichert sind. Jeder Vertrag hat unter anderem "Anfangsdatum", "Vertragsdauer" (in Monaten, mögliche Werte sind 1, 3, 6 und 12) und "Bezahlter Betrag". Ich möchte ich z. B. wissen, welcher Betrag aus allen Verträgen auf das Jahr 2012 anfällt. Das lässt sich errechnen, indem ich

1. Den Monatsanteil des Preises aus Vertragsdauer und Preis ermittle
2. Die Zahl der Vertragsmonate im Jahr 2012 aus Anfangsdatum und Vertragsdauer errechne
3. 1 mit 2 multipliziere

Nun muss ich dafür eine SQL-Abfrage erstellen. Mangels tiefgreifender SQL-Kenntnisse liste ich testweise alle Vertäge auf und führe für jeden Vertrag die o.g. Schritte aus. Nach zwei Raucherpausen kommt der Euro-Betrag raus


Das muss doch alles mit einer einzelnen Abfrage gehen. Kann mir jemand mit einem passenden Beispiel helfen?
Vielen Dank!

[jobo]

Deine Angaben sind noch etwas mager, also erstmal so bspw.

Code:

select Preis/LaufzeitInMonaten*MonateIn2012 from Vertrag
 where MonateIn2012>0

Hilfreich wäre z.B. die Angabe der Feldtypen (echte Datumstypen?) und des RDBMS. Gerade bei Datumsberechnungen gibt es viele herstellerspezifischen Funktionen.
Vermutlich bezieht sich die Aufgabenstellung auch nicht nur auf das Jahr 2012?

[DeddyH]

MS SQL 2012 ist doch angegeben.

[romber]

Vielen Dank für die schnelle Reaktion!

Zitat von jobo:

Hilfreich wäre z.B. die Angabe der Feldtypen (echte Datumstypen?) und des RDBMS. Gerade bei Datumsberechnungen gibt es viele herstellerspezifischen Funktionen.

Ich benutze SQL Server 2012. Die Datumsfelder sind vom Typ "smalldatetime".

Zitat von jobo:

Vermutlich bezieht sich die Aufgabenstellung auch nicht nur auf das Jahr 2012?

Genau. Das Jahr bzw. mehrere Jahre gibt man dynamisch an. Die Abfrage soll die Summe aus allen Verträgen liefern.

[jobo]

Zitat von DeddyH:

MS SQL 2012 ist doch angegeben.

Könnte schwören, das stand grad nicht da.

[romber]

Für einen SQL-Beispel wäre ich sehr dankbar!

[DeddyH]

Wenn das überhaupt mit einer Abfrage (statt einer Stored Procedure) machbar ist, wird es wohl mächtig kompliziert. Ich habe zumindest keinen zündenden Einfall.

[romber]

Von mir aus kann das auch eine SP sein. Danke!

[jobo]

Interessantes Problem, ich bin in MSSQL nicht so fit und die Datumsarithmetik hat auf Anhieb nicht geklappt (Rangeerror). Muss aber nichts heißen.

Hier eine Oracle Lösung, die Hälfte des Codes kann man weglassen, ist nur Erklärung bzw Daten.
- fn "Round()" ggF durch mssql variante ersetzen
- "from dual" weglassen
- "decode" durch "case when" ersetzen
- Datumsberechnung durch anloge mssql function ersetzen
- fn "to_date()" durch "cast" ersetzen, später durch Datumsparameter
dann müsste es unter mssql laufen

Noch was zum Aufbau:
Ich hab vermieden, das Datum zu zerschnippeln (ala YYYY='2012' usw), ich hoffe so bekommst Du gute Performance trotz großer Datenmengen.
Da [myEnde] bei Dir nicht explizit vorliegt, sollte man es in der inneren Where Bedingung auf einen Vergleich mit [Mystart]<@ParamIntervalStart+Laufzeit umbauen.
Die Logik (where Bedingung und Decode/Case) funktioniert (hoffentlich) nur bei sinnvollen Intervall Werten und.
Sollte unabhängig von Intervallgrößen und Datumsgrenzen funktionieren.

Code:

select z.* ,
       round(z.TageDiff/30,0) as MonateIn,
       (Preis/Laufzeit)*round(z.TageDiff/30,0) as PreisIntervall
  from (
        select y.*, -- DefinedType = Manuell vordefiniert, zur Kontrolle
                    -- DefinedType muss durch CalcType ersetzbar sein mit gleichem Ergebnis
               decode(Definedtype,'I Ende überlappend', (to_date('31.12.2012','DD.MM.YYYY')/*IntervallEnd*/ - y.myStart),
                                  'I Start überlappend', (y.myEnde-to_date('01.01.2012','DD.MM.YYYY')/*IntervallStart*/ ),
                                  'eingeschlossen von I', y.myEnde-y.myStart,
                                  'I umschließend', to_date('31.12.2012','DD.MM.YYYY')/*IntervallEnd*/-  to_date('01.01.2012','DD.MM.YYYY')/*IntervallStart*/,
                                   -1/*error*/) as TageDiff,
               100.0 as Preis,
               round((MyEnde-MyStart)/30,0) as Laufzeit
          from (
                select x.*, 
                       decode(-- MyStart kleiner als Intervallstart?
                              least(MyStart, to_date('01.01.2012','DD.MM.YYYY')/*IntervallStart*/), 
                                    MyStart, /*'ja, kleiner Case'*/ decode(greatest(MyEnde, to_date('31.12.2012','DD.MM.YYYY')/*IntervallStart*/), 
                                                                              MyEnde, 'I umschließend','I Start überlappend'),
                                             /*'ne,  größer Case'*/ decode(greatest(MyEnde, to_date('31.12.2012','DD.MM.YYYY')/*IntervallStart*/), 
                                                                              MyEndE, 'I Ende überlappend','eingeschlossen von I')
                              ) as CalcType
                  from (
                        select 'vor I'                as DefinedType, to_date('01.02.2011','DD.MM.YYYY') as myStart, to_date('30.04.2011','DD.MM.YYYY')   as myEnde from dual union
                        select 'hinter I'             as DefinedType, to_date('01.02.2015','DD.MM.YYYY') as myStart, to_date('01.05.2015','DD.MM.YYYY')   as myEnde from dual union
                        select 'I umschließend'       as DefinedType, to_date('01.02.2011','DD.MM.YYYY') as myStart, to_date('01.05.2013','DD.MM.YYYY')-1 as myEnde from dual union
                        select 'eingeschlossen von I' as DefinedType, to_date('01.02.2012','DD.MM.YYYY') as myStart, to_date('01.05.2012','DD.MM.YYYY')-1 as myEnde from dual union
                        select 'I Start überlappend'  as DefinedType, to_date('01.02.2011','DD.MM.YYYY') as myStart, to_date('01.05.2012','DD.MM.YYYY')-1 as myEnde from dual union
                        select 'I Ende überlappend'   as DefinedType, to_date('01.02.2012','DD.MM.YYYY') as myStart, to_date('01.05.2013','DD.MM.YYYY')-1 as myEnde from dual 
                        )x
                 where not x.myEnde  < to_date('01.01.2012','DD.MM.YYYY') /*IntervallStart*/
                   and not x.myStart > to_date('31.12.2012','DD.MM.YYYY') /*IntervallEnde */
               ) y
       ) z

[tgvoelker]

Code:

SELECT
V.ID
,V.ANFANG
,V.DAUER
,V.BETRAG
,BERTRAG2012=
 CASE WHEN (DATEADD(month,V.DAUER,V.ANFANG)<'01/01/2012')OR(V.ANFANG>'31/12/2012')THEN 0
      WHEN (DATEADD(month,V.DAUER,V.ANFANG)<='31/12/2012')AND(V.ANFANG>='01/01/2012')THEN 1
      WHEN (V.ANFANG<'01/01/2012')AND(DATEADD(month,V.DAUER,V.ANFANG)<='31/12/2012')THEN CAST(DATEDIFF(day,'01/01/2012',DATEADD(month,V.DAUER,V.ANFANG)) AS NUMERIC(18,8))/DATEDIFF(day,V.ANFANG,DATEADD(month,V.DAUER,V.ANFANG))
      WHEN (V.ANFANG>='01/01/2012')AND(DATEADD(month,V.DAUER,V.ANFANG)>'31/12/2012')THEN CAST(DATEDIFF(day,V.ANFANG,'31/12/2012') AS NUMERIC(18,8))/DATEDIFF(day,V.ANFANG,DATEADD(month,V.DAUER,V.ANFANG))
 END * V.BETRAG
FROM
VERTRAEGE AS V