Q&A about the site for professionals in medical and allied health fields, students of those professions, related academics, and others with a sound understanding of medicine and healthcare-related sciences
SELECT distinct /* OwnerDisplayName as OwnerDisplayName, datepart(yyyy, CreationDate) as [Year], datepart(m, CreationDate) as [Month],*/ count(OwnerUserId) as [Total Users], sum(activity) as [Total Activity], sum(AnswerCount) as AnswerCount, sum(CommentCount) as CommentCount, /**Offerings tagged **/ sum(MWact) AS MWact,sum(MWansw) AS MWansw,sum(MWcomm) AS MWcomm, sum(paasact) AS paasact,sum(paasansw) AS paasansw,sum(paascomm) AS paascomm, sum(MWpaas) as MWpaas FROM ( select OwnerUserId as OwnerUserId, CreationDate as CreationDate, sum(counter) as activity, sum(AnswerCount) as AnswerCount, sum(CommentCount) as CommentCount, /**Offerings tagged **/ sum(MWact) AS MWact,sum(MWansw) AS MWansw,sum(MWcomm) AS MWcomm, sum(paasact) AS paasact,sum(paasansw) AS paasansw,sum(paascomm) AS paascomm, sum(MWact) + sum(MWansw) + sum(MWcomm) as MWtot, sum(paasact) + sum(paasansw) + sum(paascomm) as paastot, /** Cross offerings tagged **/ CASE WHEN (sum(MWact) + sum(MWansw) + sum(MWcomm)) > 0 and (sum(paasact) + sum(paasansw) + sum(paascomm)) > 0 THEN 1 ELSE 0 END AS MWpaas /*** INITIAL DATA RETRIEVE ***/ FROM ( select OwnerUserId as OwnerUserId, CreationDate as CreationDate, counter as counter, AnswerCount as AnswerCount, CommentCount as CommentCount, /**Offerings tagged **/ /* MW Overlaps */ /*** Change Tags to toggle between MW ***/ CASE WHEN Tags like '%websphere %' THEN 1 ELSE 0 END AS MWact, CASE WHEN Tags like '%websphere %' THEN AnswerCount ELSE 0 END AS MWansw, CASE WHEN Tags like '%websphere %' THEN CommentCount ELSE 0 END AS MWcomm, CASE WHEN (Tags like '%<azure>%' or Tags like '%<google-app-engine>%' or Tags like '%<heroku>%' or Tags like '%<force.com>%' or Tags like '%amazon%') THEN 1 ELSE 0 END AS paasact, CASE WHEN (Tags like '%<azure>%' or Tags like '%<google-app-engine>%' or Tags like '%<heroku>%' or Tags like '%<force.com>%' or Tags like '%amazon%') THEN 1 ELSE 0 END AS paasansw, CASE WHEN (Tags like '%<azure>%' or Tags like '%<google-app-engine>%' or Tags like '%<heroku>%' or Tags like '%<force.com>%' or Tags like '%amazon%') THEN 1 ELSE 0 END AS paascomm from ( select OwnerUserId as OwnerUserId, 1 as counter, AnswerCount as AnswerCount, CommentCount as CommentCount, Tags as Tags, CreationDate as CreationDate from posts as data1 ) /*** END INITIAL DATA RETRIEVE ***/ as data2 ) as data3 GROUP BY OwnerUserId, CreationDate ) as data4 /* group by datepart(yyyy, CreationDate) , datepart(m, CreationDate) order by datepart(yyyy, CreationDate) , datepart(m, CreationDate) */ /* where activity > 1 GROUP BY OwnerDisplayName */