0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

-- Backend Frameworks 
-- Graph output  number, string, number 

CREATE TABLE #TagFilter (t int              
              , TagNameGroup nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS
              , FilterTagName nvarchar(35) COLLATE SQL_Latin1_General_CP1_CS_AS);
              
INSERT INTO #TagFilter (t, TagNameGroup, FilterTagName) VALUES 
        (1, 'asp.net-core', 'asp.net-core'),
        (2, 'micronaut', 'micronaut'),
        (2, 'Thinkter', 'Thinkter'),   
        (6, 'quarkus','quarkus'),
        (7, 'spring-boot','spring-boot'),
        (8, 'vert.x','vert.x'),
        (9, 'ktor','ktor'),
        (10, 'symfony', 'symfony'),
         (11, 'asp.net-core', 'asp.net-core');   

DECLARE @PostCreatedAfter smalldatetime, @Today smalldatetime 
SET @Today = GETDATE();
-- SET @PostCreatedAfter = DATEADD(WEEK, -32, GETDATE()) 
SET @PostCreatedAfter = DATEADD(MONTH, -36, GETDATE()) 

   SELECT      
     (DATEDIFF(MONTH,Posts.CreationDate, @PostCreatedAfter)*1) as MonthsAgo
     -- , Tags.TagName
     , #TagFilter.TagNameGroup
     -- ,(DATEDIFF(WEEK,Posts.CreationDate, @PostCreatedAfter)*1) as WeeksAgo
     -- ,DATEPART(year, Posts.CreationDate)*100 + DATEPART(month, Posts.CreationDate) as YearMonth     
     ,Count(Posts.Id) as NrOfPosts     
    FROM Tags
       INNER JOIN #TagFilter ON Tags.TagName = #TagFilter.FilterTagName
       INNER JOIN PostTags ON Tags.Id = PostTags.TagId
       INNER JOIN Posts ON Posts.Id = PostTags.PostId                    
    WHERE Posts.CreationDate >=  @PostCreatedAfter
    AND Posts.CreationDate < DATEFROMPARTS(YEAR(GETDATE()),MONTH(GETDATE()),1) 
    AND (DATEDIFF(MONTH,Posts.CreationDate, @PostCreatedAfter)*1) < 0
   --  AND MONTH(Posts.CreationDate) < MONTH(@Today)
    GROUP BY  #TagFilter.TagNameGroup
    -- GROUP BY Tags.TagName 
     ,DATEDIFF(MONTH,Posts.CreationDate, @PostCreatedAfter)
    ORDER BY MonthsAgo desc
    
    
    
-- Graph query result must look like     
-- number, number
-- or date, number
-- or number, string, number
-- or date, string, number

view current version

edited jan 22 21

created nov 25 20 surfmuggle