Following is code to generate additional sample data to test your solutions for BeyondRelational.com's
TSQL Challenge 19. This was done very quick and dirty (ie, very ugly code) and I believe is correct (hopefully!). Please let me know if I have made a mistake or any wrong assumptions. Likely this could be done much better and more concise but it seems to work ok. The script has some flexibility built in where you can choose the # of employees, # of levels in employee hierarchy, and total # of orders. I have also switched the @emp and @ord tables over from variables to actual tables. Also I added the Level column to the emp table to help build the original hierachy - I drop this column later in the script so that it must be determined within my possible solution.
Please let me know if this was useful to you. Thanks!
DECLARE @NumberEmployees int;
SET @NumberEmployees = 50;
-- Maximum level of Employee Hierarchy. Can be multiple root nodes.
DECLARE @MaxLevel int;
SET @MaxLevel = 5;
DECLARE @NumberOrders int;
SET @NumberOrders = 1000;
IF EXISTS (SELECT 1 FROM sys.tables WHERE sys.tables.object_id = OBJECT_ID('dbo.ord', 'U'))
DROP TABLE dbo.ord;
IF EXISTS (SELECT 1 FROM sys.tables WHERE sys.tables.object_id = OBJECT_ID('dbo.emp', 'U'))
DROP TABLE dbo.emp;
IF EXISTS (SELECT 1 FROM sys.tables WHERE sys.tables.object_id = OBJECT_ID('dbo.Hierarchy', 'U'))
DROP TABLE Hierarchy;
CREATE TABLE dbo.emp (
EmployeeID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
FirstName VARCHAR(15) NOT NULL,
LastName VARCHAR(15) NOT NULL,
ReportsTo INT NULL,
Level INT NOT NULL
);
CREATE TABLE dbo.ord (
OrderID INT NOT NULL IDENTITY(1, 1) PRIMARY KEY,
EmployeeID INT NOT NULL
);
ALTER TABLE dbo.emp ADD CONSTRAINT FK_emp_ReportsTo_emp_EmployeeID FOREIGN KEY (ReportsTo) REFERENCES dbo.emp (EmployeeID);
ALTER TABLE dbo.ord ADD CONSTRAINT FK_ord_EmployeeID_emp_EmployeeID FOREIGN KEY (EmployeeID) REFERENCES dbo.emp (EmployeeID);
-- Create emp rows
SET NOCOUNT ON;
DECLARE @i int;
SET @i = 1;
WHILE (@i <= @NumberEmployees)
BEGIN
INSERT emp (LastName, FirstName, Level)
VALUES ('LastName_' + CAST(@i AS varchar(10)), 'FirstName_' + CAST(@i AS varchar(10)), RAND() * @MaxLevel + 1)
SET @i = @i + 1;
END
-- Create Heiararchy
SELECT X.EmployeeID, X.Level empLevel, X.rank empRank,
Y.Level ReportsToLevel, Y.num ReportsToPossibleNumber
INTO Hierarchy
FROM
(SELECT EmployeeID,
Level,
RANK() OVER (PARTITION BY LEVEL ORDER BY EmployeeID) [rank]
FROM emp) X LEFT JOIN
(SELECT Level, COUNT(*) num FROM emp GROUP BY Level) Y
ON X.Level = Y.Level + 1
DECLARE myCursor CURSOR
FOR
SELECT Hierarchy.EmployeeID, Hierarchy.ReportsToLevel, Hierarchy.ReportsToPossibleNumber
FROM Hierarchy
WHERE Hierarchy.ReportsToLevel IS NOT NULL
OPEN myCursor;
DECLARE @empid int, @rpt2lvl int, @rpt2num int;
FETCH NEXT FROM myCursor INTO @empid, @rpt2lvl, @rpt2num
WHILE @@FETCH_STATUS = 0
BEGIN
UPDATE emp SET emp.ReportsTo =
(SELECT TOP 1 H.EmployeeID FROM Hierarchy H WHERE H.empLevel = @rpt2lvl and H.empRank = CAST(RAND() * @rpt2num as int) + 1)
WHERE emp.EmployeeID = @empid;
FETCH NEXT FROM myCursor INTO @empid, @rpt2lvl, @rpt2num
END
CLOSE myCursor;
DEALLOCATE myCursor;
-- Create ord rows
DECLARE @j int;
SET @j = 0;
WHILE (@j < @NumberOrders)
BEGIN
INSERT ord (EmployeeID)
VALUES (RAND() * @NumberEmployees + 1)
SET @j = @j + 1;
END
-- Clean up a bit.
DROP TABLE Hierarchy;
-- Get rid of the Level column from emp table.
ALTER TABLE emp DROP COLUMN Level;
-- see results.
SELECT *
FROM emp;
SELECT *
FROM ord;
-- Run your possible solution next using emp instead of @emp and ord instead of @ord.