DECLARE @StartTime DATE = convert(DATE, '##StartTime?2000...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Interpersonal Skills

Q&A for people looking to improve their interpersonal communication skills

--StartTime: Start time of plot
DECLARE @StartTime DATE = convert(DATE, '##StartTime?2000/01/01##')
DECLARE @EndTime DATE = convert(date,dateadd(day,-5-datepart(weekday,getdate()),getdate()))
SET DATEFIRST 1 -- Monday as week start.

select
  WeekOf,
  sum(cast(case when datediff(second, QDate, FADate) < 1209600 then 1 else 0 end as tinyint)) as 'A 14d',
  sum(cast(case when datediff(second, QDate, FADate) < 604800 then 1 else 0 end as tinyint)) as 'A 7d',
  sum(cast(case when datediff(second, QDate, FADate) < 86400 then 1 else 0 end as tinyint)) as 'A 1d',
  sum(cast(case when FADate is not null then 1 else 0 end as tinyint)) as 'A',
  sum(1-Clo) as 'not closed',
  sum((1-Clo)*(1-Del)) as 'not clo/del',
  count(*) as 'all'
from(select -- SUBQUERY: select question date, first answer date, deleted flag
    dateadd(day, 1-datepart(weekday,q.CreationDate), convert(date,q.CreationDate)) as 'WeekOf', -- Week
    q.CreationDate as 'QDate', -- Question date
    min(a.CreationDate) as 'FADate', -- First answer date
    cast(case when q.ClosedDate is not null then 1 else 0 end as tinyint) as 'Clo', -- Closed
    cast(case when q.DeletionDate is not null then 1 else 0 end as tinyint) as 'Del' -- Deleted
  from
    PostsWithDeleted as q left outer join Posts as a on a.ParentId = q.Id
    INNER JOIN PostTags on q.Id = PostTags.PostId
    INNER JOIN Tags on PostTags.TagId = Tags.Id
  where
    Tags.TagName = 'openmp'
    and q.CreationDate > @StartTime and q.CreationDate < @EndTime
    and q.PostTypeId = 1 -- questions only
    group by q.Id,q.CreationDate,q.ClosedDate,q.DeletionDate
    ) qa
group by WeekOf
order by WeekOf

Enter Parameters

Options:
Switch sites:
loading Hold tight while we fetch your results