3

Please login or register to vote for this query.

(click on this box to dismiss)

The last full day that the specified user has no record of activity in any of: Posts, Comments, Votes, or PostHistory. (aka consecutive days in a row)

Q&A about the site for Drupal developers and administrators

select owneruserid as uid, cast(creationdate as date) as dt
  into #temp from posts where owneruserid in (##UserID##)
insert into #temp select userid as uid, cast(creationdate as date) as dt
  from posthistory where userid in (##UserID##)
insert into #temp select userid as uid, cast(creationdate as date) as dt
  from comments where userid in (##UserID##)
insert into #temp select userid as uid, cast(creationdate as date) as dt
  from votes where userid in (##UserID##)
select distinct dt, uid into #userdate from #temp 

create table #calendar ([dt] date)
declare @StartDate date, @EndDate as date
set @StartDate = dateadd(YY, -2, getdate() ) --2 years back
set @EndDate = (select cast(max(creationdate) as date) from posts)
while @StartDate <= @EndDate
  begin
    insert into #Calendar(dt) select @StartDate
    set @StartDate = dateadd(dd, 1, @StartDate)
  end

select c.dt, u.uid as [UserID]
into #activity
from #userdate u right join #Calendar c 
on u.dt = c.dt
order by c.dt

select 
  ##UserID## as [User Link], 
  format(max(dt),'ddd MMMM d, yyyy') as [Last Day Missed],
  cast(datediff(d,max(dt),getdate())-1 as varchar)+' days' as [# Consecutive Days]
from #activity 
where [UserID] is null 


--UserID:Enter a numeric User ID to query:

fork query

edited jul 26 18

created jun 6 18 ashleedawg