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!