0

Please login or register to vote for this query.

(click on this box to dismiss)

Shows all my suggested edits that were rejected by the reviewers.

Q&A for professional and enthusiast programmers

-- My accepted/rejected/pending edits
-- Shows all my suggested edits
-- Returns all non-Meta StackExchange sites
-- Username searches are case-sensitive

 EXEC sp_MSforeachdb @precommand='CREATE TABLE ##fooj (Site nvarchar(150), [Site UserID] nvarchar(75), [Suggested Edit Link]  nvarchar(255), [Accepted?]  nvarchar(10), [Post Link]  nvarchar(75), comment  Text, [Creation Date]  DateTime, [Decision Date] DateTime)', 
 @postcommand='BEGIN 
 SELECT * from ##fooj order by [Creation Date] DESC
 DROP TABLE ##fooj
 END', 
 @command1='USE [?] BEGIN 
IF (DB_Name() NOT IN (''master'', ''tempdb'', ''model'', ''msdb'') and DB_Name() NOT LIKE ''Meta.%'' and DB_Name() NOT LIKE ''%.Meta'') BEGIN 
With sn as (SELECT
PARSENAME(DB_Name(),1)+''.''+COALESCE(PARSENAME(DB_Name(),2),'''')+''.''+COALESCE(PARSENAME(DB_Name(),3),'''')+''.''+COALESCE(PARSENAME(DB_Name(),4),'''') aS SiteName
), sitesnames as (SELECT
LEFT(SiteName,LEN(SiteName)+1-patindex(''%[A-Za-z]%'', reverse(SiteName)) ) as SiteName
from sn
), sites as (SELECT
SiteName,
''https://''+SiteName+''.com/'' as SiteURL,
''https://''+SiteName+''.com/users/'' as UserBase,
''https://''+SiteName+''.com/a/'' as PostBase,
''https://''+SiteName+''.com/suggested-edits/'' as SugBase
from sitesnames
)
INSERT INTO ##fooj
SELECT ''''+sites.SiteURL+''|''+sites.SiteName, --''''https://''+DB_Name()+''.com |''+DB_Name()+'''',
''''+sites.UserBase+CONVERT(NVARCHAR(250),e.OwnerUserID)+''|''+CONVERT(NVARCHAR(250),e.OwnerUserID)+'''', 
       ''''+sites.SugBase+CONVERT(NVARCHAR(250),e.id)+''|Suggestion #''+CONVERT(NVARCHAR(250),e.id)+'''' [Suggested Edit Link],
       CASE WhEN ApprovalDate>0 THEN ''Accepted'' WHEn RejectionDate>0 THEN ''Rejected'' ELSE ''Pending'' END as [Accept | Reject],
       ''''+sites.PostBase+CONVERT(NVARCHAR(250),e.PostId)+''|''+CONVERT(NVARCHAR(250),e.PostId)+'''' as [Post Link], 
       e.Comment, 
       e.CreationDate, 
       COALESCE(RejectionDate,e.ApprovalDate) as DecisionDate
       from sites CROSS JOIN ([?]..SuggestedEdits as e join [?]..Users as U on e.OwnerUserID = U.ID)
WHErE U.DisplayName=''##DisplayName##''
--order by COALESCE(e.RejectionDate,e.ApprovalDate) desc 
END END'

fork query

edited nov 3 17 mpag

created aug 31 11 Idolon