SQL Server : populate table in 15 minute intervals

Question

I need to populate a table in SQL Server with an ID column and a TimeValue column with 15 minute intervals between 01/01/2000 and 01/01/2020.

It seems there must e a simple way to do it, but I'm new to T-SQL and can't find any easy way to achieve this.

i.e.

ID         Timevalue           
------------------------------
      0    01/01/2000 00:00:00
      1    01/01/2000 00:15:00
      2    01/01/2000 00:30:00
   ...      ...                
 701280    01/01/2020 00:00:00

If you're interested this table is being used to join with a table with timestamps and values - the joined table may/may not have blanks for some intervals but shouldn't do any grouping/averaging if multiple values appear between say 01:00-01:15 it should only report the value at 01:00 and 01:15. There needs to be a fixed number of output rows so it "syncs" with other tables which are being produced in Excel.

If you can think of a better way of doing this I would be keen to know!

Answer 1

You can use a numbers table

WITH Numbers AS
(
    SELECT TOP (10000) n = CONVERT(INT, ROW_NUMBER() OVER (ORDER BY s1.[object_id]))
    FROM sys.all_objects AS s1 CROSS JOIN sys.all_objects AS s2
)
SELECT id = ROW_NUMBER() OVER (ORDER BY n), [timestamp] = DATEADD(MINUTE, n, '00:00:00')
FROM Numbers
WHERE n % 15 = 0

Answer 2

You can create your time intervals using Recursive CTE:

CREATE TABLE Table1 (ID INT IDENTITY(0,1), TIMEVALUE DATETIME);

DECLARE @start DATETIME;
DECLARE @end DATETIME;

SET @start = '20000101';
SET @end = '20200101';

WITH CTE_DT AS 
(
    SELECT @start AS DT
    UNION ALL
    SELECT DATEADD(MINUTE,15,DT) FROM CTE_DT
    WHERE DT< @end
)
INSERT INTO Table1
SELECT DT FROM CTE_DT
OPTION (MAXRECURSION 0);

SQLFiddle DEMO