0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for gardeners and landscapers

select
  p.id as [Post Id],
  (select p2.OwnerUserId from posts p2 where p2.id = p.AcceptedAnswerId) as "AAns OwnerId",
  ('=HYPERLINK("http://stackoverflow.com/questions/'+convert(varchar,p.id)+'","'+replace(title,'"','')+'")') as "Title",
  p.ViewCount as "Views", 
  p.score as "Score", 
  p.AnswerCount as "Answers", 
  --DATEDIFF(day,p.CreationDate,getdate()) as "Age (days)",
  --CONVERT(date, p.CreationDate) as "Date Created",
  --p.AcceptedAnswerId as "Answer Accepted?",
  p.tags as Tags,
  p.CreationDate as "Creation Date",
  --p.ClosedDate,
  --(select p2.CreationDate from posts p2 where p2.id = p.AcceptedAnswerId) as "Accepted Ans Dt",
  CASE WHEN
  (select count(u.DisplayName) from users u, posts p2 where u.id = p2.OwnerUserId and p2.id = p.AcceptedAnswerId and
      (u.DisplayName like '%MSFT%' or u.DisplayName like '%Microsoft%' or u.DisplayName like '%microsoft%')) > 0      
    THEN 'Y' ELSE 'N' END as "MSFT",
  --CASE WHEN (select top 1 p3.CreationDate from posts p3 where p3.PostTypeId = 2 and p3.ParentId = p.Id order by p3.CreationDate asc) IS NULL THEN 'N' ELSE 'Y' END as "Responded",
  CASE WHEN
    (select count(*) from posts p3 where p3.PostTypeId = 2 and p3.ParentId = p.Id) = 0 and 
    (select count(*) from comments c where c.postid = p.id) = 0
    THEN 'N' ELSE 'Y' END as "Responded",
  CASE WHEN p.AcceptedAnswerId IS NULL THEN 'N' ELSE 'Y' END as "Answered",
  ('P') as "P/T",
  ('N') as "Analyzed"

from posts p

where
  p.ClosedDate is null and
  --p.AcceptedAnswerId is not null and
  p.CreationDate >= '20180101' and
  p.CreationDate <= '20180131' and

  (
    p.tags like '%<uwp>%' or p.tags like '%<windows-10-universal>%'
    or
    (p.tags like '%<xaml>%' and p.tags like '%<windows-10>%')
  )


order by p.CreationDate asc;

fork query

edited feb 26 18

created jun 6 16 karann