Torna al Thread
USE tempdb;
GO
-- creo una tabella di esempio
CREATE TABLE dbo.gerarchia
(
Agente varchar(5) NOT NULL
, Padre varchar(5) NULL
, Nome varchar(30) NOT NULL
, Cognome varchar(30) NOT NULL
, CONSTRAINT PK_dbogerarchia PRIMARY KEY CLUSTERED
(
Agente
)
)
-- popolamento dati di esempio (se il presentatore è NULL allora il presentato è il capo dell'albero/gerarchia)
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0001', NULL, 'A', 'C');
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0011', 'A0001', 'E', 'D');
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0021', 'A0001', 'S', 'B');
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0111', 'A0011', 'E', 'T');
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0121', 'A0011', 'R', 'E');
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0031', 'A0001', 'A', 'A');
INSERT INTO dbo.gerarchia (Agente, Padre, Nome, Cognome)
VALUES ('A0041', 'A0001', 'A', 'B');
GO
-- ipotizziamo poi di avere una tabella dei venduti per agente e data
CREATE TABLE dbo.Venduto
(
Agente varchar(5) NOT NULL
, Data date NOT NULL
, Venduto money NOT NULL
, CONSTRAINT PK_dboVenduto PRIMARY KEY CLUSTERED
(
Agente
, Data
)
)
GO
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0001', '20110101', 50);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0001', '20110201', 50);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0011', '20110101', 20);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0011', '20110201', 40);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0021', '20110101', 20);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0031', '20110101', 30);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0031', '20110201', 10);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0041', '20110101', 70);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0111', '20110101', 30);
INSERT INTO dbo.Venduto (Agente, Data, Venduto)
VALUES ('A0121', '20110101', 80);
GO
-- quelli che superano un venduto di 40€ avranno il 2% di provvigione, il calcolo lo ottieni scorrendo in questo modo la gerarchia:
-- ricavo la gerarchia a livelli usando una CTE (link: http://technet.microsoft.com/it-it/library/cc645516.aspx)
;WITH Livelli (Agente, Padre, Nome, Cognome, Livello, Ordinamento)
AS
(
SELECT
Agente
, Padre
, Nome
, Cognome
, Livello = 1
, CAST(Agente AS VARBINARY(100))
FROM
dbo.gerarchia
WHERE
Padre IS NULL -- membro iniziale per la ricorsione
UNION ALL
SELECT
GER.Agente
, GER.Padre
, GER.Nome
, GER.Cognome
, Livello = LIV.Livello + 1
, CAST(Ordinamento + CAST(GER.Agente AS BINARY(5)) AS VARBINARY(100))
FROM
dbo.gerarchia GER
JOIN Livelli LIV ON GER.Padre = LIV.Agente
)
-- questa è la tua gerarchia a livelli, legata col venduto. Viene valorizzato un campo calcolato che è l'eventuale provvigione
SELECT
L.Agente
, L.Padre
, L.Nome
, L.Cognome
, L.Livello
, V.VendutoPerAgente
-- qui controllo se il venduto è maggiore di 40€
, Provvigione = CASE WHEN V.vendutoPerAgente > 40 THEN V.VendutoPerAgente / 100 * 2 ELSE 0 END
FROM
Livelli L
JOIN (SELECT Agente, VendutoPerAgente = SUM(Venduto) FROM dbo.Venduto GROUP BY Agente) V ON L.Agente = V.Agente
ORDER BY
Ordinamento;
GO
-- elimino le tabelle di esempio
DROP TABLE dbo.gerarchia;
GO
DROP TABLE dbo.Venduto;
GO