0

Please login or register to vote for this query.

(click on this box to dismiss)

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 */

view current version

edited may 9 13

created may 9 13 anonymous