AGB  ·  Datenschutz  ·  Impressum  







Anmelden
Nützliche Links
Registrieren
Zurück Delphi-PRAXiS Programmierung allgemein Datenbanken Delphi SQL: Jahresanteile des Vertags berechnen?
Thema durchsuchen
Ansicht
Themen-Optionen

SQL: Jahresanteile des Vertags berechnen?

Ein Thema von romber · begonnen am 10. Jun 2013 · letzter Beitrag vom 13. Jun 2013
Antwort Antwort
Seite 1 von 3  1 23      
romber

Registriert seit: 15. Apr 2004
Ort: Köln
1.166 Beiträge
 
Delphi 10 Seattle Professional
 
#1

SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 12:45
Datenbank: SQL Server • Version: 2012 • Zugriff über: ADO
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!
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#2

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 13:30
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?
Gruß, Jo
  Mit Zitat antworten Zitat
Benutzerbild von DeddyH
DeddyH

Registriert seit: 17. Sep 2006
Ort: Barchfeld
27.618 Beiträge
 
Delphi 12 Athens
 
#3

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 13:31
MS SQL 2012 ist doch angegeben.
Detlef
"Ich habe Angst vor dem Tag, an dem die Technologie unsere menschlichen Interaktionen übertrumpft. Die Welt wird eine Generation von Idioten bekommen." (Albert Einstein)
Dieser Tag ist längst gekommen
  Mit Zitat antworten Zitat
romber

Registriert seit: 15. Apr 2004
Ort: Köln
1.166 Beiträge
 
Delphi 10 Seattle Professional
 
#4

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 13:34
Vielen Dank für die schnelle Reaktion!

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".

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.

Geändert von romber (10. Jun 2013 um 13:37 Uhr)
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#5

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 13:42
MS SQL 2012 ist doch angegeben.
Könnte schwören, das stand grad nicht da.
Gruß, Jo
  Mit Zitat antworten Zitat
romber

Registriert seit: 15. Apr 2004
Ort: Köln
1.166 Beiträge
 
Delphi 10 Seattle Professional
 
#6

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 18:27
Für einen SQL-Beispel wäre ich sehr dankbar!
  Mit Zitat antworten Zitat
Benutzerbild von DeddyH
DeddyH

Registriert seit: 17. Sep 2006
Ort: Barchfeld
27.618 Beiträge
 
Delphi 12 Athens
 
#7

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 18:34
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.
Detlef
"Ich habe Angst vor dem Tag, an dem die Technologie unsere menschlichen Interaktionen übertrumpft. Die Welt wird eine Generation von Idioten bekommen." (Albert Einstein)
Dieser Tag ist längst gekommen
  Mit Zitat antworten Zitat
romber

Registriert seit: 15. Apr 2004
Ort: Köln
1.166 Beiträge
 
Delphi 10 Seattle Professional
 
#8

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 18:48
Von mir aus kann das auch eine SP sein. Danke!
  Mit Zitat antworten Zitat
jobo

Registriert seit: 29. Nov 2010
3.072 Beiträge
 
Delphi 2010 Enterprise
 
#9

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 19:07
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
Gruß, Jo
  Mit Zitat antworten Zitat
tgvoelker

Registriert seit: 9. Sep 2002
Ort: Oelsnitz, Vogtland
40 Beiträge
 
Delphi 12 Athens
 
#10

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 19:31
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
Thomas Völker
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 1 von 3  1 23      


Forumregeln

Es ist dir nicht erlaubt, neue Themen zu verfassen.
Es ist dir nicht erlaubt, auf Beiträge zu antworten.
Es ist dir nicht erlaubt, Anhänge hochzuladen.
Es ist dir nicht erlaubt, deine Beiträge zu bearbeiten.

BB-Code ist an.
Smileys sind an.
[IMG] Code ist an.
HTML-Code ist aus.
Trackbacks are an
Pingbacks are an
Refbacks are aus

Gehe zu:

Impressum · AGB · Datenschutz · Nach oben
Alle Zeitangaben in WEZ +1. Es ist jetzt 07:26 Uhr.
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz