CREATE PROCEDURE dbo.[Forum_Thread_GetSitemapThreads] @PortalID INT
AS
    BEGIN
        SELECT  [Subject],
                Body,
                P.CreatedDate,
                UserID AS StartedByUserID,
                T.ThreadID,
                F.ForumID,
                [Views],
                LastPostedPostID AS LastApprovedPostID,
                ( SELECT    COUNT(ThreadID)
                  FROM      dbo.Forum_Posts
                  WHERE     ThreadID = T.ThreadID
                            AND PostID <> T.ThreadID
                ) AS Replies,
                IsPinned,
                PinnedDate,
                IsClosed,
                ThreadStatus,
                AnswerPostID,
                AnswerUserID,
                PollID,
                AnswerDate,
                SitemapInclude,
                ISNULL(( SELECT MAX(ThreadID)
                         FROM   dbo.Forum_Threads
                         WHERE  ThreadID < T.ThreadID
                                AND ForumID = T.ForumID
                       ), 0) AS PreviousThreadID,
                ISNULL(( SELECT MIN(ThreadID)
                         FROM   dbo.Forum_Threads
                         WHERE  ThreadID > T.ThreadID
                                AND ForumID = T.ForumID
                       ), 0) AS NextThreadID,
                ISNULL(( SELECT COUNT(ThreadID)
                         FROM   dbo.Forum_ThreadRating TR
                         WHERE  TR.ThreadID = T.ThreadID
                       ), 0) AS RatingCount,
                ISNULL(( SELECT AVG(Rate)
                         FROM   dbo.Forum_ThreadRating TR
                         WHERE  TR.ThreadID = T.ThreadID
                       ), 0) AS Rating,
                1 AS TotalRecords,
                CI.CreatedByUserID,
                CI.CreatedOnDate,
                CI.ContentKey,
                CI.Indexed,
                CI.Content,
                CI.ContentItemID,
                CI.LastModifiedByUserID,
                CI.LastModifiedOnDate,
                CI.ModuleID,
                CI.TabID,
                CI.ContentTypeID
        FROM    dbo.Forum_Threads T
                INNER JOIN dbo.Forum_Forums F ON T.ForumID = F.ForumID
                INNER JOIN dbo.Forum_Groups G ON G.GroupID = F.GroupID
                INNER JOIN dbo.ContentItems CI ON T.ContentItemID = CI.ContentItemID
                INNER JOIN dbo.Forum_Posts P ON T.ThreadID = P.PostID
        WHERE   PortalID = @PortalID
                AND T.SitemapInclude = 1
                AND F.EnableSitemap = 1
             AND PublicView = 1
             ORDER BY TabID ASC, F.SortOrder ASC, LastPostedPostID DESC
    END

GO