Counts of related VBA-associated applications for questions tagged VBA or *-vba

0

Please login or register to vote for this query.

(click on this box to dismiss)

This query tries to demonstrate that VBA is an orthogonal domain from the individual applications that host it, and the various object models used with it. This is based on the number of questions tagged VBA (or `<app>-vba`), but with no other application-associated tags, or with tags associated with multiple applications. Multiple tags can indicate an association with an application, e.g. the tags [tag:excel-2007] and [tag:excel-2013] are both associated with Microsoft Excel. This query considers as separate only associations with different applications. The second result set shows the mappings from tags to applications.

Stack Overflow

Q&A for professional and enthusiast programmers

DECLARE @TagAppMapping AS TABLE (
  TagId INT PRIMARY KEY,
  TagName NVARCHAR(35),
  AppName NVARCHAR(20)
)


INSERT INTO @TagAppMapping
SELECT Tags.Id AS TagId, Tags.TagName AS TagNameRaw, VBAAppNames.AppName
FROM Tags
INNER JOIN (
  SELECT
    CASE REPLACE(TagName, '-vba', '')
      WHEN 'access' THEN 'ms-access'
      ELSE REPLACE(TagName, '-vba', '')
    END
    AS AppName
  FROM Tags
  WHERE TagName LIKE '%-vba'
    AND Id != (SELECT Id FROM Tags WHERE TagName = 'reflection-vba')  -- reflection-vba doesn't have an application tag
) AS VBAAppNames ON Tags.TagName LIKE '%' + VBAAppNames.AppName + '%'
LEFT JOIN (
  SELECT Posts.Id
  FROM Posts
  WHERE Posts.PostTypeId = 5
    AND (
      Posts.Body LIKE '%Microsoft Word%'
      OR Posts.Body LIKE '%MS Word%'
    )
) AS WordTagWikis ON Tags.WikiPostId = WordTagWikis.Id
WHERE NOT Tags.TagName LIKE '%vision%' -- we only want visio, not vision
  AND Tags.Id != (SELECT Id FROM Tags WHERE TagName = 'word')
  AND ( -- we only want tags with 'word' if they have 'Microsoft Word' or 'MS Word' in the tag wiki
    NOT Tags.TagName LIKE '%word%'
    OR WordTagWikis.Id IS NOT NULL
  );
  
  
WITH VBAPostAppNames AS (
  SELECT PostTags.PostId, AppName
  FROM PostTags
  INNER JOIN (
    SELECT PostId
    FROM PostTags
    WHERE TagId IN (SELECT Id FROM Tags WHERE TagName = 'vba' OR TagName LIKE '%-vba')
  ) AS VBAPosts ON PostTags.PostId = VBAPosts.PostId
  LEFT JOIN @TagAppMapping AS t1 ON PostTags.TagId = t1.TagId
)

SELECT AppCount, COUNT(*) AS PostCount
FROM (
  SELECT PostId, COUNT(DISTINCT AppName) AS AppCount
  FROM VBAPostAppNames
  WHERE AppName IS NOT NULL
  GROUP BY PostId
) AS t2
GROUP BY AppCount

UNION SELECT 0, COUNT(*)
FROM VBAPostAppNames
WHERE AppNAme IS NULL


SELECT *
FROM @TagAppMapping
ORDER BY AppName, TagName

Enter Parameters

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