0

Please login or register to vote for this query.

(click on this box to dismiss)

Q&A about the site for researchers and developers who explore the principles of a system through analysis of its structure, function, and operation

--  Chunk Start
  --      The date to start at
  --      input   :   date in yyyymmdd format
  --      default :   today's date
  --
  --  Chunk Type
  --      The time unit to chunk by
  --      input   :   1.  Days
  --                  2.  Months
  --                  3.  Years
  --      default :   1
  --
  --  Chunk Size
  --      The size of each chunk
  --      input   :   Group n @chunkType into one chunk
  --      default :   1
  --
  --  Chunk Amount Type
  --      The time unit to determine the size of
  --      input   :   0.  Same as @chunkType
  --                  1.  Days
  --                  2.  Months
  --                  3.  Years
  --      default :   0
  --
  --  Chunk Amount Size
  --      The amount of chunks to take in each chunk group
  --      input   :   Group n @chunkAmountType into a chunk group
  --      default :   100
  --
  --  Chunk Skip Type
  --      The time unit to determine the size of
  --      input   :   0.  Same as @chunkAmountType
  --                  1.  Days
  --                  2.  Months
  --                  3.  Years
  --      default :   0
  --
  --  Chunk Skip
  --      The amount of chunks to skip between chunk groups
  --      input   :   Skip n @chunkSkipType between the first of each chunk group
  --      default :   12
  --
  --  Chunk Groups
  --      The amount of chunk groups
  --      input   :   The amount of chunk groups desired
  --      default :   0
  --
  --  Date Normalize
  --      The normalized graph view of the data
  --      input   :   2.  Month view
  --                  3.  Year view
  --                  4.  Infinate view
  --      default :   4
  --
  --  Good Values
  --      Quarterly (days)
  --          ChunkStart      20210400
  --          ChunkType       1
  --          ChunkAmountType 2
  --          ChunkSkipType   0
  --          ChunkSize       1
  --          ChunkAmountSize 6
  --          ChunkSkip       12
  --          ChunkGroups     2
  --
  --      Quarterly (months)
  --          ChunkStart      20210100
  --          ChunkSize       7
  --          ChunkAmountSize 3
  --          ChunkSkip       12
  --          ChunkGroups     5
  --          ChunkType       1
  --          ChunkAmountType 2
  --          ChunkSkipType   0
  --          dateNormalize   3
  --
  --      Continuous (days)
  --          ChunkStart      20210400
  --          ChunkType       1
  --          ChunkAmountType 0
  --          ChunkSkipType   0
  --          ChunkSize       1
  --          ChunkAmountSize 100
  --          ChunkSkip       12
  --          ChunkGroups     1
  --
  --      Continuous (months)
  --          ChunkStart      20210400
  --          ChunkType       2
  --          ChunkAmountType 0
  --          ChunkSkipType   0
  --          ChunkSize       1
  --          ChunkAmountSize 100
  --          ChunkSkip       12
  --          ChunkGroups     1
  --
  --      Test date-normalize 3 leap years
  --          ChunkStart      20210100
  --          ChunkSize       7
  --          ChunkAmountSize 6
  --          ChunkSkip       15
  --          ChunkGroups     5
  --          ChunkType       1
  --          ChunkAmountType 2
  --          ChunkSkipType   0
  --          dateNormalize   3

  ------------
  --  Input --
  ------------

  
  DECLARE @_chunkStart AS int = ##ChunkStart:int?0##;
  DECLARE @chunkStart AS date  = DateAdd(
    day,
    1,
    CASE
      WHEN @_chunkStart = 0 THEN Convert(date, CURRENT_TIMESTAMP)
      ELSE CASE
    WHEN @_chunkStart % 100 = 0 THEN DateAdd(day, -1, cast(cast(@_chunkStart + 1 AS varchar(255)) AS date))
    ELSE cast(cast(@_chunkStart AS varchar(255)) AS date)
  END
    END
  );
  DECLARE @chunkSize AS int   = ##ChunkSize:int?1##;
  DECLARE @chunkAmountSize AS int = ##ChunkAmountSize:int?100##;
  
  DECLARE @_chunkSkip AS int = ##ChunkSkip:int?0##;
  DECLARE @chunkSkip AS int     = CASE
    WHEN @_chunkSkip = 0 THEN @chunkAmountSize
    ELSE @_chunkSkip
  END;
  DECLARE @chunkGroups AS int = ##ChunkGroups:int?1##;

  DECLARE @chunkType AS int = ##ChunkType:int?1##;
  
  DECLARE @_chunkAmountType AS int = ##ChunkAmountType:int?0##;
  DECLARE @chunkAmountType AS int     = CASE
    WHEN @_chunkAmountType = 0 THEN @chunkType
    ELSE @_chunkAmountType
  END;
  
  DECLARE @_chunkSkipType AS int = ##ChunkSkipType:int?0##;
  DECLARE @chunkSkipType AS int     = CASE
    WHEN @_chunkSkipType = 0 THEN @chunkAmountType
    ELSE @_chunkSkipType
  END;
  DECLARE @dateNormalize AS int = ##DateNormalize:int?4##;

  DECLARE @leftHandSide AS nvarchar(max) = '##LeftHandSide?Q##';
  DECLARE @rightHandSide AS nvarchar(max) = '##RightHandSide##';


  --------------------
  --  Input Sources --
  --------------------

  
  SELECT
    ROW_NUMBER() OVER (ORDER BY Id Asc) - 1 AS Id
  INTO #ChunkNumbers
  FROM Posts
  ;
  
  
  
  WITH
  _ChunksEnd AS (
    SELECT
      n.Id AS Id,
      CASE
    WHEN @chunkSkipType = 1 THEN DateAdd(day,   -n.Id * @chunkSkip, @chunkStart)
    WHEN @chunkSkipType = 2 THEN DateAdd(month, -n.Id * @chunkSkip, @chunkStart)
    WHEN @chunkSkipType = 3 THEN DateAdd(year,  -n.Id * @chunkSkip, @chunkStart)
    ELSE Null
  END AS ChunkEnd
     FROM #ChunkNumbers AS n
    WHERE n.Id < @chunkGroups
  ),
  _ChunksStart AS (
    SELECT
      c.Id,
      CASE
    WHEN @chunkAmountType = 1 THEN DateAdd(day,   -@chunkAmountSize, c.ChunkEnd)
    WHEN @chunkAmountType = 2 THEN DateAdd(month, -@chunkAmountSize, c.ChunkEnd)
    WHEN @chunkAmountType = 3 THEN DateAdd(year,  -@chunkAmountSize, c.ChunkEnd)
    ELSE Null
  END AS ChunkStart,
      c.ChunkEnd,
      DateAdd(day, -1, c.ChunkEnd) AS _ChunkEnd
    FROM _ChunksEnd AS c
  ),
  _ChunksGroups AS (
    SELECT
      c.Id,
      CASE
      WHEN year(c.ChunkStart) != year(c._ChunkEnd) THEN CASE
        WHEN
          day(c.ChunkStart) = day(c._ChunkEnd)
          AND month(c.ChunkStart) = month(c._ChunkEnd)
        THEN 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END
   + ', ' + 
  
    cast(year(c.ChunkStart) AS varchar)
   + ' - ' + 
  
    cast(year(c.ChunkStart) AS varchar)
  
        ELSE 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar)
   + ' - ' + 
  
    cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c._ChunkEnd) = 1 THEN 'Jan' WHEN month(c._ChunkEnd) = 2 THEN 'Feb' WHEN month(c._ChunkEnd) = 3 THEN 'Mar' WHEN month(c._ChunkEnd) = 4 THEN 'Apr' WHEN month(c._ChunkEnd) = 5 THEN 'May' WHEN month(c._ChunkEnd) = 6 THEN 'Jun' WHEN month(c._ChunkEnd) = 7 THEN 'Jul' WHEN month(c._ChunkEnd) = 8 THEN 'Aug' WHEN month(c._ChunkEnd) = 9 THEN 'Sep' WHEN month(c._ChunkEnd) = 10 THEN 'Oct' WHEN month(c._ChunkEnd) = 11 THEN 'Nov' WHEN month(c._ChunkEnd) = 12 THEN 'Dec' END + ' ' + cast(year(c._ChunkEnd) AS varchar)
  
      END
      WHEN month(c.ChunkStart) != month(c._ChunkEnd) THEN 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END
   + ' - ' + 
  
    cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c._ChunkEnd) = 1 THEN 'Jan' WHEN month(c._ChunkEnd) = 2 THEN 'Feb' WHEN month(c._ChunkEnd) = 3 THEN 'Mar' WHEN month(c._ChunkEnd) = 4 THEN 'Apr' WHEN month(c._ChunkEnd) = 5 THEN 'May' WHEN month(c._ChunkEnd) = 6 THEN 'Jun' WHEN month(c._ChunkEnd) = 7 THEN 'Jul' WHEN month(c._ChunkEnd) = 8 THEN 'Aug' WHEN month(c._ChunkEnd) = 9 THEN 'Sep' WHEN month(c._ChunkEnd) = 10 THEN 'Oct' WHEN month(c._ChunkEnd) = 11 THEN 'Nov' WHEN month(c._ChunkEnd) = 12 THEN 'Dec' END
   + ', ' + 
  
    cast(year(c.ChunkStart) AS varchar)
  
      WHEN day(c.ChunkStart) != day(c._ChunkEnd) THEN 
  
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END
   + ' to ' + 
  
    cast(day(c._ChunkEnd) AS varchar) + CASE WHEN day(c._ChunkEnd) % 10 = 1 THEN 'st' WHEN day(c._ChunkEnd) % 10 = 2 THEN 'nd' WHEN day(c._ChunkEnd) % 10 = 3 THEN 'rd' ELSE 'th' END
   + ' ' + 
  
    CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar)
  
      ELSE 
  
    cast(day(c.ChunkStart) AS varchar) + CASE WHEN day(c.ChunkStart) % 10 = 1 THEN 'st' WHEN day(c.ChunkStart) % 10 = 2 THEN 'nd' WHEN day(c.ChunkStart) % 10 = 3 THEN 'rd' ELSE 'th' END + ' ' + CASE WHEN month(c.ChunkStart) = 1 THEN 'Jan' WHEN month(c.ChunkStart) = 2 THEN 'Feb' WHEN month(c.ChunkStart) = 3 THEN 'Mar' WHEN month(c.ChunkStart) = 4 THEN 'Apr' WHEN month(c.ChunkStart) = 5 THEN 'May' WHEN month(c.ChunkStart) = 6 THEN 'Jun' WHEN month(c.ChunkStart) = 7 THEN 'Jul' WHEN month(c.ChunkStart) = 8 THEN 'Aug' WHEN month(c.ChunkStart) = 9 THEN 'Sep' WHEN month(c.ChunkStart) = 10 THEN 'Oct' WHEN month(c.ChunkStart) = 11 THEN 'Nov' WHEN month(c.ChunkStart) = 12 THEN 'Dec' END + ' ' + cast(year(c.ChunkStart) AS varchar)
  
    END AS ChunkName,
      c.ChunkStart,
      c.ChunkEnd,
      CAST(CASE
    WHEN @chunkType = 1 THEN DateDiff(day,   c.ChunkStart, c.ChunkEnd)
    WHEN @chunkType = 2 THEN DateDiff(month, c.ChunkStart, c.ChunkEnd)
    WHEN @chunkType = 3 THEN DateDiff(year,  c.ChunkStart, c.ChunkEnd)
    ELSE Null
  END AS FLOAT) / CAST(@chunkSize AS FLOAT) AS Amount
    FROM _ChunksStart AS c
  )

  SELECT
    c.*
  INTO #ChunkGroups
  FROM _ChunksGroups AS c
  ;
  
  
  
  
  
  
  WITH
  _ChunksRaw AS (
    SELECT
      n.Id AS ChunkIndex,
      g.Id AS ChunkGroupId,
      CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   (n.Id + 0) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 0) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 3 THEN DateAdd(year,  (n.Id + 0) * @chunkSize, g.ChunkStart)
    ELSE Null
  END AS StartDate,
      CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   (n.Id + 1) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 1) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 3 THEN DateAdd(year,  (n.Id + 1) * @chunkSize, g.ChunkStart)
    ELSE Null
  END AS EndDate,
      g.ChunkName AS ChunkName,
      CASE
        WHEN @dateNormalize in (1, 2, 3) THEN CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   n.Id * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart)))
    WHEN @chunkType = 2 THEN DateAdd(month, n.Id * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart)))
    WHEN @chunkType = 3 THEN DateAdd(year,  n.Id * @chunkSize, datefromparts(2000, month(g.ChunkStart), day(g.ChunkStart)))
    ELSE Null
  END
        WHEN @dateNormalize = 4 THEN CASE
    WHEN @chunkType = 1 THEN DateAdd(day,   (n.Id + 0) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 2 THEN DateAdd(month, (n.Id + 0) * @chunkSize, g.ChunkStart)
    WHEN @chunkType = 3 THEN DateAdd(year,  (n.Id + 0) * @chunkSize, g.ChunkStart)
    ELSE Null
  END
        ELSE Null
      END AS ChunkDate
          FROM #ChunkGroups AS g
    INNER JOIN #ChunkNumbers AS n
            ON n.Id < g.Amount
  ),
  _ChunksCorrected AS (
    SELECT
      c.ChunkIndex,
      c.ChunkGroupId,
      c.StartDate,
      CASE
    WHEN year(c.EndDate) * 10000 + month(c.EndDate) * 100 + day(c.EndDate) <= year(g.ChunkEnd) * 10000 + month(g.ChunkEnd) * 100 + day(g.ChunkEnd) THEN c.EndDate
    ELSE g.ChunkEnd
  END AS EndDate,
      c.ChunkName,
      CASE
        WHEN @dateNormalize = 1 THEN datefromparts(2000, 1, 1)
        WHEN @dateNormalize = 2 THEN DateAdd(day,   7 - datepart(dayofyear, datefromparts(2000, 1, day(c.ChunkDate))) % @chunkSize, datefromparts(2000, 1, day(c.ChunkDate)))
        WHEN @dateNormalize = 3 THEN DateAdd(day,   7 - datepart(dayofyear, datefromparts(2000, month(c.ChunkDate), day(c.ChunkDate))) % @chunkSize, datefromparts(2000, month(c.ChunkDate), day(c.ChunkDate)))
        ELSE c.ChunkDate
      END AS ChunkDate
          FROM _ChunksRaw AS c
    INNER JOIN #ChunkGroups AS g
            ON c.ChunkGroupId = g.Id
  )

  SELECT
    c.*
  INTO #Chunks
  FROM _ChunksCorrected AS c
  ;


  -------------
  -- Sources --
  -------------

  IF @leftHandSide = 'A' OR @rightHandSide = 'A'
  -- Answers
  SELECT
    p.Id AS Id,
    1 AS Value,
    1 AS Amount,
    p.OwnerUserId AS UserId,
    c.ChunkName AS ChunkName,
    c.ChunkDate AS ChunkDate
        INTO #Answers
        FROM #Chunks AS c
  INNER JOIN Posts AS p
          ON c.StartDate <= p.CreationDate
         AND p.CreationDate < c.EndDate
       WHERE p.PostTypeId = 2
  ;

  IF @leftHandSide = 'P'
  -- Posts
  SELECT
    p.Id AS Id,
    1 AS Value,
    1 AS Amount,
    p.OwnerUserId AS UserId,
    c.ChunkName AS ChunkName,
    c.ChunkDate AS ChunkDate
        INTO #Posts
        FROM #Chunks AS c
  INNER JOIN Posts AS p
          ON c.StartDate <= p.CreationDate
         AND p.CreationDate < c.EndDate
  ;

  IF @leftHandSide = 'Q'
  -- Questions
  SELECT
    p.Id AS Id,
    1 AS Value,
    1 AS Amount,
    p.OwnerUserId AS UserId,
    c.ChunkName AS ChunkName,
    c.ChunkDate AS ChunkDate
        INTO #Questions
        FROM #Chunks AS c
  INNER JOIN Posts AS p
          ON c.StartDate <= p.CreationDate
         AND p.CreationDate < c.EndDate
       WHERE p.PostTypeId = 1
  ;

  IF @leftHandSide = 'R'
  -- Reputation
  SELECT
    v.PostId AS Id,
    CASE WHEN v.VoteTypeId = 1 THEN 15 WHEN v.VoteTypeId = 2 THEN 10 WHEN v.VoteTypeId = 3 THEN -2 WHEN v.VoteTypeId = 8 THEN -v.BountyAmount WHEN v.VoteTypeId = 9 THEN v.BountyAmount WHEN v.VoteTypeId = 16 THEN 2 ELSE 0 END AS Value,
    1 AS Amount,
    v.UserId AS UserId,
    c.ChunkName AS ChunkName,
    c.ChunkDate AS ChunkDate
        INTO #Reputation
        FROM #Chunks AS c
  INNER JOIN Votes AS v
          ON c.StartDate <= v.CreationDate
         AND v.CreationDate < c.EndDate
       WHERE (
                 v.VoteTypeId = 16
                 AND v.BountyAmount IS NOT Null
             )
          OR v.VoteTypeId in (1, 2, 3, 8, 9)
  ;

  IF @leftHandSide = 'V'
  -- Votes
  SELECT
    v.PostId AS Id,
    CASE WHEN v.VoteTypeId = 2 THEN 1 ELSE -1 END AS Value,
    1 AS Amount,
    v.UserId AS UserId,
    c.ChunkName AS ChunkName,
    c.ChunkDate AS ChunkDate
        INTO #Votes
        FROM #Chunks AS c
  INNER JOIN Votes AS v
          ON c.StartDate <= v.CreationDate
         AND v.CreationDate < c.EndDate
       WHERE v.VoteTypeId in (2, 3)
  ;

  IF @leftHandSide = 'Z'
  -- Zombies
  SELECT
    p.Id AS Id,
    1 AS Value,
    1 AS Amount,
    p.OwnerUserId AS UserId,
    c.ChunkName AS ChunkName,
    c.ChunkDate AS ChunkDate
        INTO #Zombies
        FROM #Chunks AS c
  INNER JOIN Posts AS p
          ON c.StartDate <= p.CreationDate
         AND p.CreationDate < c.EndDate
       WHERE p.PostTypeId = 1
         AND p.AcceptedAnswerId IS Null
         AND 0 = (
                 SELECT
                     count(*)
                  FROM Posts AS a
                 WHERE a.ParentId = p.Id
                   AND 0 < a.Score
             )
  ;


  -------------
  -- Grouped --
  -------------

  
  SELECT
    sum(a.Value) AS Value,
    sum(a.Amount) AS Amount,
    a.ChunkName AS ChunkName,
    a.ChunkDate AS ChunkDate
      INTO #ChunkAnswers
      FROM #Answers AS a
  GROUP BY a.ChunkName, a.ChunkDate
  ;
  
  SELECT
    sum(q.Value) AS Value,
    sum(q.Amount) AS Amount,
    q.ChunkName AS ChunkName,
    q.ChunkDate AS ChunkDate
      INTO #ChunkQuestions
      FROM #Questions AS q
  GROUP BY q.ChunkName, q.ChunkDate
  ;


  --------------
  -- Equation --
  --------------

  
  SELECT
    cast(a.Value as float) / cast(q.Value as float) AS Value,
    a.Amount + q.Amount AS Amount,
    a.ChunkName AS ChunkName,
    a.ChunkDate AS ChunkDate
        INTO #Metric
        FROM #ChunkAnswers AS a
  INNER JOIN #ChunkQuestions AS q
          ON a.ChunkName = q.ChunkName
         AND a.ChunkDate = q.ChunkDate
  ;


  -------------
  -- Cleaned --
  -------------

  
  SELECT
    s.Value AS Value,
    s.ChunkName AS ChunkName,
    s.ChunkDate AS ChunkDate
      INTO #MetricClean
      FROM #Metric AS s
  ORDER BY s.ChunkDate, s.ChunkName
  ;


  -------------
  -- Display --
  -------------

  
  DECLARE @pivot AS nvarchar(max) = STUFF(
    (
      SELECT
        ',' + QUOTENAME(ChunkName)
          FROM #ChunkGroups
      ORDER BY Id ASC
      FOR XML PATH(''), TYPE
    ).value(
      '.',
      'NVARCHAR(MAX)'
    ),
    1,
    1,
    ''
  );
  PRINT @pivot;
  DECLARE @query AS nvarchar(max) = 'SELECT * FROM #MetricClean PIVOT ( sum(Value) FOR ChunkName IN (' + @pivot + ') ) AS Pivoted';
  execute(@query);

fork query

edited sep 22 Sᴀᴍ Onᴇᴌᴀ

created sep 20 Peilonrayz