0

Please login or register to vote for this query.

(click on this box to dismiss)

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
    ORDER BY a.Date, a.ChunkGroupId, a.ChunkIndex, a.Name
  ;


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

WITH

  _CleanExit AS (
    SELECT
      s.*
    FROM #Metric AS s
  )

  SELECT
    c.*
  INTO #MetricClean
  FROM _CleanExit AS c
  ;


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

SELECT * FROM #MetricClean

fork query

edited mar 4

created feb 28 anonymous