0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

create table #Results 
(
  [Site] nvarchar(128) not null,
  [Post ID] int not null,
  [URL] nvarchar(max),
  [Link] nvarchar(max),
  [Type] nvarchar(50),
  [Title] nvarchar(250),
  [HTML] nvarchar(max),
  [Markdown] nvarchar(max),
  [Tags] nvarchar(250),
  [Created] datetime,
  [Last Edit] datetime,
  [Edited By] nvarchar(max),
  [Score] int,
  [Favorites] int,
  [Views] int,
  [Answers] int,
  [Accepted] nvarchar(20),
  [CW] nvarchar(20),
  [Closed] nvarchar(20)
);

DECLARE @collate nvarchar(255) = N' COLLATE SQL_Latin1_General_CP1_CS_AS ';

DECLARE @sql nvarchar(max) = N'DECLARE @AccountId int = ##AccountId:int##
DECLARE @localuserid int = (
SELECT user_id FROM dbo.sede_users  
WHERE site_id = [$site_id$] AND account_id = @AccountId);

   SELECT [Site]       = [$site_name$],
          [Post Id]    = p.Id,
          [URL]        = CONCAT([$site_url$], ''/q/'', p.Id),
          [Link]       = CONCAT([$site_url$], ''/q/'', p.Id, ''|'', p.Title),
          p.[Type],
          p.Title,
          HTML         = p.Body,
          Markdown     = LastRevision.Text,
          Tags         = COALESCE(WikiTags.Tag, p.Tags), 
          p.[Created],
          p.[Last Edit],
          [Edited By]  = COALESCE(Editors.EditorNames, ''n/a''),
          p.Score,
          p.Favorites,
          p.Views,
          p.Answers,
          p.Accepted, 
          p.CW,
          Closed = IIF(p.ClosedDate IS NOT NULL, 
                   IIF (ClosedDupe.DuplicateId IS NOT NULL, ''Duplicate'', ''Closed''), '''')
    FROM 
    ( 
      SELECT p.Id, 
             Title       = COALESCE(p.Title, Parent.Title),-- ' + @collate + N',
             Body        = p.Body,
             [Type]      = pt.Name,
             p.Tags,     
             [Created]   = p.CreationDate,
             [Last Edit] = p.LastEditDate,
             p.Score,
             Favorites   = p.FavoriteCount,
             Views       = COALESCE(p.ViewCount, Parent.ViewCount),
             Answers     = p.AnswerCount,
             Accepted    = IIF(p.AcceptedAnswerId IS NOT NULL 
                           OR p.Id = parent.AcceptedAnswerId, ''Accepted'', ''''),
             CW          = IIF(p.CommunityOwnedDate IS NOT NULL, ''CW'', ''''),
             p.PostTypeId,
             p.ClosedDate
        FROM Posts AS p
        LEFT OUTER JOIN Posts AS Parent
          ON Parent.Id = p.ParentId
        LEFT OUTER JOIN PostTypes AS pt
          ON p.PostTypeId = pt.Id
        WHERE p.OwnerUserId = @localuserid
    ) AS p
    OUTER APPLY 
    (
      SELECT TOP (1) Id, [Text]
        FROM PostHistory 
       WHERE PostId = p.Id
         AND PostHistoryTypeId in (2,5,8)
       ORDER BY Id DESC
    ) AS LastRevision
    OUTER APPLY 
    (
      SELECT TOP (1) DuplicateId = Id 
        FROM PostLinks 
       WHERE PostId = p.Id 
         AND LinkTypeId = 3
    ) AS ClosedDupe
    OUTER APPLY
    (
      SELECT TOP (1) TagName ' + @collate + N'
        FROM Tags 
       WHERE p.PostTypeId IN (4,5)
         AND p.Id IN (Tags.WikiPostId, Tags.ExcerptPostId) 
    ) AS WikiTags(Tag)
    OUTER APPLY 
    (
      SELECT EditorNames = STRING_AGG(CONCAT(Name, CONCAT('' ('', UserId, '')'')), '', '')
                           WITHIN GROUP (ORDER BY FirstEditDate)
        FROM
        (
          SELECT ph.UserId, 
                 Name = COALESCE(ph.Name, u.DisplayName), 
                 FirstEditDate = MIN(ph.CreationDate)
            FROM 
            (
              SELECT UserId,  
                     Name = UserDisplayName ' + @collate + N',
                     CreationDate
                FROM PostHistory WHERE PostId = p.Id
                 AND PostHistoryTypeId IN (4,5)
                 AND NOT (UserId = @localuserid)
            ) AS ph
            LEFT OUTER JOIN Users AS u 
              ON ph.UserId = u.Id
           GROUP BY UserId, COALESCE(ph.Name, u.DisplayName)
        ) AS Editors
    ) AS Editors;';

insert into #Results 
  EXEC dbo.sede_ineachdb @sql, @CollectResultsForMe = 0;
      
SELECT * FROM #Results;;;

fork query

edited apr 30

created apr 30 Aaron Bertrand