0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for speakers of other languages learning English

with Migration as (
  select * from PostHistory where PostHistoryTypeId in (17,36)
    and Comment like 'from %'
), Closure as (
  select * from PostHistory where PostHistoryTypeId = 10
    and Comment not in (1,101)  -- not a duplicate
)
select top 1000
  Question.Id as [Post Link],
  Question.CreationDate as [Asked],
  Migration.CreationDate as [Migrated],
  Right(Migration.Comment, Len(Migration.Comment)-5) as [Source],
  Closure.CreationDate as [Closed],
  Coalesce(CloseReasonTypes.Name, Closure.Comment) as [CloseReason],
  case
    when Author.Id is null then Question.OwnerDisplayName
    else 'site://users/' + cast(Author.Id as varchar) + '|' + Author.DisplayName
  end as [Author],
  Question.Score as [Score],
  Question.ViewCount as [Views],
  Question.Tags as [Tags]
from Posts as Question
  join Migration on Question.Id = Migration.PostId
  join Closure on Question.Id = Closure.PostId
  left join CloseReasonTypes on Closure.Comment = CloseReasonTypes.Id
  left join Users as Author on Question.OwnerUserId = Author.Id
where Question.PostTypeId = 1
  and Question.ClosedDate is not null

fork query

edited dec 18 15

created dec 18 15 Ilmari Karonen