TSQL - Tabelle mit Kosten pro Monat und Jahr in View über mehrere Jahre übernehmen.

Hallo allerseits,

derzeit habe ich ein Problemchen mit der Realisierung folgender Anforderung:

Für ein Projektmanagement-Tool sollen Kosten pro Projekt, Task,Fiskaljahr und Monat erfasst werden. Die Tabelle sieht so aus:

[ID]
,[Month]
,[FiscalYear]
,[Costs]
,[ProjectID]
,[PrjTaskID]

Beispieldaten:

Die ID's sind alle uniqueidentifier.

Month | FiscalYear | Costs

1 | 14 | 25
2 | 14 | 25
3 | 14 | 25
5 | 14 | 25
5 | 15 | 25

Nun geht es darum, diese Kosten auf einen Zeitstrahl zu visualisieren.Was ich final eigentlich möchte, ist eine View,die pro Projekttask über die komplette Laufzeit des Projekts, also quasi vom geringsten Fiskaljahr ausgehend, bis zu sieben Jahre in die Zukunft darstellt:

ProjectID, PrjTaskID, Min(FiscalYear) as FY_Begin, [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12], .....[84]

Dafür habe ich bereits eine View gemacht, die das per Fiskaljahr, Projekt und Task zusammenfasst:

SELECT [FiscalYear]
,[ProjectID]
,[PrjTaskID]
,cast([1] as numeric(12,3)) as [1]
,cast([2] as numeric(12,3)) as [2]
,cast([3] as numeric(12,3)) as [3]
,cast([4] as numeric(12,3)) as [4]
,cast([5] as numeric(12,3)) as [5]
,cast([6] as numeric(12,3)) as [6]
,cast([7] as numeric(12,3)) as [7]
,cast([8] as numeric(12,3)) as [8]
,cast([9] as numeric(12,3)) as [9]
,cast([10] as numeric(12,3)) as [10]
,cast([11] as numeric(12,3)) as [11]
,cast([12] as numeric(12,3)) as [12]

FROM
(SELECT
[Month]
,Costs
,FiscalYear
,[ProjectID]
,[PrjTaskID]
FROM
Actual_Costs

)A
PIVOT(SUM(Costs) FOR [Month] in
(
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
)
) PVT

Diese gibt mir im Beispiel zwei Zeilen mit Fiskaljahr,ProjectID, PrjTaskID und 12 Monaten. nur wie komme ich nun auf die 84 Monate und vor allem wie bekomme ich die Reihenfolge über die Fiskaljahre aufsteigend. gruppiert über ProjektID und PrjTaskID hin?

Bin für jeden Ansatz dankbar, wie ich daraus eine View machen kann. Falls es keine View werden kann, würde ich einen Trigger auf die Actual_Costs erstellen, der mir die Daten jeweils horizontal in eine Tabelle schreibt - das wäre aus meiner Sicht allerdings suboptimal (redundant, fehleranfällig, komplex zu debuggen und verwalten).

Viele Grüße
Grinskeks

Bitte markiere auch die Kommentare, die zur Lösung des Beitrags beigetragen haben

Content-ID: 259161

Url: https://administrator.de/contentid/259161

Ausgedruckt am: 24.11.2024 um 23:11 Uhr

2 Kommentare

Neuester Kommentar

Hallo Grinskeks,

wenn ich dich richtig verstanden habe, dann meinst Du sowas in der Art:

with	Zahlen as (select 1 as i union all select 2 union all select 3 union all select 4 union all select 5),
	MehrZahlen as (select t1.i from Zahlen t1 cross join Zahlen t2 cross join Zahlen t3),
	Folge as (select top (84) row_number () over (order by i) as i from MehrZahlen),
	MinJahr as (select ProjectID, min (FiscalYear) as FY_Begin from Actual_Costs group by ProjectID),
	PrjTask as (select distinct ProjectID, PrjTaskID from Actual_Costs)
