0

Please login or register to vote for this query.

(click on this box to dismiss)

assumed first names from displayname of users active this year and other criteria, setup to extract by page number for export. It's a frikkin mess, don't look. lol Experimenting with PHP "Gender" Extension (https://www.php.net/manual/en/class.gender.php) ... needed some sample data to play with. :-) Total output was about 20 pages (1 million names)

Q&A about the site for developers and end users of the Sitecore CMS and multichannel marketing software

declare @pagenum int = 1;

select 
case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end as firstname,
  count(*) cnt,
  avg(1.0*reputation) rep,
  avg(1.0*upvotes) upv,
  avg(1.0*downvotes) downv
  into #temp
from users
where year(LastAccessDate)=year(getdate())
group by case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end
having charindex('.',case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end)=0 and len(case when CharIndex( ' ', displayname )=0 then lower(displayname) else
  Substring( lower(displayname), 1, CharIndex( ' ', displayname ) - 1)
  end)>1
order by cnt desc

select ROW_NUMBER()OVER( order by cnt desc,firstname) num,* into #out from #temp

select * from #out
where num between (@pagenum-1)*50000+1 and @pagenum*50000
order by cnt desc , firstname

fork query

edited nov 17 anonymous

created aug 5 19 ashleedawg