Time Tally Table

Question

Post reply

Time Tally Table

dougsto

SSC Veteran

Points: 298
More actions
November 14, 2023 at 10:28 pm
Go to Answer
#4327580
I'm attempting to create a time tally table but it is not working as expected. I just need a start time and end time for a 24 hour period similar to this example. I need each 15 minute increment for each 15 minute start time in a day. As you can see the first record is for 1/1/1900 00:00:000 with an end time of the same. The last record in the table should be 1/1/1900 11:45:000 for the start time and the end time would be the same. Hopefully that makes sense.
Here is my stored procedure
```
ALTER PROCEDURE [dbo].[PopulateScheduleTallyTable] 
-- Add the parameters for the stored procedure here
@start DATETIME = '1/1/1900 00:00:000', 
@end DATETIME = '1/1/1900 23:59:000',
@enddate DATETIME = '1/1/1900'
AS
BEGIN
SET NOCOUNT ON;
CREATE TABLE #Table1 (StartTime DATETIME, EndTime DATETIME);

WHILE @start < @end
BEGIN
WHILE @enddate BETWEEN @start AND @end
BEGIN
INSERT INTO #Table1
VALUES (@start, @enddate)
SET @enddate = DATEADD(MINUTE, 15, @enddate)
END
  SET @enddate = '1/1/1900 00:00:000'
  SET @start = DATEADD(MINUTE, 15, @start)
END

SELECT * FROM #Table1
END
```
This works for the first start time but the inner loop does not run after the first iteration. I'm sure it is something simple that I'm missing but any assistance would be appreciated.

Phil Parkin

SSC Guru

Points: 246960

November 14, 2023 at 11:02 pm

Answer

#4327618

A WHILE loop is a really slow way of generating this. Instead, consider using something like this, which does it fast! (Once again, thanks to Jeff Moden for the base code.)

DECLARE @StartDate DATETIME --Inclusive
       ,@EndDate DATETIME   --Exclusive
       ,@Repeats INT;

SELECT @StartDate = '19000101' --Inclusive
      ,@EndDate = '19000102'   --Exclusive
      ,@Repeats = DATEDIFF (MINUTE, @StartDate, @EndDate) / 15;

SELECT TOP(@Repeats)
       TheMonth = DATEADD (MINUTE, 15 * (ROW_NUMBER () OVER (ORDER BY(SELECT NULL)) - 1), @StartDate)
FROM sys.all_columns ac1
    CROSS JOIN sys.all_columns ac2;

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

March 26, 2024 at 3:17 am

This was removed by the editor as SPAM
March 26, 2024 at 3:17 am

This was removed by the editor as SPAM

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 1

Try installing the table valued function here: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function

Then to select 15 minute intervals as in your example:

SELECT Value
  FROM dbo.DateRange('1900-01-01 00:00', '1900-01-01 23:59' ,'mi', 15)
;

Screenshot 2023-11-14 232146

Jonathan AC Roberts SSCoach Points: 18354 More actions · Answer 2

.

This reply was modified 1 year ago by Jonathan AC Roberts. Reason: duplicate

Eddie Wuerch SSChampion Points: 12535 More actions · Answer 3

You're working too hard. You can do decimal math on datetime values; 1.0 = one full day, 0.5 = 12 hours, etc:

SELECT getdate() AS [Now], getdate() + 1 AS [aDayFromNow], getdate() + 0.25 AS [6HoursFromNow]

That makes this stuff much easier:

DECLARE @StartTime smalldatetime = 0, 
        @interval decimal(12, 8) = 15.0 / 1440.0; -- 15 minutes / total minutes in 1 day;

SELECT TOP 96 @StartTime + (ROW_NUMBER()  OVER (ORDER BY (SELECT 0)) - 1) * @interval AS [StartTime],
       @StartTime + (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) * @interval) AS [EndTime]
FROM sys.all_objects;


StartTime               EndTime
----------------------- -----------------------
1900-01-01 00:00:00     1900-01-01 00:15:00
1900-01-01 00:15:00     1900-01-01 00:30:00
1900-01-01 00:30:00     1900-01-01 00:45:00
1900-01-01 00:45:00     1900-01-01 01:00:00
1900-01-01 01:00:00     1900-01-01 01:15:00
1900-01-01 01:15:00     1900-01-01 01:30:00
[...]
1900-01-01 23:30:00     1900-01-01 23:45:00
1900-01-01 23:45:00     1900-01-02 00:00:00

(96 rows affected)

Eddie Wuerch
MCM: SQL

Jeff Moden SSC Guru Points: 1004343 More actions · Answer 4

Jonathan AC Roberts wrote:

Try installing the table valued function here: https://www.sqlservercentral.com/scripts/a-daterange-table-valued-function
Then to select 15 minute intervals as in your example:
SELECT Value
  FROM dbo.DateRange('1900-01-01 00:00', '1900-01-01 23:59' ,'mi', 15)
;

Chicken dinner right there!

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)