(CTE) Recursive SQL Query

Question

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,

Please read How to ask And How to create a Minimal, Complete, and Verifiable example. We need sample data and desire output to find an answer faster, otherwise we will be wasting time guessing what you need. You also could create your schema with data using sqlfiddle.com — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Oct 26, 2015 at 12:32
Thanks for advice Juan, I'll try to create my schema with data. — GKHN
– GKHN, Commented Oct 26, 2015 at 12:49
@TabAlleman my aim is take the count of the assets that comes selected location's 1 level down — GKHN
– GKHN, Commented Oct 26, 2015 at 13:46
Ok, and how is that different from what your current query produces? Your post doesn't show an example of the results of your current query. — Tab Alleman
– Tab Alleman, Commented Oct 26, 2015 at 14:13

JamieD77 · Accepted Answer · 2015-10-26 15:32:20Z

2

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;

edited Oct 26, 2015 at 15:32

answered Oct 26, 2015 at 15:13

JamieD77

14k1 gold badge19 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

GKHN Over a year ago

Thanks for your helping @JamieD77 and also thanks your solution. Exactly what I wanted.

JamieD77 Over a year ago

i over thought this as usual.. dont really need to get the complicated path in the first cte.. just get the ID.. :)

Collectives™ on Stack Overflow

(CTE) Recursive SQL Query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related