![]() |
AW: SQL: Jahresanteile des Vertags berechnen?
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] |
AW: SQL: Jahresanteile des Vertags berechnen?
:thumb:
Vielen Dank an alle! Ich muss jetzt alles ausprobieren! Danke!!! |
AW: SQL: Jahresanteile des Vertags berechnen?
--- blödsinn ---
|
AW: SQL: Jahresanteile des Vertags berechnen?
Nochmal kommentiert:
Code:
Diese Syntax funktioniert auch bei Verträgen, die mitten im Monat anfangen.
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 Betrag_2012 multipliziert den Betrag mit einem Wert. Dieser Wert ist:
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. |
AW: SQL: Jahresanteile des Vertags berechnen?
Zitat:
|
AW: SQL: Jahresanteile des Vertags berechnen?
Zitat:
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). |
AW: SQL: Jahresanteile des Vertags berechnen?
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:
Wenn's denn funktionieren sollte, so funktioniert es auch noch in 100 Jahren, da die Jahreszahl per Parameter an das SQL übergeben wird.
/* 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 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 |
AW: SQL: Jahresanteile des Vertags berechnen?
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 -------------------------------------------------------------------------------------- |
AW: SQL: Jahresanteile des Vertags berechnen?
Zitat:
Was ist das Ergebnis, wenn der Vertrag am 01.12.2011 anfängt und 3 monate läuft? :> |
AW: SQL: Jahresanteile des Vertags berechnen?
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 |
Alle Zeitangaben in WEZ +1. Es ist jetzt 01:47 Uhr. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
LinkBacks Enabled by vBSEO © 2011, Crawlability, Inc.
Delphi-PRAXiS (c) 2002 - 2023 by Daniel R. Wolf, 2024 by Thomas Breitkreuz