Hallo Thorsten,
hier ein Beispiel für eine tageweise Umrechnung der Ausgaben auf Monate.
Schönen Abend.
IF OBJECT_ID(N'tempdb..#Calendar') IS NOT NULL
DROP TABLE #Calendar;
DECLARE @DateMax DATETIME, @DateMin DATETIME;
DECLARE @Periode VARCHAR(MAX);
DECLARE @Zeitachse CHAR(1);
SET @DateMin = '01.01.2019';
SET @DateMax = '31.12.2020';
SET @Zeitachse = 'm'; /* Schalter: Y = Jahr; Q = Quartal; M = Monat */
DECLARE @Projekte TABLE ( BeginnProjekt DATETIME, EndeProjekt DATETIME, Ausgaben MONEY );
INSERT INTO @Projekte VALUES
( '16.04.2019', '31.08.2019', 1000 );
CREATE TABLE #Calendar(
Jahr int NULL,
Quartal int NULL,
Monat CHAR (2) NULL,
Beginn_Periode DATETIME,
Ende_Periode DATETIME,
Periode varchar(10) NULL,
Periodentage INT);
WITH Calendar_func AS
(
SELECT @DateMin AS DateDay
UNION ALL
SELECT DATEADD(DAY, 1, DateDay)
FROM Calendar_func
WHERE DateDay < @DateMax
)
INSERT INTO #Calendar (Jahr, Quartal, Monat, Beginn_Periode, Ende_Periode, Periode, Periodentage)
SELECT DISTINCT
DATEPART(yy, DateDay) AS [Jahr],
CASE WHEN @Zeitachse = 'Y'
THEN NULL
ELSE CASE WHEN @Zeitachse = 'Q'
THEN DATEPART(qq, DateDay)
ELSE CASE WHEN @Zeitachse = 'M'
THEN DATEPART(qq, DateDay)
ELSE NULL
END
END
END AS [Quartal],
CASE WHEN @Zeitachse = 'Y'
THEN NULL
ELSE CASE WHEN @Zeitachse = 'Q'
THEN NULL
ELSE DATEPART(mm, DateDay)
END
END AS [Monat],
CASE WHEN @Zeitachse = 'Y'
THEN DATEADD(yy,DATEDIFF(yy,0,DateDay),0)
ELSE CASE WHEN @Zeitachse = 'Q'
THEN DATEADD(qq,DATEDIFF(qq,0,DateDay),0)
ELSE CASE WHEN @Zeitachse = 'M'
THEN DATEADD(mm,DATEDIFF(mm,0,DateDay),0)
ELSE NULL
END
END
END AS [Beginn_Periode],
CASE WHEN @Zeitachse = 'Y'
THEN DATEADD(yy,DATEDIFF(yy,0,DateDay)+1,-1)
ELSE CASE WHEN @Zeitachse = 'Q'
THEN DATEADD(qq,DATEDIFF(qq,0,DateDay)+1,-1)
ELSE CASE WHEN @Zeitachse = 'M'
THEN DATEADD(mm,DATEDIFF(mm,0,DateDay)+1,-1)
ELSE NULL
END
END
END AS [Ende_Periode],
CASE WHEN @Zeitachse = 'Y'
THEN CAST (DATEPART(yy, DateDay) AS CHAR (4))
ELSE CASE WHEN @Zeitachse = 'Q'
THEN CAST (DATEPART(yy, DateDay) AS CHAR (4))
+ ' - Q'
+ CAST (DATEPART(qq, DateDay) AS CHAR (1))
ELSE CASE WHEN @Zeitachse = 'M'
THEN CAST (DATEPART(yy, DateDay) AS CHAR (4))
+ ' - '
+ CASE
WHEN DATEPART(mm, DateDay) < 10
THEN RIGHT('0' + CONVERT( varchar, DATEPART(mm, DateDay) ), 2)
ELSE CONVERT(VARCHAR, DATEPART(mm, DateDay))
END
ELSE NULL
END
END
END AS [Periode],
CASE WHEN @Zeitachse = 'Y'
THEN DATEDIFF (d, DATEADD(yy,DATEDIFF(yy,0,DateDay),0), DATEADD(yy,DATEDIFF(yy,0,DateDay)+1,-0))
ELSE CASE WHEN @Zeitachse = 'Q'
THEN DATEDIFF (d, DATEADD(qq,DATEDIFF(qq,0,DateDay),0), DATEADD(qq,DATEDIFF(qq,0,DateDay)+1,-0))
ELSE CASE WHEN @Zeitachse = 'M'
THEN DATEDIFF (d, DATEADD(mm,DATEDIFF(mm,0,DateDay),0), DATEADD(mm,DATEDIFF(mm,0,DateDay)+1,-0))
END
END
END AS [Periodentage]
FROM Calendar_func
OPTION ( MAXRECURSION 0 )
SELECT * FROM @Projekte
SELECT Periode,
CASE WHEN BeginnProjekt > c.Beginn_Periode THEN BeginnProjekt ELSE c.Beginn_Periode END as von,
CASE WHEN EndeProjekt < c.Ende_Periode THEN EndeProjekt ELSE c.Ende_Periode END AS bis,
DATEDIFF (dd, CASE WHEN BeginnProjekt > c.Beginn_Periode THEN BeginnProjekt ELSE c.Beginn_Periode END, CASE WHEN EndeProjekt < c.Ende_Periode THEN EndeProjekt ELSE c.Ende_Periode END) + 1 AS Tage,
Ausgaben * (DATEDIFF (dd, CASE WHEN BeginnProjekt > c.Beginn_Periode THEN BeginnProjekt ELSE c.Beginn_Periode END, CASE WHEN EndeProjekt < c.Ende_Periode THEN EndeProjekt ELSE c.Ende_Periode END) + 1) / (DATEDIFF (dd, BeginnProjekt, EndeProjekt) + 1) AS Ausgaben_Periode,
DATEDIFF (dd, BeginnProjekt, EndeProjekt) + 1 AS Projektdauer,
Ausgaben
FROM #Calendar c
RIGHT OUTER JOIN @Projekte p ON (c.Ende_Periode >= p.BeginnProjekt AND c.Beginn_Periode <=p.EndeProjekt)
