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 2 von 3     12 3      
Benutzerbild von Bummi
Bummi

Registriert seit: 15. Jun 2010
Ort: Augsburg Bayern Süddeutschland
3.470 Beiträge
 
Delphi XE3 Enterprise
 
#11

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 19:32
Wenn Verträge auch innerhalb eines Monats beginnen können musst Du aufbohren / umbauen


Code:
/*  einmalig zu erstellende Function

Create Function EncodeDate( @day int,@month int,@year int) Returns DateTime
begin
 Declare @Result DateTime
 SELECT @Result=CAST(CAST(10000*@year+100*@month+@day AS VARCHAR(8)) AS DATETIME);
 Return @Result
end;

*/


Declare @Months Table(y int,m int)
insert into @Months values (2012,1),(2012,2),(2012,3),(2012,4),(2012,5),(2012,6),(2012,7),(2012,8),(2012,9),(2012,10),(2012,11),(2012,12)



Declare @a Table (ID int,Anfangsdatum datetime,Vertragsdauer int,[Bezahlter Betrag] money)
insert into @a Values(1,'20111101',12,100),(2,'20120101',3,100),(3,'20120601',12,100);



Select ID,COUNT(*) as Monate,[Bezahlter Betrag]/Vertragsdauer * COUNT(*) as Betrag
from
(
Select ID, Anfangsdatum,DATEADD(mm,Vertragsdauer,Anfangsdatum)-1 as EndDatum,[Bezahlter Betrag],Vertragsdauer
from @a
) a
join @Months m on Anfangsdatum<= dbo.EncodeDate(1,m.m,m.y) and Enddatum>= dbo.EncodeDate(1,m.m,m.y)
Group by ID,Vertragsdauer,[Bezahlter Betrag]
Thomas Wassermann H₂♂
Das Problem steckt meistens zwischen den Ohren
DRY DRY KISS
H₂ (wenn bei meinen Snipplets nichts anderes angegeben ist Lizenz: WTFPL)
  Mit Zitat antworten Zitat
romber

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

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 19:42

Vielen Dank an alle! Ich muss jetzt alles ausprobieren!
Danke!!!
  Mit Zitat antworten Zitat
Furtbichler
(Gast)

n/a Beiträge
 
#13

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 20:14
--- blödsinn ---
  Mit Zitat antworten Zitat
tgvoelker

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

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 20:19
Nochmal kommentiert:
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
Diese Syntax funktioniert auch bei Verträgen, die mitten im Monat anfangen.

Betrag_2012 multipliziert den Betrag mit einem Wert. Dieser Wert ist:
  • 0, wenn Anfangsdatum nach dem 31.12.2012 oder Ende vor dem 1.1.2012
  • 1, wenn Anfangsdatum gleich oder nach dem 1.1.2012 und enddatum gleich oder vor dem 31.12.2012
  • Quotient aus den Tagen zwischen 1.1.2012 und Ende und Gesamttagen, wenn Anfang vor dem 1.1.2012 und ende gleich oder vor dem 31.12.2012 (Vertrag außerhalb 2012 wurde schon im ersten Punkt behandelt!))
  • Quotient aus den Tagen zwischen Anfang und 31.12.2012 und Gesamttagen, wenn Anfange nach dem 1.1.2012 und Ende nach dem 31.12.2012

Die Umwandlung des Dividenden in Numeric(18,8) hat den Hintergrund, daß sonst das Ergebnis in INT umgewandelt würde.

Ist auf SQL Server 2005 getestet.

