Registriert seit: 29. Nov 2010
3.072 Beiträge
Delphi 2010 Enterprise
|
AW: SQL: Jahresanteile des Vertags berechnen?
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
|