Results 1 to 2 of 2
  1. #1
    Join Date
    Sep 2003
    Posts
    2

    Unanswered: Count of Descendents?

    Bear with me I'm not a TSQL expert. I am writing a directory, and I want to get the count of all descendents underneath a selected parent category. I had this in done in VB code, but I want to transfer this function to a sproc.

    I am able to build the structure and provide parent paths, counts, etc. The table structure consists of two tables a categories table, and sites table. Within the categories table their is a SiteCount field, which contains all sites within that specific category. Now I want to get all the descendants of the category and populate a CulCount field. The sproc I have now is the following:

    ALTER procedure GetCategories

    @ParentID int,
    @moduleID int

    as

    DECLARE @Level Int
    DECLARE @Rows Int
    DECLARE @id int
    DECLARE @cChildren int

    SET @Level = 0

    --Temporary table
    CREATE TABLE #Test2(PK Int IDENTITY (1,1), CategoryId Int, level Int, CategoryName varchar(100),SortColumn varchar(1000), Path varchar(1000), SiteCount Int, CulCount Int, DateSiteAdded datetime, ParentID Int)

    INSERT INTO #Test2 (CategoryId, Level, CategoryName, SortColumn, Path, SiteCount, DateSiteAdded, ParentID)

    SELECT
    CategoryID, 0, CategoryName, ';' + LTRIM(STR(@ParentID)) + ';', 'Root > ' + Categories.CategoryName,
    (SELECT Count(SiteID) FROM Sites WHERE Sites.SiteCatID = CategoryID AND SiteActive = 1) As SiteCount,
    Categories.DateSiteAdded,
    @ParentID
    FROM
    Categories
    WHERE
    ParentId = @ParentID AND ModuleID = @ModuleId
    SELECT @Rows = @@RowCount

    WHILE @Rows > 0
    BEGIN
    INSERT INTO #Test2 (CategoryId, Level, CategoryName, SortColumn, Path, SiteCount, DateSiteAdded, ParentID)

    SELECT S.CategoryID, @Level + 1, S.CategoryName, SortColumn + LTRIM(RTRIM(STR(T.CategoryId))) + ';', Path + ' > ' + T.CategoryName,
    (Select Count(SiteID) As Count FROM Sites WHERE SiteCatID = S.CategoryID AND SiteActive = 1)As SiteCount, S.DateSiteAdded, S.ParentID
    FROM #Test2 T
    JOIN Categories S On S.ParentId=T.CategoryId And T.Level = @Level

    SELECT @Rows = @@RowCount, @Level = @Level + 1
    END

    SELECT CategoryID, CategoryName, Path, SiteCount, DateSiteAdded, ParentID, SortColumn, CulCount
    FROM #Test2 WHERE ParentID = @ParentID
    ORDER BY SortColumn, CategoryName

    DROP TABLE #Test2


    This provides me with almost everything I need, except the culmative count of all descendent sites, I have been working on this for days and I can't find an answer. I am trying to stay away from using recursive functions for this so I can support a larger variety of databases if needed. Thanks for the assist.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    While @@Rowcount > 0
    update #Test2
    set CulCount = SiteCount + ChildSites
    from #Test2
    inner join --Get the count of sites from children
    (select #Test2.PK, sum(Children.CulCount) ChildSites
    from #Test2
    inner join #Test2 Chilren on #Test2.PK = Chilren.ParentID) ChildTotals
    on #Test2.PK = ChildTotals.PK
    where not exists --Only update the lowest level that has not been updated
    (select *
    from #Test2 SubTable
    where SubTable.ParentID = #Test2.PK
    and SubTable.CulCount is null)

    blindman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •