0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for users of Iota, the open-source cryptocurrency for IoT that does not use a blockchain

SELECT
    p.Id AS [ParentPostID],
    p.Id AS [PostID],
    CONCAT('site://q/', p.Id, '|', p.Title) AS [PostLinkValid],
    CONCAT('https://stackoverflow.com/q/', p.Id) AS [PostLink],
    'post' AS [PostType],
    p.Score AS [PostScore],
    p.ViewCount AS [ViewCount],
    p.CommentCount,
    p.AnswerCount,
    p.CreationDate AS [PostDate],
    p.Tags AS [PostTags],
    p.Body AS [PostText],
    c.Text AS [CommentText],
    c.Score AS [CommentScore],
    c.CreationDate AS [CommentDate]
FROM Posts p
JOIN PostTags pt ON pt.PostId = p.Id
JOIN Tags t on pt.TagId = t.Id and t.TagName like '%travis-ci'
                                --t.TagName = 'aws-codepipeline'
                                --t.TagName like '%teamcity%'
                                --t.TagName = 'jenkins'
                                --t.TagName like '%gitlab-ci%'
                                --t.TagName like '%github-actions%'
                                --t.TagName like '%circleci%'
                                --t.TagName = 'azure-devops'
                                --t.TagName in ('bitbucket-pipelines', 'cicd', 'continuous-integration', 'continuous-deployment', 'build-automation', 'devops') 

LEFT JOIN Comments c ON c.PostID = p.Id    

WHERE p.CreationDate >= '2023-01-01'

UNION ALL

SELECT
    a.ParentId AS [ParentPostID],
    a.Id AS [PostID],
    CONCAT('site://a/', a.Id, '|', p.Title) AS [PostLinkValid],
    CONCAT('https://stackoverflow.com/a/', a.Id) AS [PostLink],
    'answer' AS [PostType],
    a.Score AS [PostScore],
    a.ViewCount AS [ViewCount],
    a.CommentCount,
    a.AnswerCount,
    a.CreationDate AS [PostDate],
    p.Tags AS [PostTags],
    a.Body AS [PostText],
    c.Text AS [CommentText],
    c.Score AS [CommentScore],
    c.CreationDate AS [CommentDate]
FROM Posts p
JOIN Posts a ON a.ParentId = p.Id
JOIN PostTags pt ON pt.PostId = p.Id
JOIN Tags t on pt.TagId = t.Id and t.TagName like '%travis-ci'
                                --t.TagName = 'aws-codepipeline'
                                --t.TagName like '%teamcity%'
                                --t.TagName = 'jenkins'
                                --t.TagName like '%gitlab-ci%'
                                --t.TagName like '%github-actions%'
                                --t.TagName like '%circleci%'
                                --t.TagName = 'azure-devops'
                                --t.TagName in ('bitbucket-pipelines', 'cicd', 'continuous-integration', 'continuous-deployment', 'build-automation', 'devops') 

LEFT JOIN Comments c ON c.PostID = a.Id

WHERE p.CreationDate >= '2023-01-01'

ORDER BY
    ParentPostID, 
    PostID, 
    CommentDate;

fork query

edited dec 3

created oct 23 anonymous