DECLARE @_chunkStart AS int = ##ChunkStart:int?0##; DE...

0

Please login or register to vote for this query.

(click on this box to dismiss)

Drones and Model Aircraft Meta

Q&A for experienced flyers, pilots, and builders of drones and model aircraft

--  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##;


  --------------------
  --  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 Name,
      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.Name AS Name,
      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 Date
          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.Name,
      CASE
        WHEN @dateNormalize = 1 THEN datefromparts(2000, 1, 1)
        WHEN @dateNormalize = 2 THEN DateAdd(day,   7 - datepart(dayofyear, datefromparts(2000, 1, day(c.Date))) % @chunkSize, datefromparts(2000, 1, day(c.Date)))
        WHEN @dateNormalize = 3 THEN DateAdd(day,   7 - datepart(dayofyear, datefromparts(2000, month(c.Date), day(c.Date))) % @chunkSize, datefromparts(2000, month(c.Date), day(c.Date)))
        ELSE c.Date
      END AS Date
          FROM _ChunksRaw AS c
    INNER JOIN #ChunkGroups AS g
            ON c.ChunkGroupId = g.Id
  )

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


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


  SELECT
    s.Id AS Id,
    1 AS Value,
    1 AS Amount,
    s.CreationDate AS StartDate,
    s.OwnerUserId AS "User",
    c.ChunkIndex AS ChunkIndex,
    c.ChunkGroupId AS ChunkGroupId,
    c.Name AS Name,
    c.Date AS Date
        INTO #Answers
        FROM Posts AS s
  INNER JOIN #Chunks AS c
          ON c.StartDate <= s.CreationDate
         AND s.CreationDate < c.EndDate
       WHERE s.PostTypeId = 2
  ;

  SELECT
    s.Id AS Id,
    1 AS Value,
    1 AS Amount,
    s.CreationDate AS StartDate,
    s.OwnerUserId AS "User",
    c.ChunkIndex AS ChunkIndex,
    c.ChunkGroupId AS ChunkGroupId,
    c.Name AS Name,
    c.Date AS Date
        INTO #Questions
        FROM Posts AS s
  INNER JOIN #Chunks AS c
          ON c.StartDate <= s.CreationDate
         AND s.CreationDate < c.EndDate
       WHERE s.PostTypeId = 1
  ;


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


  SELECT
    sum(a.Amount) AS Amount,
    sum(a.Value) AS Value,
    a.Date AS Date,
    a.ChunkGroupId AS ChunkGroupId,
    a.ChunkIndex AS ChunkIndex,
    a.Name AS Name
      INTO #ChunkAnswers
      FROM #Answers AS a
  GROUP BY a.Date, a.ChunkGroupId, a.ChunkIndex, a.Name
  ;

  SELECT
    sum(q.Amount) AS Amount,
    sum(q.Value) AS Value,
    q.Date AS Date,
    q.ChunkGroupId AS ChunkGroupId,
    q.ChunkIndex AS ChunkIndex,
    q.Name AS Name
      INTO #ChunkQuestions
      FROM #Questions AS q
  GROUP BY q.Date, q.ChunkGroupId, q.ChunkIndex, q.Name
  ;


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


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


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


  SELECT
    s.Value AS Value,
    s.Date AS Date,
    s.Name AS Name
      INTO #MetricClean
      FROM #Metric AS s
  ORDER BY s.Date, s.Name
  ;


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

DECLARE @pivot AS nvarchar(max) = STUFF(
    (
      SELECT
        ',' + QUOTENAME(Name)
          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 Name IN (' + @pivot + ') ) AS Pivoted';
  execute(@query);

Enter Parameters

Options:
Switch to main site
loading Hold tight while we fetch your results