0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for professional and enthusiast programmers

declare @tagToSearch nvarchar(35) = '<' + ##tag:string## + '>';
declare @textToSearch nvarchar(max) = lower(##text:string##);
declare @include bit = iif(##includeText:string?yes## = 'yes', 1, 0);
declare @withTag bit = iif(##includeTag:string?yes## = 'yes', 1, 0);
declare @totalPosted bigint;

set @totalPosted = (
    select 
      count(Id) 
    from 
      Posts 
    where 
      PostTypeId = 1 and 
      charindex(@tagToSearch, Tags) > 0
  );

select
  count(Id) as [Contain Text],
  @totalPosted as [Total Posted],
  count(Id) * 100 / @totalPosted as Percentage
from
  Posts
where
  PostTypeId = 1
  and
  (
    @withTag = 1 and charindex(@tagToSearch, Tags) > 0
    or
    @withTag = 0 and charindex(@tagToSearch, Tags) = 0
  )
  and
  (
    @include = 1 and charindex(@textToSearch, lower(Body)) > 0
    or
    @include = 0 and charindex(@textToSearch, lower(Body)) = 0
  )
  and
  DeletionDate is null
  and
  ClosedDate is null
  and
  Score >= 0

fork query

edited jul 24 20

created jul 19 20 0Valt