1

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A for people interested in the history and origins of science and mathematics

-- migration prefix length
DECLARE @mhpl INT, @mapl INT, @mhid INT, @maid INT;

SELECT @mhid = Id from PostHistoryTypes where Name = N'Post Migrated Here'
SELECT @maid = Id from PostHistoryTypes where Name = N'Post Migrated Away'

-- Create copy of PostHistory table with
-- all the posts migrated here from other sites
SELECT * INTO #ph FROM posthistory WHERE PostHistoryTypeId in (@mhid, @maid)

-- Add "migration direction" column 
ALTER TABLE #ph ADD mDir CHAR(1) NULL;
UPDATE #ph SET mDir = '<' WHERE PostHistoryTypeId=@mhid
UPDATE #ph SET mDir = '>' WHERE PostHistoryTypeId=@maid

-- Add column for site where question was migrated from
ALTER TABLE #ph ADD mSite varchar(255) NULL;

-- Parse migration source site from comment field
-- and put into column
SET @mhpl = LEN(N'from http://')
SET @mapl = LEN(N'to http://'  )
UPDATE #ph SET mSite = 
  replace( 
    CASE WHEN mDir = N'<' THEN 
      substring(
                 COMMENT, @mhpl, 
                 charindex('.',COMMENT,@mhpl) - @mhpl
               )
    ELSE 
      substring(
                 COMMENT, @mapl, 
                 charindex('.',COMMENT,@mapl) - @mapl
               )
    END,
    N'/', ''
  )


-- Add calendar quarter column column for site where question was migrated from
ALTER TABLE #ph ADD CalendarQuarter CHAR( 7 ) NULL; -- LEN(N'FYXXQYY')
UPDATE #ph SET CalendarQuarter = N'FY' + 
               RIGHT(
                      CAST( YEAR(CreationDate) AS CHAR(4) ), 
                      2 
               ) +
               N'Q' + 
               CAST( CEILING(
                             CAST(
                                  MONTH(CreationDate) AS DECIMAL(4,2)
                                  ) / 3 
                             ) AS char(1)
               )
            
SELECT 
  CalendarQuarter, 
  SUM(CASE WHEN mDir='>' THEN 1 ELSE  0 END) as Departing,
  SUM(CASE WHEN mDir='<' THEN 1 ELSE  0 END) as Arriving,
  SUM(CASE WHEN mDir='>' THEN 1 ELSE -1 END) as NetDepartures
FROM #ph
WHERE mSite='ell'
GROUP BY CalendarQuarter
ORDER BY CalendarQuarter DESC

fork query

edited oct 24 18 Dan.Bron26186

created oct 24 18 anonymous