3

Please login or register to vote for this query.

(click on this box to dismiss)

Identify at a glance which days you were active, and which days you missed, in Calendar format, starting on the date you became a user. Kind of a messy query, and just for fun (not official). Does not include rep for posts you downvote, nor moderator adjustments, but is "mostly accurate", to give a general impression of your overall activity, and X's on days missed. I'm a geek for data... I play around with numbers recreationally... I have some of my other favourites bookmarked here: https://data.stackexchange.com/users/27246/ashleedawg?order_by=favorite No promises to accuracy for anything!

Q&A for ebook publishers and readers

declare @CellWidth int = 15 --column width not including separating lines (Avail: --width:x=150, height:y=34)


declare @ShowWeeksWithoutData bit = 'false'
declare @ReverseCalendar bit = 'true'       --show most recent dates first?
set nocount on
set ansi_warnings off

--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=--=-=--=-=-=-=-=-=-=-=-
declare @StartDate date = (select creationdate from users where id=##UserID##) --Start: User acct creation date 
set @StartDate = dateadd(dd,0-datepart(weekday,@startdate)+1,@startdate)
declare @EndDate date = (select min(create_date) from sys.tables) --End: day before last day found in SEDE 
set @EndDate = dateadd(dd,0-datepart(weekday,@enddate), @enddate) 
declare @numWeeks int = datediff(wk,@startdate,@EndDate)+1
declare @monthYear nvarchar(30), @dt date, @cWkdy int, @daysWithData tinyint

declare @userName nvarchar(50) = (select displayname from users where id=##UserID##)
declare @strTitle nvarchar(200) = replicate(N' ',10)+N'Activity for '+@UserName+N' on '+DB_NAME()
        + N' (with Rep and Questions/Answers/Edits/Comments)' + char(10) +
        + N' +xx = Rep Earned, Q=Questions, A=Answers, C=Comments, E=Edits, ♥=Badge(s) Earned, ▲=One of your best days'
print @strTitle print ''

set @dt  = @StartDate  --Generate temp calendar table
create table #calendar ([dt] date)
while @dt<=@EndDate    
  begin
    insert into #Calendar(dt) select @dt
    set @dt = dateadd(dd, 1, @dt)
  end

select cast(creationdate as date) as dt,                                 --- Posts
       sum(case when posttypeid=1 then 1 else 0 end) as [Questions],
       sum(case when posttypeid=2 then 1 else 0 end) as [Answers]
into #Posts from Posts
where owneruserid=##UserID## and posttypeid in (1,2) group by cast(creationdate as date)

select cast(creationdate as date) as dt, count(*) as [Comments]          ---Comments
into #Comments from Comments where userid=##UserID## group by cast(creationdate as date)

select cast(creationdate as date) as dt, count(distinct PostID) as Edits ---Edits
into #Edits from PostHistory where userid=##UserID## group by cast(creationdate as date)

select distinct cast([date] as date) as badgeDate                        ---Badge Dates
into #badgeDates from badges where userid=##UserID##

--=-=-=-=-=-=-=-=-=-=-=-=-==-=-=-           --<-- CALCULATE REP BY DAY --<--
select v.creationdate as vDate,
  sum(case 
    when v.votetypeid=2 then
      case when p.posttypeid=1 then 5                  --Upvote Question
           when p.posttypeid=2 then 10 end             --Upvote Answer
    when v.votetypeid=3 then -2                        --Downvote Q or A
  end ) as [rep],
  sum(case 
    when v.votetypeid=1 then
      case when p.posttypeid=2 then 15 end             --Accepted Answer
  end ) as [accepted]
into #rep1 from posts p left outer join votes v on v.postid = p.id 
where p.owneruserid=##UserID##                         --Q&A Accepted/Upvote/Downvote only 
  and v.votetypeid in (1,2,3) and p.posttypeid in (1,2)                            
group by p.owneruserid,v.creationdate

select v.creationdate as vDate,                        --Calculate rep from Bounty in/out
  sum(case when votetypeid=8 then 0-v.bountyamount else v.bountyamount end) as bounty
into #rep2 from votes v right join posts p on v.postid =p.id
where votetypeid in (8,9) and owneruserid = ##UserID## --8=bounty received, 9=bounty paid
group by v.creationdate

select 
  format(dt,'yyyy-MM-dd') as vDate,                    --apply 200/day cap to rep by day
  --rep as [Rep from Votes],
  --accepted as [Rep from Accepted],
  --bounty as [Rep from Bounty],
  case when isnull(rep,0)>200 then 200 else isnull(rep,0) end + isnull(accepted,0) + isnull(bounty,0) as [Total Rep Gain],
  case when isnull(rep,0)>200 then rep-200 else 0 end as [Rep over cap]
into #rep from #Calendar cal left join #rep1 r1 on cal.dt=r1.vdate left join #rep2 r2 on cal.dt=r2.vdate
order by dt desc

select top 10 percent                                  --GET TOP 10% TO ▲ sTaR THE USER'S BEST DAYS
   convert(date,vdate) as Date,                        --               │  
   [Total Rep Gain]+[Rep over cap] as AllRep
into #top10percent from #rep
order by [Total Rep Gain]+[Rep over cap] desc

select [vDate] as [dt], sum([Total Rep Gain]) as [Rep]  --SUMMARIZE REP INTO #dailyRep
into #dailyRep from #rep group by vdate order by vdate
--=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=

select
  format(cal.dt,'yyyy-MM-dd') as [Date],   ---SUMMARIZE DATA into #Summary
  Questions, Answers, Comments, Edits,  Rep
into #Summary from #Calendar cal 
  left join #Posts    p on cal.dt=p.dt
  left join #Comments c on cal.dt=c.dt
  left join #Edits    e on cal.dt=e.dt
  left join #dailyRep r on cal.dt=r.dt
order by cal.dt

--variables for grid
set datefirst 7 --weeks starts on Sunday
declare @y int, @x int, @badge smallint, @strCell nvarchar(250), @prevMonth int 
declare @strWeek1 nvarchar(250)=N'', @strWeek2 nvarchar(250)=N'', 
        @strWeek3 nvarchar(250)=N'', @strWeek4 nvarchar(250)=N''
declare @rep int
set @dt = @startdate    
declare @stats1 nvarchar(250)=N'',@stats2 nvarchar(250)=N'', @stRep nvarchar(10)=N''
declare @padchar nvarchar(2), @stats nvarchar(250),  @yrPrnt nvarchar(10) = N'', @prevYear int = 1

--Weekday headings (the "long" way as opposed to hard coded, so column widths can be variable)
set @cWkdy = 0
while @cWkdy < 7
  begin
      set @strCell = format(dateadd(d,@cWkdy,@dt),'dddd') 
      set @strCell = N' '+replicate(N' ',((@CellWidth-len(@strCell))/2)-1)+@strCell --padding to center:Left
      set @strCell = @strCell+replicate(N' ',@CellWidth-len(@strCell)+1) --padding to center:Right
      set @strWeek1 = @strWeek1 + @strCell 
    set @cWkdy = @cWkdy + 1 
  end
print N'  ' + @strWeek1
print N' ╔'+replicate(replicate(N'═' ,@cellWidth)+N'╦',6)+replicate(N'═' ,@cellWidth)+N'╗' --Top Border
set @strCell = N''
set @padChar = N' ' 

create table #weeks ( week tinyint, line tinyint, daysWithData tinyint, txt nvarchar(250) ) 

set @strWeek1 = N'' 
set @y = 1  --@y = the week number
while @y <= @numWeeks
  begin
    set @x = 1 --@x = the weekday
    set @monthYear = ''
    set @daysWithData = 0
    while @x <= 7                 
     begin
-----------------------------------
      set @stats1=(select case when questions>0 then N'Q:'+cast(questions as nvarchar(20))+N'  ' else '' end 
                      +  case when answers>0 then    N'A:'+cast(answers   as nvarchar(20))+N'  ' else '' end 
                                      as [Stats] from #summary where [Date]=@dt)

      set @stats2=(select case when comments>0 then N'C:'+cast(comments as nvarchar(20))+N'  ' else '' end 
                      +  case when edits>0 then     N'E:'+cast(edits    as nvarchar(20))+N'  ' else '' end 
                                      as [Stats] from #summary where [Date]=@dt)

      set @badge = (select count(*) from #badgeDates where badgeDate=@dt) --check if there's a badge today
      if len(@stats1+@stats2)>0 set @daysWithData = @daysWithData + 1 --count days with data (to ignore weeks without)  ??????????/
      set @rep = (select rep from #summary where [Date]=@dt)
      set @strep = case when @rep <>0 then format(@rep,N'+0;-0') else N'' end
      
      --add top "10% rep day" symbol ▲ if needed
      declare @topDay nvarchar(20) = case when ( select count(*) from #top10percent where [date]=@dt ) = 0
                                          then N' ' else N' ▲' end
      set @stRep = left(@stRep + @topDay + N'      ' ,6)  --@stRep is always len=6 (max~+200/day)

-- Prep 4-row output for one week       
    set @strCell = N' '+format(@dt,'MMM')                                                  --*ROW1* of week
	set @strCell = @stRep + replicate(@padChar,@CellWidth-len(@strCell)-6)+@strCell --pad 
	set @strWeek1 = @strWeek1 + @strCell + N'║'  

    set @strCell = right(N'   '+cast(day(@dt) as nvarchar(20)),4)                          --*ROW2*
	set @strCell = replicate(@padChar,@CellWidth-len(@strCell))+@strCell --pad
	set @strWeek2 = @strWeek2 + @strCell + N'║' 

    if len(@stats1+@stats2)=0 set @strcell = N' \/ ' else set @strcell = @stats1           --*ROW3*
	set @strCell = left(@strCell+replicate(@padChar,@CellWidth),@CellWidth) --padd
	set @strWeek3 = @strWeek3 + @strCell + N'║' 

    if len(@stats1+@stats2)=0 set @strcell= N' /\ ' else set @strcell = @stats2            --*ROW4*
	set @strCell = left(@strCell+replicate(@padChar,@CellWidth-2),@CellWidth-2) --pad
	  + case when @badge=0 then @padChar+@padChar else N'♥ ' end                   --add badge symbol (or blank)
	set @strWeek4 = @strWeek4 + @strCell + N'║' 
	  
	  
	if @x = 7 
	begin 

      if @ReverseCalendar = 'true'  
        --begin
		  if day(@dt)=day(eomonth(@dt))  
	  	    set @monthYear = format(dateadd(d,-1,@dt),' MMMM yyyy') --label week that has the last day of the month
		    else if day(@dt)=1  set @monthYear = format(@dt,' MMMM yyyy') --label week that has the first day of the month

			--end    --<<<<<<<<<<<<?????
          
          insert into #weeks  (week, line, daysWithData, txt ) values
           (@y,1, @daysWithData, N' ║'+@strWeek1), 
           (@y,2, @daysWithData, N' ║'+@strWeek2), 
           (@y,3, @daysWithData, N' ║'+@strWeek3),     
           (@y,4, @daysWithData, N' ║'+@strWeek4)
          
          set @strweek1 = N'' set @strweek2 = N''   set @strweek3 = N''  set @strweek4 = N''

        end
      set @dt=dateadd( d, 1, @dt ) --increment counters

      set @x=@x+1
     end
    set @y=@y+1
  end

declare @fWeek tinyint, @fLine tinyint, @firstRow bit = 'true'
declare @fTxt1 nvarchar(250), @fTxt2 nvarchar(250), @fTxt3 nvarchar(250), @fTxt4 nvarchar(250)

--use cursor to parse temp table, so that output can be newest first
declare contact_cursor cursor for  
  select week, line, daysWithData, txt from #weeks
  order by week desc, line
    --case when @ReverseCalendar='true' then week desc else week asc end,  --vary depending on user prompt<<<<<<<<<<<
    --https://stackoverflow.com/questions/18207954/using-variables-for-asc-and-desc-in-order-by
open contact_cursor;  

while @@fetch_status = 0
  begin
    fetch next from contact_cursor into @fWeek, @fLine, @daysWithData, @fTxt1
    fetch next from contact_cursor into @fWeek, @fLine, @daysWithData, @fTxt2
    fetch next from contact_cursor into @fWeek, @fLine, @daysWithData, @fTxt3
    fetch next from contact_cursor into @fWeek, @fLine, @daysWithData, @fTxt4
    
    if not ( @daysWithData = 0 and @ShowWeeksWithoutData = 0 )
      begin
        if @firstRow = 'false'
          if @@fetch_status = 0  --separating border line
            print N' ╠'+replicate(replicate(N'═',@CellWidth)+N'╬',6)+replicate(N'═',@CellWidth)+N'╣' 
        set @firstrow = 'false'

        print @fTxt1 
        print @fTxt2 
        print @fTxt3 
        print @fTxt4
      end
  end
close contact_cursor
deallocate contact_cursor 
print N' ╚'+replicate(replicate(N'═',@CellWidth)+N'╩',6)+replicate(N'═',@CellWidth)+N'╝'  --bottom border

print ''
declare @SEDEupdated datetime = (select min(create_date) from sys.tables)
print '      SEDE Data Updated '+format( @SEDEupdated,'ddd MMM d HH:mm')+', next update in about '+
  format((7*24)-datediff(HH, @SEDEupdated,getdate()),'0')+' hours'  
    
    
--UserID: Enter a numeric User ID:"You can find your user id within the URL for your user profile."

fork query

edited aug 14 18

created jul 28 18 ashleedawg