SELECT FY_Begin
,[ProjectID]
,[PrjTaskID]
,cast([1] as numeric(12,3)) as [1]
,cast([2] as numeric(12,3)) as [2]
,cast([3] as numeric(12,3)) as [3]
,cast([4] as numeric(12,3)) as [4]
,cast([5] as numeric(12,3)) as [5]
,cast([6] as numeric(12,3)) as [6]
,cast([7] as numeric(12,3)) as [7]
,cast([8] as numeric(12,3)) as [8]
,cast([9] as numeric(12,3)) as [9]
,cast([10] as numeric(12,3)) as [10]
,cast([11] as numeric(12,3)) as [11]
,cast([12] as numeric(12,3)) as [12]
,cast([13] as numeric(12,3)) as [13]
,cast([14] as numeric(12,3)) as [14]
,cast([15] as numeric(12,3)) as [15]
,cast([16] as numeric(12,3)) as [16]
,cast([17] as numeric(12,3)) as [17]
,cast([18] as numeric(12,3)) as [18]
,cast([19] as numeric(12,3)) as [19]
,cast([20] as numeric(12,3)) as [20]
,cast([21] as numeric(12,3)) as [21]
,cast([22] as numeric(12,3)) as [22]
,cast([23] as numeric(12,3)) as [23]
,cast([24] as numeric(12,3)) as [24]
,cast([25] as numeric(12,3)) as [25]
,cast([26] as numeric(12,3)) as [26]
,cast([27] as numeric(12,3)) as [27]
,cast([28] as numeric(12,3)) as [28]
,cast([29] as numeric(12,3)) as [29]
,cast([30] as numeric(12,3)) as [30]
,cast([31] as numeric(12,3)) as [31]
,cast([32] as numeric(12,3)) as [32]
,cast([33] as numeric(12,3)) as [33]
,cast([34] as numeric(12,3)) as [34]
,cast([35] as numeric(12,3)) as [35]
,cast([36] as numeric(12,3)) as [36]
,cast([37] as numeric(12,3)) as [37]
,cast([38] as numeric(12,3)) as [38]
,cast([39] as numeric(12,3)) as [39]
,cast([40] as numeric(12,3)) as [40]
,cast([41] as numeric(12,3)) as [41]
,cast([42] as numeric(12,3)) as [42]
,cast([43] as numeric(12,3)) as [43]
,cast([44] as numeric(12,3)) as [44]
,cast([45] as numeric(12,3)) as [45]
,cast([46] as numeric(12,3)) as [46]
,cast([47] as numeric(12,3)) as [47]
,cast([48] as numeric(12,3)) as [48]
,cast([49] as numeric(12,3)) as [49]
,cast([50] as numeric(12,3)) as [50]
,cast([51] as numeric(12,3)) as [51]
,cast([52] as numeric(12,3)) as [52]
,cast([53] as numeric(12,3)) as [53]
,cast([54] as numeric(12,3)) as [54]
,cast([55] as numeric(12,3)) as [55]
,cast([56] as numeric(12,3)) as [56]
,cast([57] as numeric(12,3)) as [57]
,cast([58] as numeric(12,3)) as [58]
,cast([59] as numeric(12,3)) as [59]
,cast([60] as numeric(12,3)) as [60]
,cast([61] as numeric(12,3)) as [61]
,cast([62] as numeric(12,3)) as [62]
,cast([63] as numeric(12,3)) as [63]
,cast([64] as numeric(12,3)) as [64]
,cast([65] as numeric(12,3)) as [65]
,cast([66] as numeric(12,3)) as [66]
,cast([67] as numeric(12,3)) as [67]
,cast([68] as numeric(12,3)) as [68]
,cast([69] as numeric(12,3)) as [69]
,cast([70] as numeric(12,3)) as [70]
,cast([71] as numeric(12,3)) as [71]
,cast([72] as numeric(12,3)) as [72]
,cast([73] as numeric(12,3)) as [73]
,cast([74] as numeric(12,3)) as [74]
,cast([75] as numeric(12,3)) as [75]
,cast([76] as numeric(12,3)) as [76]
,cast([77] as numeric(12,3)) as [77]
,cast([78] as numeric(12,3)) as [78]
,cast([79] as numeric(12,3)) as [79]
,cast([80] as numeric(12,3)) as [80]
,cast([81] as numeric(12,3)) as [81]
,cast([82] as numeric(12,3)) as [82]
,cast([83] as numeric(12,3)) as [83]
,cast([84] as numeric(12,3)) as [84]

FROM
(select	t.ProjectID, t.PrjTaskID, f.i, j.FY_Begin, c.Costs
from	PrjTask t
	join MinJahr j on j.ProjectID = t.ProjectID
	cross join Folge f
	left join Actual_Costs c on c.ProjectID = t.ProjectID and c.PrjTaskID = t.PrjTaskID and c.FiscalYear = j.FY_Begin + ((f.i - 1) / 12) and c.[Month] = ((f.i - 1) % 12) + 1
)A
PIVOT(SUM(Costs) FOR [i] in
(
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12],[13],[14],[15],[16],[17],[18],[19],[20],[21],[22],[23],[24],[25],[26],[27],[28],[29],[30],[31],[32],[33],[34],[35],[36],[37],[38],[39],[40],[41],[42],[43],[44],[45],[46],[47],[48],[49],[50],[51],[52],[53],[54],[55],[56],[57],[58],[59],[60],[61],[62],[63],[64],[65],[66],[67],[68],[69],[70],[71],[72],[73],[74],[75],[76],[77],[78],[79],[80],[81],[82],[83],[84]
)
) PVT

Ob das jetzt natürlich die einfache Lösung ist, die Du suchst, weiß ich nicht. Alternative wäre aber auch noch, das in einer Prozedur aufzubereiten und ausgeben zu lassen.

Gruß, Mad Max

Hallo Mad Max,

das ist im Prinzip genau, was ich brauche - vielen Dank!

Jetzt sieht es tatsächlich einfach aus - bisher habe ich CTE nur rudimentär und auch nur für eine Query genutzt, aber damit geht ja schon so einiges