Hallo,
ich habe eine Rekursive Struktur mit Nested-Sets.
Es gibt verschiedene Mitarbeiter mit unterschiedlichen Stundsätzen
Mitarbeiter a) 10 EUR
Mitarbeiter b) 15 EUR
Mitarbeiter c) 20 EUR
Nun soll der Durchschnittstundensatz aller Mitarbeiter berechnet werden, wobei diese Mitarbeiter in einer Rekursiven Struktur mit Nested-Sets abgebildet werden.
Ebenfalls sollen Zwischenergebnisse je Struktur ermittelt werden können.
Mitarbeiter a) 10 Stunden mit je 10 EUR = 100 EUR
Mitarbeiter b) 5 Stunden mit je 15 EUR =75 EUR
Mitarbeiter c) 2 Stunden mit je 20 EUR =40 EUR
Rechenweise: Summe von (Stundensatz * Anzahl Stunden) geteilt durch Summe der Anzahl Stunden
Mitarbeiter a und Mitarbeiter gehören zu dem Vorgesetztem „Meier“
Vorgesetzter Meier=( (10 Std *10 EUR) + (5 Std * 15 EUR))/15 Std =11,6 EUR
Mitarbeiter c gehört zu Vorgesetztem „Müller“
Vorgesetzter Müller =( (2 Std *20 EUR) )/2 Std =20 EUR
Gesamt: 215 EUR geteilt durch 17 Stunden=12,64 EUR
Struktur:
Tabelle „Struktur“
ID Bezeichnung IDRekursiv lft rht
1–Gesamt-------0---------1—6
2–Meier--------1---------2—3
3–Müller-------2---------4—5
Tabelle „Mitarbeiter“
ID Bezeichnung Stundensatz AnzahlStunden
1–a-----------10----------10
2–b-----------15-----------5
3–c-----------20-----------2
Tabelle „Verknüpfung“
ID IDStruktur IDMitarbeiter
1–2----------1
2–2----------2
3–3----------3
Meine Frage ist nun, ob ich einen SQL Befehl bauen kann, der mir für jeden Mitarbeiter anzeigt, sowie für jede Ebene der Struktur einen Durchschnittlichen Stundensatz ausgibt.
Folgende Tabelle soll meine Ausgabe sein.
chef-----Mitarbeiter Lohn Stundensatz Durchsch.
Gesamt---------------215–17-------------12,64
Meier----------------175–15-------------11,6
Meier----a-----------100–10-------------10
Meier----b-----------75—5--------------15
Müller---------------40—2--------------20
Müller—c-----------40—2--------------20
Mit dem folgenden Befehl erhalte ich „fast“ das gewünschte Ergebnis.
Nur die Zeile mit dem „Gesamt“ erhalte ich nicht.
Das Ergebnis von der Gruppe „Gesamt“ soll von allen untergeordneten Gruppen und davon aller zugeordneten Mitarbeiten das Ergebnis ermitteln.
Wichtig: Die Struktur ist beliebig tief erweiterbar und es ist möglich, dass es viele „Zwischengruppen“ ohne direkt zugeordnete Mitarbeitern gibt. Für jede Zwischengruppe benötige ich jedoch ein Ergebnis.
SELECT chef, ’ ’ as Mitarbeiter, sum(lohn) as Lohn, sum(AnzahlStunden) as StdSatz, sum(lohn)/sum(AnzahlStunden) as Durchschnitt
FROM (SELECT mitarbeiter.Bezeichnung , mitarbeiter.AnzahlStunden , mitarbeiter.Stundensatz, mitarbeiter.Stundensatz*mitarbeiter.AnzahlStunden AS lohn,
Struktur.Bezeichnung AS chef
FROM Struktur,Mitarbeiter,Verknuepfung
Where Mitarbeiter.ID = Verknuepfung.IDMitarbeiter and Struktur.ID = Verknuepfung.IDStruktur
) AS help
WHERE NOT chef IS NULL
GROUP BY chef
HAVING NOT sum(AnzahlStunden) = 0
UNION
SELECT chef, Mitarb as Mitarbeiter, sum(lohn) as Lohn, sum(AnzahlStunden) as StdSatz, sum(lohn)/sum(AnzahlStunden) as Durchschnitt
FROM (SELECT mitarbeiter.Bezeichnung as Mitarb, mitarbeiter.AnzahlStunden , mitarbeiter.Stundensatz, mitarbeiter.Stundensatz*mitarbeiter.AnzahlStunden AS lohn,
Struktur.Bezeichnung AS chef
FROM Struktur,Mitarbeiter,Verknuepfung
Where Mitarbeiter.ID = Verknuepfung.IDMitarbeiter and Struktur.ID = Verknuepfung.IDStruktur
) AS help
WHERE NOT AnzahlStunden = 0
GROUP BY chef, Mitarb
Order by chef
Anbei das Script für die Datenstruktur auf einem SQL Server
USE [TEST_TS]
GO
/****** Object: Table [dbo].[Struktur] Script Date: 01/23/2013 11:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Struktur](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Bezeichnung] nvarchar NULL,
[IDRekursiv] [int] NULL,
[lft] [int] NULL,
[rgt] [int] NULL,
CONSTRAINT [aaaaaStruktur_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Struktur] ON
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv], [lft],[rgt]) VALUES (1, N’Gesamt’, 0,1,6)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv], [lft],[rgt]) VALUES (2, N’Meier’, 1,2,3)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv], [lft],[rgt]) VALUES (3, N’Müller’, 2,4,5)
SET IDENTITY_INSERT [dbo].[Struktur] OFF
/****** Object: Table [dbo].[Verknuepfung] Script Date: 01/23/2013 11:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Verknuepfung](
[ID] [int] IDENTITY(1,1) NOT NULL,
[IDStruktur] [int] NULL,
[IDMitarbeiter] [int] NULL,
CONSTRAINT [aaaaaVerknuepfung_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Verknuepfung] ON
INSERT [dbo].[Verknuepfung] ([ID], [IDStruktur], [IDMitarbeiter]) VALUES (2, 2, 1)
INSERT [dbo].[Verknuepfung] ([ID], [IDStruktur], [IDMitarbeiter]) VALUES (3, 2, 2)
INSERT [dbo].[Verknuepfung] ([ID], [IDStruktur], [IDMitarbeiter]) VALUES (4, 3, 3)
SET IDENTITY_INSERT [dbo].[Verknuepfung] OFF
/****** Object: Table [dbo].[Mitarbeiter] Script Date: 01/23/2013 11:54:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Mitarbeiter](
[ID] [int] IDENTITY(1,1) NOT NULL,
[Bezeichnung] nvarchar NULL,
[Stundensatz] [int] NULL,
[AnzahlStunden] [int] NULL,
CONSTRAINT [aaaaaMitarbeiter_PK] PRIMARY KEY NONCLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[Mitarbeiter] ON
INSERT [dbo].[Mitarbeiter] ([ID], [Bezeichnung], [Stundensatz], [AnzahlStunden]) VALUES (1, N’a’, 10, 10)
INSERT [dbo].[Mitarbeiter] ([ID], [Bezeichnung], [Stundensatz], [AnzahlStunden]) VALUES (2, N’b’, 15, 5)
INSERT [dbo].[Mitarbeiter] ([ID], [Bezeichnung], [Stundensatz], [AnzahlStunden]) VALUES (3, N’c’, 20, 2)
SET IDENTITY_INSERT [dbo].[Mitarbeiter] OFF
Bin für jede Info dankbar.
Gruß
Thomas