Das Ergebnis ist abweichend von einer Implementierung auf Basis ganzer Monate (wenn Verträge immer am 01. beginnen würden). In diesem Fall wäre anstelle von DATEDIFF(day... DATEDIFF(month... zu verwenden.
Thomas Völker

Geändert von tgvoelker (10. Jun 2013 um 20:28 Uhr)
  Mit Zitat antworten Zitat
tgvoelker

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

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 20:20
--- blödsinn ---
O_o
Thomas Völker
  Mit Zitat antworten Zitat
Benutzerbild von sx2008
sx2008

Registriert seit: 16. Feb 2008
Ort: Baden-Württemberg
2.332 Beiträge
 
Delphi 2007 Professional
 
#16

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 20:34
Mangels tiefgreifender SQL-Kenntnisse liste ich testweise alle Vertäge auf und führe für jeden Vertrag die o.g. Schritte aus.
Du kannst alle Verträge vor dem 1.1.2011 (= 01.01.2012 - maxVertragslaufzeit) per SQL wegfiltern.
Das müsste die Menge der Datensätze beträchtlich reduzieren.

Manche Dinge lassen sich einfach besser mit einer "richtigen" Programmiersprache berechnen und sind in SQL eine Quälerei.
Wenn deine Zigarettenpause auf <= 20 Sekunden schrumpft dann würde ich bei der Clientseitigen Lösung bleiben.

Auf jeden Fall solltest du vermeiden, den Restbetrag mit SQL zu berechnen.
Man kann alles mit SQL machen (Quälerei) oder alles mit Delphi (langsam) oder man filtert nur alle Datensätze, die im Jahr 2012 noch laufen und bewerkstelligt die Berechnung mit einem Calculated Field (schnell & flexibel).
fork me on Github
  Mit Zitat antworten Zitat
nahpets
(Gast)

n/a Beiträge
 
#17

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 10. Jun 2013, 21:22
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
  Mit Zitat antworten Zitat
Furtbichler
(Gast)

n/a Beiträge
 
#18

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 11. Jun 2013, 08:51
Ich glaube, es geht kürzer:
SQL-Code:
---
--- Testumgebung ----------------------------------------------------------
---
USE [Test]
GO

CREATE TABLE [dbo].[Contracts](
   [ctID] [int] IDENTITY(1,1) NOT NULL,
   [ctStart] [smalldatetime] NOT NULL,
   [ctDurationMonths] [int] NOT NULL,
   [ctPrice] [money] NOT NULL,
   [soll] [int] NULL
) ON [PRIMARY]


declare @startPeriod dateTime
declare @EndPeriod DateTime
set @startPeriod='20120101'
set @EndPeriod = '20121231'

--- Abfrage ---------------------------------------------------------------------------
select *, ctPrice*DateDiff(Month, dmin,dmax)/ctDurationMonths as PriceInPeriod
from
(
select *,
   (select max (X) from (values (@StartPeriod), (ctStart)) as T(X)) dMin,
   (select min (X) from (values (@EndPeriod), (DateAdd(Month, ctDurationMonths, ctStart))) as T(X)) dMax
----------*****---- Hier war ein Tippfehler. Danke tgvoelker!   
 from contracts
 ) x
 where DateDiff(Month, dmin,dmax)>0
--------------------------------------------------------------------------------------

Geändert von Furtbichler (12. Jun 2013 um 08:20 Uhr) Grund: Berichtigung eines Tippfehlers
  Mit Zitat antworten Zitat
tgvoelker

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

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 12. Jun 2013, 07:02
Ich glaube, es geht kürzer:
SQL-Code:
---
--- Testumgebung ----------------------------------------------------------
---
USE [Test]
GO

CREATE TABLE [dbo].[Contracts](
   [ctID] [int] IDENTITY(1,1) NOT NULL,
   [ctStart] [smalldatetime] NOT NULL,
   [ctDurationMonths] [int] NOT NULL,
   [ctPrice] [money] NOT NULL,
   [soll] [int] NULL
) ON [PRIMARY]


declare @startPeriod dateTime
declare @EndPeriod DateTime
set @startPeriod='20120101'
set @EndPeriod = '20121231'

--- Abfrage ---------------------------------------------------------------------------
select *, ctPrice*DateDiff(Month, dmin,dmax)/ctDurationMonths as PriceInPeriod
from
(
select *,
   (select max (X) from (values (@StartPeriod), (ctStart)) as T(X)) dMin,
   (select max (X) from (values (@EndPeriod), (DateAdd(Month, ctDurationMonths, ctStart))) as T(X)) dMax
   
 from contracts
 ) x
 where DateDiff(Month, dmin,dmax)>0
--------------------------------------------------------------------------------------
Was ist das Ergebnis, wenn der Vertrag am 01.06.2013 anfängt und 1 Monat läuft?
Was ist das Ergebnis, wenn der Vertrag am 01.12.2011 anfängt und 3 monate läuft?

:>
Thomas Völker
  Mit Zitat antworten Zitat
Furtbichler
(Gast)

n/a Beiträge
 
#20

AW: SQL: Jahresanteile des Vertags berechnen?

  Alt 12. Jun 2013, 08:19
Moin,
Danke fürs Testen.
Da war noch ein Tippfehler beim Refaktorisieren.

Hier die korrekte Version (war ja zu deutlich):

SQL-Code:
select *, ctPrice*DateDiff(Month, dmin,dmax)/ctDurationMonths as PriceInPeriod
from
 (
select *,
    (select max (X) from (values (@StartPeriod), (ctStart)) as T(X)) dMin,
    (select min (X) from (values (@EndPeriod), (DateAdd(Month, ctDurationMonths, ctStart))) as T(X)) dMax
-----------****----- hier stand 'max'
  from contracts
  ) x
  where DateDiff(Month, dmin,dmax)>0
  Mit Zitat antworten Zitat
Antwort Antwort
Seite 2 von 3     12 3      


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:40 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