October 14, 2010 at 3:54 pm
Thanks to anyone who can help.
--DROP TABLE History
CREATE TABLE History
(
ID int,
Parent_ID int
)
INSERT INTO History
SELECT 1,NULL UNION
SELECT 2,1 UNION
SELECT 3,NULL UNION
SELECT 4,3 UNION
SELECT 5,4 UNION
SELECT 6,NULL UNION
SELECT 7,5 UNION
SELECT 8,2 UNION
SELECT 10,NULL
SELECT * FROM History
DECLARE @ID int
SET @ID = 8
SELECT ID FROM History WHERE ID = @ID
Essentially I want to walk back up the history table and select all IDs associated with a particular ID I pass as a parameter.
Expected results: to the left of the = sign is the parameter I am passing, to the right are the records I want returned.
1=1
2=2,1
3=3
4=4,3
5=5,4,3
6=6
7=7,5,4,3
8=8,2,1
10=10
October 14, 2010 at 5:13 pm
Something like this?
;
WITH CTE (id, Parent_ID, [LEVEL]) -- recursive CTE
AS
(
SELECT h.id, h.Parent_ID, 0 AS LEVEL -- get all id's
FROM History h
UNION ALL
SELECT h.id, ph.Parent_ID, LEVEL + 1 -- and all related "subordinated" id's
FROM History h
INNER JOIN CTE ph ON ph.id = h.Parent_ID
)
SELECT
CAST(ID AS VARCHAR(2))+ -- select the "anchor" id
ISNULL(
(SELECT ','
+ CAST(parent_id AS VARCHAR(2))
FROM CTE t2
WHERE t2.id = t1.id AND parent_id IS NOT NULL
ORDER BY LEVEL
FOR XML PATH('') -- and a concatenated list of all "subordinates" ordered by level
),'')
FROM cte t1
WHERE id=@ID
GROUP BY id
Side note: if you have a hierarchy with more than one child per parent_id you need to define the order in the concatenated list.
October 14, 2010 at 7:25 pm
Lutz - looks great... except this is a SQL 7/2000 forum.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 15, 2010 at 2:14 am
You're not using SQL2000, are you? (fingers crossed)
If you're still at SQL 2000, it'll require "slightly more work".
One option is to use a stored procedure that calls itself. An example can be found at http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
@Wayne: It seems like my answer goes into the "just for exercise" box then...
I put a post-it on my screen: ALWAYS CHECK THE FORUM BEFORE TRYING TO ANSWER!
Thanx 😉
October 15, 2010 at 7:31 am
LutzM (10/15/2010)
@Wayne: It seems like my answer goes into the "just for exercise" box then...I put a post-it on my screen: ALWAYS CHECK THE FORUM BEFORE TRYING TO ANSWER!
Thanx 😉
I understand. I was about 3/4 through coding a recursive CTE myself for this (for my exercise in solving this), when I happened to notice the forum.
Wayne
Microsoft Certified Master: SQL Server 2008
Author - SQL Server T-SQL Recipes
October 17, 2010 at 8:16 pm
Basing on the data sample from the article I built a "chart dependency" table which reflects different levels of subordination:
CREATE TABLE dbo.EmpChart
(
EmpIDint FOREIGN KEY REFERENCES dbo.Emp(EmpID),
BossID int FOREIGN KEY REFERENCES dbo.Emp(EmpID),
HierarchyLevelint
PRIMARY KEY (EmpID, BossID),
UNIQUE (BossID, EmpID)
)
GO
DECLARE @RCNT int
INSERT INTO dbo.EmpChart
(EmpID, BossID, HierarchyLevel)
SELECT EmpID, MgrID, 1
FROM dbo.Emp E
WHERE MgrID IS NOT NULL
AND NOT EXISTS (select * from dbo.EmpChart C
where C.EmpID = E.EmpID and C.BossID = E.MgrID)
SET @RCNT = @@ROWCOUNT
WHILE @RCNT > 0
BEGIN
INSERT INTO dbo.EmpChart
(EmpID, BossID, HierarchyLevel)
SELECT E.EmpID, EC.BossID, EC.HierarchyLevel + 1
FROM dbo.Emp E
INNER JOIN dbo.EmpChart EC ON EC.EmpID = E.MgrID
WHERE MgrID IS NOT NULL
AND NOT EXISTS (select * from dbo.EmpChart C
where C.EmpID = E.EmpID and C.BossID = EC.BossID)
SET @RCNT = @@ROWCOUNT
RAISERROR ('Rows Inserted: %d', 0,1, @RCNT) WITH NOWAIT
END
GO
SELECT * FROM EmpChart
ORDER BY EmpID, HierarchyLevel, BossID
GO
Following query returns exatctly what OP was asking for:
DECLARE @BossID int
SET @BossID = 7
SELECT * FROM EmpChart C
INNER JOIN dbo.Emp E ON E.EmpID = C.EmpID
WHERE BossID = @BossID
ORDER BY C.HierarchyLevel, C.EmpID, C.BossID
If to put the code populating table EmpChart nto a trigger on table Emp then every change in the Org Chart will be reflected immediately in EmpChart and ready for querying.
The advantage of this method is that hierarchies are resolved only once, when they are updated/created. Since such updates happen normally very seldom resolving the hierarchies actually is not required most of the time.
It would not make much of a difference on small datasets, but with really big and complex dependencies even CTE approach can put a lot of pressure on a server.
_____________
Code for TallyGenerator
October 18, 2010 at 8:51 am
Thanks all. Yes I am still using 2000 (or at least my vendor is). This definitely is a bigger can of worms than I first thought!
I am going to try the CTE through a linked server first. Then I'll take a look at http://vyaskn.tripod.com/hierarchies_in_sql_server_databases.htm
October 18, 2010 at 9:21 am
If to put the code populating table EmpChart nto a trigger on table Emp then every change in the Org Chart will be reflected immediately in EmpChart and ready for querying.
The advantage of this method is that hierarchies are resolved only once, when they are updated/created. Since such updates happen normally very seldom resolving the hierarchies actually is not required most of the time.
It would not make much of a difference on small datasets, but with really big and complex dependencies even CTE approach can put a lot of pressure on a server.
Thanks for this, I can see using this under other circumstances but I can't use triggers in this situation.
October 18, 2010 at 2:16 pm
LutzM (10/14/2010)[/b]
Something like this?
I tried this using linked server and had to terminate the query after 5 minutes. My table has 75 million rows.
I added WHERE clauses and then ran into
The statement terminated. The maximum recursion 30000 has been exhausted before statement completion. ( I had set max recursion to 30,00 as a test.)
I am going to look at the SQL Server 2000, non linked possibilities.
Side note: if you have a hierarchy with more than one child per parent_id you need to define the order in the concatenated list.
I actually don't need the concatenated list and have eliminated that part from my test. It was just how I expressed my expected results.
October 28, 2010 at 10:15 am
Thanks to those who responded, it was educational. Pursuing hierarchical processing is beyond my scope right now so I'd like to close the thread.
October 28, 2010 at 5:50 pm
With little help from Calendar table you may add the days which did not have any hours recorded:
-- Create temporary table
IF OBJECT_ID('Tempdb..#PontoRegistos') IS NOT NULL
DROP TABLE #PontoRegistos
CREATE TABLE #PontoRegistos (
UtilizadorNome varchar(50),
Horas int,
DataRegisto datetime,
PRIMARY KEY (DataRegisto, UtilizadorNome)
)
-- This table should be static in your "DBA" database on every server instance
IF OBJECT_ID('Tempdb..#Calendar') IS NOT NULL
DROP TABLE #Calendar
CREATE TABLE #Calendar (
ID int IDENTITY(0,1) NOT NULL,
[Date] AS DATEADD(dd, ID, CONVERT(datetime, 0, 120)),
C1 int,
PRIMARY KEY NONCLUSTERED (ID)
)
CREATE CLUSTERED INDEX IX_CalendarDate ON #Calendar([Date])
INSERT INTO #Calendar ([C1])
SELECT TOP 65536 1
FROM syscolumns c1, syscolumns c2, syscolumns c3
-- Insert some sample data
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 2, '20101018 12:20')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 6, '20101019 23:59')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 3, '20101020 00:00')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 3, '20101021 8:30')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 1, '20101012 10:00')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 2, '20101012 11:00')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 4, '20101012 14:00')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 5, '20101018 9:00')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 8, '20101021 8:00')
INSERT INTO #PontoRegistos VALUES ('rootfixxxer', 2, '20101021 15:00')
INSERT INTO #PontoRegistos VALUES ('Gianluca', 4, '20101021 16:00')
DECLARE @data AS DATETIME
SET @data = '20101020'
SELECT C.[Date], ISNULL(UtilizadorNome, ''),
ISNULL(SUM(Horas), 0) AS SumHoras
FROM #Calendar C
LEFT JOIN #PontoRegistos R ON R.DataRegisto >= C.[Date] AND R.DataRegisto < C.[Date] + 1
AND DataRegisto >= @data AND DataRegisto < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)
WHERE C.[Date] >= @data AND C.[Date] < DATEADD(dd, DATEDIFF(dd, 0, GETDATE()), 1)
GROUP BY UtilizadorNome, DATEADD(dd, DATEDIFF(dd, 0, DataRegisto), 0), C.[Date]
ORDER BY UtilizadorNome, DATEADD(dd, DATEDIFF(dd, 0, DataRegisto), 0)
_____________
Code for TallyGenerator
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply