Q&A about the site for developers and users of the secure, private and untraceable cryptocurrency Monero
/* Result columns (accounts are on target sites): TotalMigrated: Total count of posts migrated. HaveAccount: Migrated posts where OP currently has an account. HadAccount: Migrated posts where OP had an account already before migration. AccountAfterPost: Where OP created an account after the post/migration was made. AccountAfterMigrate: Where OP created an account after the migration was made. NoAccount: Where OP never created an account. NoAccountOpen: Where OP never created an account, and question is still open. NoAccountPostActivity: Where OP never created an account, and question got a comment or an answer after the migration. NoAccountPostActivityOpen: Where OP never created an account, and question is still open and got a comment or an answer after the migration. */ DECLARE @db sysname DECLARE @query nvarchar(max) DECLARE c CURSOR FOR SELECT Name FROM sys.databases WHERE database_id > 5 CREATE TABLE #Results (Site sysname, TotalMigrated int, HaveAccount int, HadAccount int, AccountAfterPost int, AccountAfterMigrate int, NoAccount int, NoAccountOpen int, NoAccountPostActivity int, NoAccountPostActivityOpen int) OPEN c FETCH NEXT FROM c INTO @db WHILE (@@FETCH_STATUS = 0) BEGIN SET @query = ' USE ' + QUOTENAME(@db) + '; WITH Activity AS ( SELECT DISTINCT h.PostId FROM PostHistory h INNER JOIN ( SELECT PostId, CreationDate FROM Comments UNION ALL SELECT ParentId PostId, CreationDate FROM Posts ) a ON a.PostId = h.PostId WHERE h.PostHistoryTypeId = 36 AND a.CreationDate >= h.CreationDate ) INSERT INTO #Results SELECT DB_NAME() Site, COUNT(*) TotalMigrated, COUNT(p.OwnerUserId) HaveAccount, COALESCE(SUM(IIF(u.CreationDate < p.CreationDate, 1, 0)), 0) HadAccount, COALESCE(SUM(IIF(u.CreationDate > p.CreationDate, 1, 0)), 0) AccountAfterPost, COALESCE(SUM(IIF(u.CreationDate > h.CreationDate, 1, 0)), 0) AccountAfterMigrate, COALESCE(SUM(IIF(p.OwnerUserId IS NULL, 1, 0)), 0) NoAccount, COALESCE(SUM(IIF(p.OwnerUserId IS NULL AND p.ClosedDate IS NULL, 1, 0)), 0) NoAccount, COALESCE(SUM(IIF(p.OwnerUserId IS NULL AND a.PostId IS NOT NULL, 1, 0)), 0) NoAccountPostActivity, COALESCE(SUM(IIF(p.OwnerUserId IS NULL AND p.ClosedDate IS NULL AND a.PostId IS NOT NULL, 1, 0)), 0) NoAccountPostActivityOpen FROM PostHistory h INNER JOIN Posts p ON p.Id = h.PostId LEFT OUTER JOIN Users u ON u.Id = p.OwnerUserId LEFT OUTER JOIN Activity a ON a.PostId = h.PostId WHERE h.PostHistoryTypeId = 36 AND p.PostTypeId = 1 ' EXEC(@Query) FETCH NEXT FROM c INTO @db END; CLOSE c; DEALLOCATE c; SELECT * FROM #Results UNION ALL SELECT 'TOTAL', SUM(TotalMigrated), SUM(HaveAccount), SUM(HadAccount), SUM(AccountAfterPost), SUM(AccountAfterMigrate), SUM(NoAccount), SUM(NoAccountOpen), SUM(NoAccountPostActivity), SUM(NoAccountPostActivityOpen) FROM #Results ORDER BY TotalMigrated DESC, NoAccount DESC, Site ASC