1

Please login or register to vote for this query.

(click on this box to dismiss)

Pick out the older posts that people seem to be interested in, filtered by tags.

Q&A for committed Christians, experts in Christianity and those interested in learning more

--2019-07-30 Gregor y
declare @StartDate  date = getdate()-##DaysAgo:int?14##;
declare @LowerBound date = dateadd(year, ##OceanClamp:int?-2##, @StartDate);

with TheOcean as (
select
    Id as [Post Link]
   ,CreationDate
   ,Score
   ,ViewCount
   ,OwnerUserId as [User Link]
   ,Tags
   ,AnswerCount
   ,CommentCount
   ,FavoriteCount
   ,ClosedDate
   ,rank() over(partition by floor(cast(CreationDate as float)) order by Score desc, FavoriteCount desc, ViewCount desc) r
from Posts
where 
   CreationDate    between @LowerBound and @StartDate
   and PostTypeId   = 1 --PostTypes.Id=1,PostTypes.Name='Question'
   and Score       >= 0
   and AnswerCount  = 0
   and CommentCount = 0
--   and ClosedDate is null
--   and DeletionDate is null
--   and CommunityOwnedDate is null
   and Tags like ('%'+ ##Tag1:string?<## + '%')
   and Tags like ('%'+ ##Tag2:string?<## + '%')
   and Tags like ('%'+ ##Tag3:string?<## + '%')
   and Tags like ('%'+ ##Tag4:string?<## + '%')
   and Tags like ('%'+ ##Tag5:string?<## + '%')
)select top ##MaxRecs:int?500## * from TheOcean where r < ##RankClamp:int?5##
order by Score desc, floor(cast(CreationDate as float)) desc, ViewCount desc
;

fork query

edited jul 31 19

created jul 31 19 anonymous