Tough T-SQL zur Anzeige des Organigramms (Hierarchie / Rekursion)

Question

Jul 22, 2010, 03:36 PM

sql-server-2005 sql-server hierarchical-data

Tough T-SQL zur Anzeige des Organigramms (Hierarchie / Rekursion)

Bitte verweise ich nicht auf einen Artikel zum Erstellen von Baumstrukturen oder CTEs in SQL. Ich habe viel gelesen !!! Ich denke, das ist vielleicht nicht so schwer für das T-SQL im Herzen, aber es ist definitiv schwer für mich:).

Hier ist die Situation, ich muss einen Bericht erstellen, der so aussieht:

alt text http://img85.imageshack.us/img85/6372/70337249.pn

Dies funktioniert hervorragend, wenn der Parameter für meine gespeicherte Prozedur (SQL Server sproc) auf "All" gesetzt ist, da hierdurch nur alle Daten erfasst werden und der Endbenutzer Elemente erweitern / reduzieren kann, um die Hierarchie anzuzeigen. Das Problem tritt beispielsweise auf, wenn ich den Bericht ausführe und einen Namen wähle, wie in diesem Fall "Kevin Bicking". Das Ergebnis wird angezeigt:

alt text http://img69.imageshack.us/img69/8398/46964880.pn

Das Problem dabei ist, dass ich nur den direkten Bericht von Kevin erhalte, aber ich muss tatsächlich alle Sub-Direktiven sehen. Im ersten Bild möchte ich zum Beispiel, dass in meinem Bericht alle Personen unterhalb von Kevin, unterhalb von Kelvin und unterhalb von Tim usw. usw. angezeigt werden.

Ich verstehe das Problem, aber ich weiß nicht, wie ich mit T-SQL umgehen soll. Hier ist meine gespeicherte Prozedur:

CREATE PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
  SET NOCOUNT ON;    

 SELECT 
  c1.id AS EmployeeID,
  c2.id as ManagerID, 
  c1.first_name + ' ' + c1.last_name AS [EmployeeName], 
  c1.title AS Title, 
  c2.first_name + ' ' + c2.last_name AS [ReportsTo]
FROM 
  Contacts c1
INNER JOIN 
  Contacts c2
ON 
  c1.reports_to_id = c2.id
WHERE 
  c1.deleted=0
  AND (@ContactID='All' OR (c2.first_name + ' ' + c2.last_name = @ContactID OR (c1.first_name + ' ' + c1.last_name = @ContactID)))
END

Der Sproc funktioniert einwandfrei, es liegt kein Fehler vor, aber meine Frage bezieht sich auf die Felder, die ich hier aufgelistet habe. Wie kann ich ihn ändern, um die direkten Meldungen unter den oben beschriebenen Namen zu erhalten? Grundsätzlich ist das Feld "EmployeeName" jedes Mal die oberste Ebene (das ist der Berichtsparameter). Der Alias "ReportsTo" ist das Feld im Bericht, das Sie im Bild sehen.

Ich habe keine Frage zum SSRS-Bericht, nur zum Ändern der Abfrage, sodass in diesem Fall Kevin Bicking ausgewählt und an meine gespeicherte Prozedur übergeben wird. Derzeit wird nur der direkte Mitarbeiter Kelvin Squires zurückgegeben. Aber ich möchte, dass es nicht nur Kelvin zurückgibt, sondern auch alle Leute, die Kelvin Bericht erstatten, und alle Leute, die möglicherweise Chefs unter Kelvin sind, aber auch direkte Berichte haben.

Jede Hilfe wird sehr geschätzt. Vielen Dank für Ihre Zeit

Edit PortionIch verwende SQL Server 2005. Jemand hat nach einer Tabellendefinition gefragt. Bitte beachten Sie, dass ich diese Tabelle nicht erstellt habe. Es handelt sich um ein CRM-basiertes System, das automatisch generiert wird:

USE [sugarcrm]
GO
/****** Object:  Table [dbo].[contacts]    Script Date: 07/22/2010 10:44:31 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING OFF
GO
CREATE TABLE [dbo].[contacts](
    [id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [date_entered] [datetime] NULL,
    [date_modified] [datetime] NULL,
    [modified_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [created_by] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [description] [text] COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [deleted] [bit] NULL DEFAULT ('0'),
    [assigned_user_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [team_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [salutation] [varchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [first_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [last_name] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [title] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [department] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [do_not_call] [bit] NULL DEFAULT ('0'),
    [phone_home] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_mobile] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_work] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_other] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [phone_fax] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_street] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_city] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_state] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_postalcode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [primary_address_country] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_street] [varchar](150) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_city] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_state] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_postalcode] [varchar](20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [alt_address_country] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [assistant] [varchar](75) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [assistant_phone] [varchar](25) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [lead_source] [varchar](100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [reports_to_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [birthdate] [datetime] NULL,
    [portal_name] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [portal_active] [bit] NOT NULL DEFAULT ('0'),
    [portal_password] [varchar](32) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [portal_app] [varchar](255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
    [campaign_id] [varchar](36) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
 CONSTRAINT [pk_contacts] PRIMARY KEY CLUSTERED 
(
    [id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF

Lösun

Mit der Hilfe von euch hier war meine Lösung

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go





-- =============================================
-- Author:      <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[rptContactsHierarchy]
@ContactID varchar(100)='All'
AS
BEGIN
SET NOCOUNT ON;

--grab id of @contactid
DECLARE @Test varchar(36)
SELECT @Test = (SELECT id FROM contacts c1 WHERE c1.first_name + ' ' + c1.last_name = @ContactID)


;WITH StaffTree AS 
( 
    SELECT  
        c.id, 
        c.Title, 
        c.first_name, 
        c.last_name, 
        c.reports_to_id, 
        c.reports_to_id as Manager_id, 
        cc.first_name AS Manager_first_name, 
        cc.last_name as Manager_last_name, 
        cc.first_name + ' ' + cc.last_name AS [ReportsTo], 
        c.first_name + ' ' + c.last_name as EmployeeName,  
        1 AS LevelOf 
        FROM Contacts                  c 
            LEFT OUTER JOIN Contacts  cc ON c.reports_to_id=cc.id 
        WHERE c.id=@Test OR (@Test IS NULL AND c.reports_to_id IS NULL) 
    UNION ALL 
        SELECT  
        s.id, 
        s.Title, 
        s.first_name, 
        s.last_name, 
        s.reports_to_id, 
        t.id, 
        t.first_name, 
        t.last_name, 
        t.first_name + ' ' + t.last_name, 
        s.first_name + ' ' + s.last_name,
        t.LevelOf+1 
        FROM StaffTree            t 
            INNER JOIN Contacts  s ON t.id=s.reports_to_id 
    WHERE s.reports_to_id=@Test OR @Test IS NULL OR t.LevelOf>1 
)
SELECT * FROM StaffTree 

END