• Thank you!

    I'm actually having trouble trying to apply your solution to my situation. Maybe you could be so kind as to help me integrate it?

    I should've started off the thread with my original table structure:

    CREATE TABLE [dbo].[temp] (

    [id] INT NOT NULL,

    [time] SMALLDATETIME NOT NULL,

    [value] SMALLMONEY,

    [flag] SMALLINT,

    [max_value] SMALLMONEY,

    VARCHAR(500)

    );

    ALTER TABLE [dbo].[temp] ADD CONSTRAINT [PK_TEMP] PRIMARY KEY CLUSTERED ([id], [time]);

    INSERT INTO [dbo].[temp]

    VALUES

    (42, '2008-04-08 18:00:00', 945.00, 4, 1080.00, ''),

    (42, '2008-04-08 18:20:00', 948.00, 4, 1080.00, ''), -- 20min

    (42, '2008-04-08 20:00:00', 945.00, 4, 1080.00, ''), -- 100min

    (42, '2008-04-08 20:20:00', 943.00, 4, 1080.00, ''), -- 20min

    (42, '2008-04-08 22:00:00', 945.00, 4, 1080.00, ''), -- 40min

    (42, '2008-04-08 22:20:00', 947.00, 4, 1080.00, ''), -- 20min

    (42, '2008-04-09 00:00:00', 948.00, 4, 1080.00, ''), -- 100min

    (42, '2008-04-09 00:20:00', 934.00, 4, 1080.00, ''), -- 20min

    (42, '2008-04-09 02:00:00', 931.00, 4, 1080.00, ''), -- 40min

    (42, '2008-04-09 02:20:00', 918.00, 4, 1080.00, ''), -- 20min

    (42, '2008-04-09 04:00:00', 900.00, 4, 1080.00, ''), -- 100min

    (42, '2008-04-09 04:20:00', 911.00, 4, 1080.00, ''), -- 20min

    (42, '2008-04-09 04:30:00', 923.00, 4, 1080.00, ''), -- 10min

    (4429, '2008-04-08 17:30:00', 2348.00, 4, 3340.00, ''),

    (4429, '2008-04-08 18:00:00', 2354.00, 4, 3340.00, ''), -- 30min

    (4429, '2008-04-08 19:48:00', 2399.00, 4, 3340.00, ''), -- 108min

    (4429, '2008-04-08 20:00:00', 2395.00, 4, 3340.00, ''), -- 12min

    (4429, '2008-04-08 22:00:00', 2345.00, 4, 3340.00, ''), -- 120min

    (4429, '2008-04-08 22:38:00', 2344.00, 4, 3340.00, ''), -- 38min

    (4429, '2008-04-08 23:05:00', 2297.00, 4, 3340.00, ''), -- 27min

    (4429, '2008-04-08 23:09:00', 2408.00, 4, 3340.00, ''), -- 4min

    (4429, '2008-04-08 23:31:00', 2453.00, 4, 3340.00, ''), -- 22min

    (4429, '2008-04-09 00:00:00', 2436.00, 4, 3340.00, '') -- 29min

    This is a 5 mil record table so there are a bunch of IDs involved...

    The records I would need to add are:

    (42, '2008-04-08 19:00:00', 948.00, 4, 1080.00, ''),

    (42, '2008-04-08 23:00:00', 947.00, 4, 1080.00, ''),

    (42, '2008-04-09 01:00:00', 934.00, 4, 1080.00, ''),

    (42, '2008-04-09 03:00:00', 918.00, 4, 1080.00, ''),

    (42, '2008-04-09 05:00:00', 923.00, 4, 1080.00, ''),

    (4429, '2008-04-08 19:00:00', 2354.00, 4, 3340.00, ''),

    (4429, '2008-04-08 21:00:00', 2395.00, 4, 3340.00, ''),

    (4429, '2008-04-08 23:00:00', 2344.00, 4, 3340.00, '')

    As you can see, the new records "fills" in the missing hourly times and the values are the previous record's values. Then I need to find the minute differences for the hour ending.

    Also, I don't think having an identity would work here since there's a wide variety of IDs and dates. For example, there's a record with the date of '1969-12-01 00:09:00' with a gap about 20 years... Just gets better and better... 😛

    Thank you!