I'm confused about some problem that related with recursive query.
I'm using SQL SERVER 2012
My scnerio,
locations are defined hierarchical,
Each locations has their own asset.
My Locations table like;
Id | Name | ParentLocationId
----+------+-----------------
1 | L1 | NULL
2 | L2 | 1
3 | L3 | 1
4 | L4 | 1
5 | L5 | 1
6 | L6 | 4
7 | L7 | 4
8 | L8 | 4
9 | L9 | 2
10 | L10 | 2
11 | L11 | 6
12 | L12 | 6
13 | L13 | 6
My Asset table like;
Id | AssetNo | Description | CurrentLocationId
-------+---------+-------------+------------------
1 | AN001 | ADesc | 1
2 | AN002 | BDesc | 1
L1 has 1, L2 has 2, L3 has 0, L4 has 3, L5 has 5, L6 has 5, L7 has 1, L8 has 0, L9 has 3, L10 has 2, L11 has 5, L12 has 3, L13 has 6 Assets
My question is, how can I take the total number of selected Location's 1 level down asset count?
For Example; Selected LocationId = 1 (L1) Sample Output is;
Id | Name | Qty
-------+------+-----
2 | L2 | 7
3 | L3 | 0
4 | L4 | 23
5 | L5 | 5
Another Example; Selected LocationId = 4 (L4)
Id | Name | Qty
---+------+-----
6 | L6 | 19
7 | L7 | 1
8 | L8 | 0
I try wrote a query,
WITH recursiveTable
AS (SELECT *
FROM location l
WHERE ParentLocationId = 1
UNION ALL
SELECT l.*
FROM location l
INNER JOIN recursiveTable r
ON r.Id = l.ParentLocationId),
allLocations
AS (SELECT *
FROM recursiveTable
UNION
SELECT *
FROM Location
WHERE Id = 0),
resultset
AS (SELECT r.NAME AS LocationName,
a.*
FROM allLocations r
INNER JOIN Asset a ON a.CurrentLocationId = r.Id
WHERE r.DataStatus = 1)
select CurrentLocationId
,min(LocationName) as LocationName
,count(Id) as NumberOfAsset
from resultset
group by CurrentLocationId
Additional;
Create Table Location
(
Id int,
Name nvarchar(100),
Description nvarchar(250),
ParentLocationId int,
DataStatus int
)
Create Table Asset
(
Id int,
AssetNo nvarchar(50),
Description nvarchar(250),
CurrentLocationId int,
DataStatus int
)
Insert Into Location Values(1,'L1','LDesc1',NULL,1)
Insert Into Location Values(2,'L2','LDesc2',1,1)
Insert Into Location Values(3,'L3','LDesc3',1,1)
Insert Into Location Values(4,'L4','LDesc4',1,1)
Insert Into Location Values(5,'L5','LDesc5',1,1)
Insert Into Location Values(6,'L6','LDesc6',4,1)
Insert Into Location Values(7,'L7','LDesc7',4,1)
Insert Into Location Values(8,'L8','LDesc8',4,1)
Insert Into Location Values(9,'L9','LDesc9',2,1)
Insert Into Location Values(10,'L10','LDesc10',2,1)
Insert Into Location Values(11,'L11','LDesc11',6,1)
Insert Into Location Values(12,'L12','LDesc12',6,1)
Insert Into Location Values(13,'L13','LDesc13',6,1)
Insert Into Asset Values (1,'FDB-001','Desc1',1,1)
Insert Into Asset Values (2,'FDB-002','Desc2',2,1)
Insert Into Asset Values (3,'FDB-003','Desc3',2,1)
Insert Into Asset Values (4,'FDB-004','Desc4',4,1)
Insert Into Asset Values (5,'FDB-005','Desc5',4,1)
Insert Into Asset Values (6,'FDB-006','Desc6',4,1)
Insert Into Asset Values (7,'FDB-007','Desc7',5,1)
Insert Into Asset Values (8,'FDB-008','Desc8',5,1)
Insert Into Asset Values (9,'FDB-009','Desc9',5,1)
Insert Into Asset Values (10,'FDB-010','Desc10',5,1)
Insert Into Asset Values (11,'FDB-011','Desc11',5,1)
Insert Into Asset Values (12,'FDB-012','Desc12',6,1)
Insert Into Asset Values (13,'FDB-013','Desc13',6,1)
Insert Into Asset Values (14,'FDB-014','Desc14',6,1)
Insert Into Asset Values (15,'FDB-015','Desc15',6,1)
Insert Into Asset Values (16,'FDB-016','Desc16',6,1)
Insert Into Asset Values (17,'FDB-017','Desc17',7,1)
Insert Into Asset Values (18,'FDB-018','Desc18',9,1)
Insert Into Asset Values (19,'FDB-019','Desc19',9,1)
Insert Into Asset Values (20,'FDB-020','Desc20',9,1)
Insert Into Asset Values (21,'FDB-021','Desc21',10,1)
Insert Into Asset Values (22,'FDB-022','Desc22',10,1)
Insert Into Asset Values (23,'FDB-023','Desc23',11,1)
Insert Into Asset Values (24,'FDB-024','Desc24',11,1)
Insert Into Asset Values (25,'FDB-025','Desc25',11,1)
Insert Into Asset Values (26,'FDB-026','Desc26',11,1)
Insert Into Asset Values (27,'FDB-027','Desc27',11,1)
Insert Into Asset Values (28,'FDB-028','Desc28',12,1)
Insert Into Asset Values (29,'FDB-029','Desc29',12,1)
Insert Into Asset Values (30,'FDB-030','Desc30',12,1)
Insert Into Asset Values (31,'FDB-031','Desc31',13,1)
Insert Into Asset Values (32,'FDB-032','Desc32',13,1)
Insert Into Asset Values (33,'FDB-033','Desc33',13,1)
Insert Into Asset Values (34,'FDB-034','Desc34',13,1)
Insert Into Asset Values (35,'FDB-035','Desc35',13,1)
Insert Into Asset Values (36,'FDB-036','Desc36',13,1)
Best Regards,
We can apply a Level and a Path to try and get your child counts, but only display the first level of children. We end up grouping the count of assets by the path, which is the ID of the first level of children. Then select only the first Level at the end
DECLARE @LocationID INT = 1;
WITH recursiveCTE AS
(
SELECT
*,
1 AS [Level],
Id [Path]
FROM
location l
WHERE
l.ParentLocationId = @LocationID
UNION ALL
SELECT
l.*,
[Level] + 1,
[Path]
FROM
location l
JOIN recursiveCTE r ON l.ParentLocationId = r.Id
),
countCte AS (
SELECT
[Path] Id,
COUNT(a.AssetNo) Qty
FROM recursiveCTE c
JOIN Asset a ON c.Id = a.CurrentLocationId
GROUP BY [Path]
)
SELECT r.Id,
r.[Name],
COALESCE(c.Qty,0) Qty
FROM recursiveCTE r
LEFT JOIN countCte c ON r.Id = c.Id
WHERE r.[Level] = 1;