spezielle SQL Abfrage mit Zwischenergebnissen

Question

Hallo,

ich habe eine rekursive Datenstruktur und muss dafür eine spezielle Abfrage bauen.

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 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
1  Gesamt        0
2  Meier          1
3  Müller         2

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.

Struktur Mitarbeiter Stundensatz Anzahl_Stunden Durchsch. 
Ges.                       215              17                  12,64
Meier                      175             15                  11,6
Meier    a                 10              10                  10
Meier    b                 15              5                   15
Müller                     20               2                   20
Müller   c                 20               2                   20

So in der Art:
SELECT Struktur.Bezeichnung, Mitarbeiter.Bezeichnung, Mitarbeiter.Stundensatz, Mitarbeiter.AnzahlStunden, 'Durchschnitt'
FROM Struktur,Mitarbeiter,Verknuepfung 
Where Mitarbeiter.ID = Verknuepfung.IDMitarbeiter and Struktur.ID = Verknuepfung.IDStruktur;
UNION
SELECT Struktur.Bezeichnung, '', 'SummeStundensatz', 'SummeStunden', 'Durchschnitt'
FROM Struktur

Das ganze habe ich auch in einer kleinen Access Datenbank abgebildet. Gerne kann ich diese zur Verfügung stellen.

Bin für jede Info dankbar.

Gruß

Thomas

Answer 1

Klarer Fall von Nested Sets anstatt einer Adjazenzliste.

Answer 2

Hallo Thomas,
hinterlege bei deinen Mitarbeitern oder bei der Verknüpfung einen String. und frage diesen Mit like ab.

Etwa so, der Einfachheit halber bei Mitarbeiter, soll für Dich aber in Verknüpfung genauso gehen.
Tabelle Mitarbeiter
ID Bezeichnung StundenSatz Ebene
1   a                 11                011
2  b                  12                021

Wenn Du nun die Summen der Mitarbeiter von Vorgesetzter 01 haben möchtest ist ein like '01%' angesagt.
Das kann man beliebig erweitern. Es soll schnell und von den SQL's nicht zu kompliziert sein.

Grüße Alexander

Answer 3

Hallo Alex,

Die Idee kann man auch verfolgen. Dann nennen wir das Kind aber beim richtigen Namen: Das ist der Pfad (in der Hierarchie). Für ein konkretes Beispiel bräuchten wir aber die Tabellenstrukturen und ein paar Beispieldaten vom OP.

Das Stichwort hierzu ist: Rekursive CTE. In diesem Beispiel die Spalte [FullQualifiedName].

Answer 4

Hallo Stefan,
Mir war nicht klar das es hierfür bereits ein Pattern gibt. Vielen Dank für die Info.

Grüße Alexander

Answer 5

Hallo,

vielen Dank für die Tips.

Beide Ansätze sind sehr interessant:-)

Wobei ich jedoch immer noch mein Problem habe.

Kann ich mir einen Select bauen, in dem ich die Einzelergebnisse (Berechnungen der einzelnen Mitglieder) sowohl auch Gesamtergebnisse (Berechnungen über alle Mitglieder in der richtigen Sturkturebene) ermitteln lassen kann?

Thomas

Answer 6

Ja. Aber ohne konkrete Skripts (Tabellen und Daten von dir) wirds schwierig..

Answer 7

Hallo,

in meinem ersten Post zu diesem Thema sind all meine Tabellen (inklusive Daten) vorhanden.

Hat man irgendwie eine Möglichkeit, ein Access DB hier hochzuladen? Darin habe ich alles vorbereitet!

Thomas

Answer 8

Hallo Thomas,

in Ermangelung geeigneter Scripts von Deiner Seite zur Definition Deiner Relationen kannst Du das folgende Beispiel zum Testen verwenden.

USE tempdb
GO

IF OBJECT_ID('dbo.EmployeeActivity', 'U') IS NOT NULL
	DROP TABLE dbo.EmployeeActivity
	GO

IF OBJECT_ID('dbo.Employees', 'U') IS NOT NULL
	DROP TABLE dbo.Employees
	GO

-- Erstellen der Relation
CREATE TABLE dbo.Employees
(
	EmployeeId		int				NOT NULL	IDENTITY (1, 1),
	EmployeeName	varchar(200)	NOT NULL,
	HourRate		smallmoney		NOT NULL	DEFAULT (0),
	ManagerId		int				NULL,

	CONSTRAINT pk_Employees PRIMARY KEY CLUSTERED (EmployeeId)
);

CREATE INDEX ix_Employee_ManagerId ON dbo.Employees (ManagerId) INCLUDE (EmployeeName, HourRate)

CREATE TABLE dbo.EmployeeActivity
(
	EntryId	int	NOT NULL	IDENTITY (1, 1),
	EmployeeId	int			NOT NULL,
	StartDate	date		NOT NULL,
	StartTime	datetime	NOT NULL,
	EndTime		datetime	NOT NULL,

	CONSTRAINT pk_EmployeeActivity_EntryId PRIMARY KEY CLUSTERED (EntryId),
	CONSTRAINT fk_EmployeeActivity_EmployeeId FOREIGN KEY (EmployeeId)
	REFERENCES dbo.Employees (EmployeeId)
	ON DELETE CASCADE
);

CREATE UNIQUE INDEX ux_EmployeeActivity_EmployeeId_StartTime ON dbo.EmployeeActivity
(
	EmployeeId,
	StartDate,
	StartTime
);

-- Erstellen einer View für die bessere Konsolidierungsaufgabe
IF OBJECT_ID('dbo.view_EmployeeStructure', 'V') IS NOT NULL
	DROP VIEW dbo.view_EmployeeStructure
	GO

CREATE VIEW dbo.view_EmployeeStructure
AS
	WITH CTE (EmployeeId, EmployeeName, HourRate, ManagerLevel)
	AS
	(
		SELECT	EmployeeId, EmployeeName, HourRate, 1 AS ManagerLevel
		FROM	dbo.Employees
		WHERE	ManagerId IS NULL

		UNION ALL

		SELECT	e.EmployeeId, e.EmployeeName, e.HourRate, c.ManagerLevel + 1
		FROM	cte c INNER JOIN dbo.Employees e
				ON	(c.EmployeeId = e.ManagerId)
	)
	SELECT * FROM CTE
GO

-- Eintragen von Mitarbeitern
INSERT INTO dbo.Employees (EmployeeName, HourRate, ManagerId)
VALUES
('Uwe Ricken', 100, NULL);

INSERT INTO dbo.Employees (EmployeeName, HourRate, ManagerId)
VALUES
('Beate Ricken', 80, 1);

INSERT INTO dbo.Employees (EmployeeName, HourRate, ManagerId)
VALUES
('Katharina Ricken', 80, 1);

INSERT INTO dbo.Employees(EmployeeName, HourRate, ManagerId)
VALUES
('Alicia Ricken', 80, 1);

INSERT INTO dbo.Employees(EmployeeName, HourRate, ManagerId)
VALUES
('Maria Meier', 50, 2);

INSERT INTO dbo.Employees(EmployeeName, HourRate, ManagerId)
VALUES
('Georg Meier', 50, 2);


SELECT * FROM dbo.Employees

Das Script baut zunächst in der Tempdb zwei Relationen auf, die für die weitere Demonstration benötigt werden. Hierbei handelt es sich zum einen um die Mitarbeitertabelle, in der die Stundensätze gespeichert sind. Desweiteren wird eine Relation mit den Arbeitszeiten erstellt.

Da Du ja schon von Stefan Hoffmann auf "Nested Sets" sowie CTE hingewiesen worden bist, solltest Du mal ein wenig dazu googlen. Die View, die ich im obigen Script erstelle, stellt so eine CTE (common table expression) dar. Diese View bildet u. a. die Hierarchie im Unternehmen dar. Wie Du siehst, bin ich Chef! :)

Gib selbst ein paar Daten in die EmployeeActivity-Relation ein. Die eigentliche Abfrage könnte dann wie folgt aussehen:

SELECT	v.ManagerLevel,
		v.EmployeeName,
		v.HourRate,
		AVG(v.HourRate * DATEDIFF(hour, ea.StartTime, ea.EndTime))	AS	AGV_Rate
FROM	dbo.view_EmployeeStructure v INNER JOIN dbo.EmployeeActivity ea
		ON	(v.EmployeeId = ea.EmployeeId)
WHERE	StartDate BETWEEN @StartDate AND @EndDate
GROUP BY
		GROUPING SETS
		(
			(v.ManagerLevel, v.EmployeeName, v.HourRate),
			(v.ManagerLevel, v.EmployeeName),
			(v.ManagerLevel),
			()
		);

Weitere Informationen zum Thema findest Du hier:

GROUPING SETS: http://msdn.microsoft.com/de-de/library/bb510427(v=sql.105).aspx

CTE: http://msdn.microsoft.com/de-de/library/ms186243(v=sql.105).aspx

---

Uwe Ricken

MCSA - SQL Server 2012
MCITP Database Administrator 2005
MCITP Database Administrator 2008
MCITP Microsoft SQL Server 2008, Database Development

db Berater GmbH
http://www-db-berater.de
SQL Server Blog (german only)

Answer 9

Hallo Thomas,
kannst Du nicht die Access-Daten in Deinen SQL Server laden und dann die Tabellen mit Daten skripten?
Dann könnte man direkt mit dem SQL Server das Problem nachstellen.

Einen schönen Tag noch,
Christoph
--
Microsoft SQL Server MVP
www.insidesql.org/blogs/cmu

Answer 10

Hallo Christoph,

so wie du beschrieben hast, habe ich das Skript für die Nachstellung hinbekommen!

/****** 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](255) NULL,
	[IDRekursiv] [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]) VALUES (1, N'Gesamt', 0)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv]) VALUES (2, N'Meier', 1)
INSERT [dbo].[Struktur] ([ID], [Bezeichnung], [IDRekursiv]) VALUES (3, N'Müller', 2)
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](255) 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

Answer 11

Hallo Uwe,
vielen Dank für dieses Ausführliche Beispiel.
Jedoch habe ich noch eine Frage
In dieser Struktur ist "Uwe" der Chef
Beate, Katharina und Alicia sind dem Uwe untergeordnet
Maria Meier und Georg Meier sind der Beate untergeordnet.
Nun hätte ich gerne zusätzlich noch "Gesamtergebnisse" für
"Uwe": hier sollen alle untergeordneten (also komplett alle) berücksichtigt werden
"Beate": hier sollen alle untergeordneten von Beate (Maria und Georg) berücksichtigt werden.

Geht das auch?

Thomas

Answer 12

Hallo miteinander,

durch eure Tips bin ich nun einen Schritt weitergekommen.

Jedoch habe ich immer noch das Problem mit dem Gesamtergebnis.

Darum beschreibe ich das Problem in einem weiteren Thread noch einmal neu (inklusive Script)

Thomas

@Hallo Forenadmin, oder soll ich hier weitermachen? Dann kannst du es gerne hierher veschieben.