1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

--  from https://meta.stackexchange.com/questions/359794
declare @url_for_this_db varchar(80)

;with alldbs as
(
select [Url]
     , SiteName
     , DatabaseName
from
(
  select (pid - 8) pid
     , value [Url]
     , Lead(value, 1) over (order by pid) [SiteName]
     , Lead(value, 2) over (order by pid) [DatabaseName]
  from (
    SELECT row_number() over(order by @@identity ) pid
         , LEFT(TRIM(VALUE), 100) Value FROM STRING_SPLIT(
    -- fetch Markdown for post containing table with site details
    (SELECT TOP 1  Text
     FROM [StackExchange.Meta].dbo.PostHistory
     WHERE PostId = 359796 AND PostHistoryTypeId = 5
     ORDER BY CreationDate DESC)
    , '|')
    WHERE LEN(TRIM(REPLACE(REPLACE(VALUE, CHAR(10), ''), CHAR(13), ''))) > 0
  ) src
  where pid >= 8
) data
where pid % 3 = 0
and url like 'https://%' -- ouch 
)

select @url_for_this_db = concat('%',replace(url, 'https://', ''), '%')
from alldbs
where databasename = db_name();

-- the actual query

select posts.body
     , case 
         when Reputation < 15 then 1 
         when reputation between 15 and 50 then 2
         else 3
       end as repcategory
into #posts
from Users 
inner join posts on posts.owneruserid = users.id
WHERE Users.Reputation < 100
and posttypeid = 2

select 
   choose(
      repcategory, 
     'None', 
     'FlagOnly',
     'FlagAndComment') AS Privilege,
   Count(*) [PrivilegeCount] 
from #posts
where body like @url_for_this_db
and (body like '%http:%' 
or body like '%https:%')
group by repcategory

fork query

edited jan 15

created jan 15 rene