
CREATE TABLE DetailAccount(id INT PRIMARY KEY,
parent INT, balance FLOAT)
CREATE TABLE RollupAccount(id INT PRIMARY KEY,
parent INT)
INSERT INTO DetailAccount VALUES (3001, 2001, 10)
INSERT INTO DetailAccount VALUES(4001, 3002, 12)
INSERT INTO DetailAccount VALUES(4002, 3002, 14)
INSERT INTO DetailAccount VALUES(3004, 2002, 17)
INSERT INTO DetailAccount VALUES(3005, 2002, 10)
INSERT INTO DetailAccount VALUES(3006, 2002, 25)
INSERT INTO DetailAccount VALUES(3007, 2003, 7)
INSERT INTO DetailAccount VALUES(3008, 2003, 9)

INSERT INTO RollupAccount VALUES(3002, 2001)
INSERT INTO RollupAccount VALUES(2001, 1000)
INSERT INTO RollupAccount VALUES(2002, 1000)
INSERT INTO RollupAccount VALUES(2003, 1000)
INSERT INTO RollupAccount VALUES(1000, 0)


WITH Rollup
AS
(
SELECT id, parent FROM RollupAccount 
WHERE id = 1000
UNION ALL
SELECT R.id, R.parent FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT SUM(balance) AS Balance
FROM DetailAccount
WHERE Parent in (SELECT id FROM Rollup)


WITH Rollup
AS
(
SELECT id, parent FROM RollupAccount 
WHERE id = 1000
UNION ALL
SELECT R.id, R.parent FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT SUM(balance) AS Balance
FROM DetailAccount
JOIN
Rollup
ON DetailAccount.parent = Rollup.id




WITH Rollup
AS
(
SELECT id, parent FROM RollupAccount 
WHERE id = 1000
UNION ALL
SELECT R.id, R.parent FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT SUM(balance) AS Balance
FROM DetailAccount
JOIN
Rollup
ON DetailAccount.parent = Rollup.id
OPTION (MAXRECURSION 1)












WITH Rollup
AS
(
SELECT id, parent, 0 AS depth FROM RollupAccount 
WHERE id = 1000
UNION ALL
SELECT R.id, R.parent, depth + 1 AS depth FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
WHERE depth < 1
)
SELECT SUM(balance) AS Balance
FROM DetailAccount
JOIN
Rollup
ON DetailAccount.parent = Rollup.id




WITH Rollup
AS
(
SELECT id, parent, 0 as lvl FROM RollupAccount where id = 1000
UNION ALL
SELECT R.id, R.parent, lvl + 1 FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT id, parent from rollup
UNION
SELECT 10000, 0



WITH Rollup
AS
(
SELECT id, parent FROM RollupAccount 
WHERE id = 1000
UNION ALL
SELECT R.id, R.parent FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT id, parent, balance AS Balance
FROM DetailAccount
WHERE Parent in (SELECT id FROM Rollup)
UNION
SELECT 10000, 0, 0

CREATE 
--ALTER
FUNCTION AccountDescendents(@account INT)
RETURNS TABLE
RETURN WITH Rollup
AS
(
SELECT id, parent FROM RollupAccount 
WHERE id = @account
UNION ALL
SELECT R.id, R.parent FROM
RollupAccount AS R
JOIN
Rollup AS RP
ON R.parent = RP.ID
)
SELECT id FROM Rollup

select * from dbo.AccountDescendents(2001)

SELECT SUM(balance) FROM DetailAccount
JOIN dbo.AccountDescendents(2001) AS R
ON DetailAccount.parent = R.id


CREATE SYNONYM dept FOR AdvenentureWorks.HumanResources.Department

CREATE 
--DROP
SYNONYM emp FOR AdventureWorks.HumanResources.Employee


select count(*) from emp
select employeeID from emp where managerid is null

select MAX(EmployeeID) from emp

WITH PEMP
AS
(
SELECT EmployeeID, ManagerID, 0 as depth FROM emp
WHERE EmployeeID = 109
UNION ALL
SELECT E.EmployeeID, E.ManagerID, depth + 1 as depth from 
emp E JOIN PEMP P
ON E.ManagerID = P.EmployeeID
)
SELECT max(depth) from PEMP

SELECT EmployeeID from emp
where employeeID = managerID


WITH PEMP
AS
(
SELECT EmployeeID, ManagerID FROM emp
WHERE EmployeeID = 290
UNION ALL
SELECT E.EmployeeID, E.ManagerID from 
emp E JOIN PEMP P
ON P.ManagerID = E.EmployeeID
)
SELECT * from PEMP


SELECT employeeid from emp
where managerid is null

CREATE 
--ALTER
FUNCTION AWEmpTree(@startAt INT)
RETURNS TABLE
RETURN
WITH Rollup
AS
(
SELECT EmployeeID, ManagerID, 0 AS depth
FROM AdventureWorks.HumanResources.Employee
WHERE EmployeeID = @startAt
UNION ALL
SELECT E.EmployeeID, E.ManagerID, depth + 1 AS depth
FROM AdventureWorks.HumanResources.Employee E
JOIN Rollup AS R ON R.EmployeeID = E.ManagerID 
)
SELECT EmployeeID, ManagerID, depth FROM Rollup


select * from dbo.AWEmpTree(273)
UNION
SELECT 0,0,10000
ORDER BY DEPTH

select * from dbo.AWEmpAncestors(109)

select * from dbo.AWEmpAncestors(290)
UNION
SELECT 10000, 10000


SELECT * FROM dbo.AWEmpAncestors(287)
ORDER BY distance


SELECT EmployeeID AS [Direct Reports]
FROM dbo.AWEmpTree(140)
WHERE depth = 1
UNION SELECT 5000



SELECT COUNT(*) - 1 [Number of Reports]
FROM dbo.AWEmpTree(140)
UNION SELECT 5000



SELECT Employee


select count(*) from emp


CREATE 
--ALTER
FUNCTION AWEmpAncestors(@startAt INT)
RETURNS TABLE
RETURN
WITH Rollup
AS
(
SELECT EmployeeID, ManagerID, 0 AS distance
FROM AdventureWorks.HumanResources.Employee
WHERE EmployeeID = @startAt
UNION ALL
SELECT E.EmployeeID, E.ManagerID, distance + 1 AS distance
FROM AdventureWorks.HumanResources.Employee E
JOIN Rollup AS R ON E.EmployeeID = R.ManagerID 
)
SELECT EmployeeID, distance FROM Rollup


select * from AdventureWorks.HumanResources.Employee

WITH Ancestors
AS
(
SELECT EmployeeID from dbo.AWEmpAncestors(273)
)
Select NationalIDNumber FROM AdventureWorks.HumanResources.Employee
WHERE EmployeeID IN (SELECT EmployeeID FROM dbo.AWEmpAncestors(273))
