InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 11:11:19 GMT20http://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspx[quote][b]dajonx (3/7/2013)[/b][hr]Thank you! Is there a way to figure this out?Time Minute Difference2013-03-02 15:00:00 102013-03-02 16:20:00 802013-03-02 17:20:00 60But since it has to be hour ending, I would essentially need something like this (add another record for each hour):Time Minute Difference 2013-03-02 15:00:00 10*2013-03-02 16:00:00 *60 2013-03-02 16:20:00 *20*2013-03-02 17:00:00 *40 2013-03-02 17:20:00 *20The added records are 2013-03-02 16:00:00 and 2013-03-02 17:00:00 and updated the minute difference... One of the requirements is that there has to be a weighted average value for each hour, every hour even if there isn't an actual record. Is there a way to insert missing hour ending records with the previous value?I thought I was done with this until he mentioned that... Thank you![/quote]This might work for the change to the problem spec:[code="sql"]declare @t2 table (dt datetime, value INT)insert @t2 values ('2013-03-02 15:00:00', 10),('2013-03-02 16:20:00', 80),('2013-03-02 17:20:00', 60);WITH Tally (n) AS ( SELECT TOP (SELECT 1+DATEDIFF(hour, MIN(dt), MAX(dt)) FROM @t2) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))-1 FROM sys.all_columns) ,AddHours AS ( SELECT dt=DATEADD(hour, n, (SELECT MIN(dt) FROM @t2)) FROM Tally UNION SELECT dt FROM @t2)SELECT dt= ( SELECT MIN(B.dt) FROM AddHours AS B WHERE B.dt > A.dt ) ,value=DATEDIFF(minute, dt, ( SELECT MIN(B.dt) FROM AddHours AS B WHERE B.dt > A.dt ) )FROM AddHours AS AWHERE NOT EXISTS ( SELECT * FROM AddHours AS B WHERE B.dt = A.dt + 1)AND dt < (SELECT MAX(dt) FROM AddHours)UNION ALLSELECT dt, valueFROM ( SELECT TOP 1 dt, value FROM @t2 ORDER BY dt) aORDER BY dt, value;[/code]Should also be pretty swift, assuming you can put a clustered index on your date.[b][Edit]:[/b] Solution simplified.Wed, 13 Mar 2013 19:21:50 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxI'm not sure if this has been suggested but I think it will be pretty fast:[code="sql"]declare @t table (dt datetime)insert @t values ('2013-03-02 13:40:00'),('2013-03-02 14:00:00'),('2013-03-02 14:20:00'),('2013-03-02 14:30:00'),('2013-03-02 14:50:00'),('2013-03-02 15:00:00'),('2013-03-02 17:00:00');SELECT A.dt, minutes=DATEDIFF(minute, dt, ( SELECT MIN(B.dt) FROM @t AS B WHERE B.dt > A.dt) )FROM @t AS AWHERE NOT EXISTS ( SELECT * FROM @t AS B WHERE B.dt = A.dt + 1)AND dt < (SELECT MAX(dt) FROM @t);[/code][b]Edit:[/b] Note that solution proposed is for the first post.Wed, 13 Mar 2013 18:56:36 GMTdwain.chttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspx[quote][b]dajonx (3/13/2013)[/b][hr]Thank you very much for your help! The cursor helped, but it's pegging the CPU so I still need to see if I can tune it somehow. But at least it's working! :-D[/quote]Try this instead of the cursor. You will see in the script a WAITFOR statement. You can set this to cause a delay between each batch run. I set it for 10 seconds but you can set it for whatever is best to keep your server happy.Again, start with sample code. This time I'm using a regular table and I added another batch.[code="sql"]/* Create a source table for testing *//* Do this ONCE */CREATE TABLE dbo.TempSourceTable ( [UniqueKey] INT IDENTITY(1,1) NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[id],[RecTime]));GOINSERT INTO dbo.TempSourceTableVALUES (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(42, '2008-04-09 07:30:00', 923.00, 4, 1080.00, ''), -- 10min(42, '2008-04-09 07:40:00', 923.00, 4, 1080.00, ''), -- 10min(42, '2008-04-09 08:10: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(51, '2009-10-22 18:00:00', 945.00, 4, 1080.00, ''), (51, '2009-10-22 18:20:00', 948.00, 4, 1080.00, ''), -- 20min(51, '2009-10-22 20:00:00', 945.00, 4, 1080.00, ''), -- 100min(51, '2009-10-22 20:20:00', 943.00, 4, 1080.00, ''), -- 20min(51, '2009-10-22 22:00:00', 945.00, 4, 1080.00, ''), -- 40min(51, '2009-10-22 22:20:00', 947.00, 4, 1080.00, ''), -- 20min(51, '2009-10-23 00:00:00', 948.00, 4, 1080.00, ''), -- 100min(51, '2009-10-23 00:20:00', 934.00, 4, 1080.00, ''), -- 20min(51, '2009-10-23 02:00:00', 931.00, 4, 1080.00, ''), -- 40min(51, '2009-10-23 02:20:00', 918.00, 4, 1080.00, ''), -- 20min(51, '2009-10-23 04:00:00', 900.00, 4, 1080.00, ''), -- 100min(51, '2009-10-23 04:20:00', 911.00, 4, 1080.00, ''), -- 20min(51, '2009-10-23 04:30:00', 923.00, 4, 1080.00, ''), -- 10min(51, '2009-10-23 07:30:00', 923.00, 4, 1080.00, ''), -- 10min(51, '2009-10-23 07:40:00', 923.00, 4, 1080.00, ''), -- 10min(51, '2009-10-23 08:10:00', 923.00, 4, 1080.00, '') -- 10minGO/* End of sample source data input */[/code]Create a table to hold the final results.[code="sql"]/* Create a table for the final combined results *//* Do this ONCE */CREATE TABLE [dbo].[FinalResultTable]( [UniqueKey] [int] IDENTITY(1,1) NOT NULL, [id] [int] NOT NULL, [RecTime] [smalldatetime] NOT NULL, [value] [smallmoney] NULL, [flag] [smallint] NULL, [max_value] [smallmoney] NULL, [note] [varchar](500) NULL, CONSTRAINT [PK__FinalRes__06B77A925892CFA9] PRIMARY KEY CLUSTERED ( [UniqueKey] ASC, [id] ASC, [RecTime] ASC)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]) ON [PRIMARY]GO[/code]Now create a procedure to to do the actual work.[code="sql"]CREATE PROCEDURE dbo.RunBatchProcessCalculations @ProcessID INT ASBEGIN SET NOCOUNT ON /* EXEC dbo.RunBatchProcessCalculations 42 EXEC dbo.RunBatchProcessCalculations 4429 */ IF OBJECT_ID('tempdb..#InterimTable') IS NOT NULL DROP TABLE #InterimTable IF OBJECT_ID('tempdb..#CalcTable') IS NOT NULL DROP TABLE #CalcTable IF OBJECT_ID('tempdb..#ResultTable') IS NOT NULL DROP TABLE #ResultTable /* A working table to hold the results of each batch */ CREATE TABLE #InterimTable ( [UniqueKey] INT NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[id],[RecTime])); /* Another working table for holding the */ /* results of the duration calculations. */ CREATE TABLE #CalcTable ( [UniqueKey] INT NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[RecTime])); /* A working table to hold the output for each batch */ CREATE TABLE #ResultTable ( [UniqueKey] INT IDENTITY(1,1) NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[id],[RecTime])); DECLARE @NextUniqueKey INT /* Get the next unique key value */ SELECT @NextUniqueKey = MAX(UniqueKey)+1 FROM dbo.TempSourceTable WHERE UniqueKey > 0 TRUNCATE TABLE #InterimTable TRUNCATE TABLE #CalcTable /* Get a working copy of each set so we don't */ /* have to make changes to the source table. */ INSERT INTO #InterimTable (UniqueKey,id,RecTime,value,flag,max_value,note) SELECT UniqueKey ,id ,RecTime ,value ,flag ,max_value ,note FROM dbo.TempSourceTable WHERE ID = @ProcessID /* Calculate the durations and create new rows where necessary. */ INSERT INTO #CalcTable ([UniqueKey],[id],[RecTime],[value],[flag],[max_value],[note]) SELECT ROW_NUMBER() OVER (ORDER BY [RecTime])+(@NextUniqueKey) AS UniqueKey ,[id] ,(CASE WHEN DATEPART(minute,[RecTime2]) = 0 THEN DATEADD(hour,1,RecTime2) WHEN DATEDIFF(minute,[RecTime2],[RecTime]) > 120 THEN DATEADD(hour,-1,DATEADD(minute,DATEPART(minute,RecTime),RecTime)) WHEN DATEDIFF(minute,[RecTime2],[RecTime]) > 60 THEN DATEADD(hour,-1,RecTime) WHEN DATEDIFF(minute,[RecTime2],[RecTime]) = 60 THEN DATEDIFF(minute,[RecTime2],DATEADD(minute,-DATEPART(minute,[RecTime]),[RecTime])) ELSE DATEADD(minute,-DATEPART(minute,[RecTime]),[RecTime]) END) AS [RecTime] ,[value] ,[flag] ,[max_value] ,(CASE WHEN DATEPART(minute,[RecTime2]) = 0 THEN 60 WHEN Diff >= 120 THEN DATEDIFF(minute,[RecTime2],[RecTime]) WHEN Diff > 60 THEN Diff-60 WHEN Diff = 60 THEN DATEPART(minute,[RecTime]) ELSE Diff END) AS Note FROM ( SELECT t1.UniqueKey ,t1.id ,t1.[RecTime] ,t1.[value] ,t1.[flag] ,t1.[max_value] ,t2.[RecTime] AS [RecTime2] ,ISNULL(DATEDIFF(minute,t2.[RecTime],t1.[RecTime]),0) AS Diff FROM #InterimTable t1 LEFT JOIN #InterimTable t2 ON t1.UniqueKey = t2.UniqueKey + 1 WHERE t1.UniqueKey > 0 AND t2.UniqueKey > 0 ) R WHERE Diff >= 60 ORDER BY [RecTime] /* Insert the new rows into the interim working table. */ INSERT INTO #InterimTable SELECT UniqueKey ,id ,RecTime ,value ,flag ,max_value ,(CASE WHEN note > 60 THEN '+'+note ELSE '*' END) AS note FROM #CalcTable WHERE UniqueKey > 0 /* Insert the data into the output table */ /* sorted by datetime with new unique keys. */ INSERT INTO #ResultTable SELECT id ,RecTime ,value ,flag ,max_value ,note FROM #InterimTable WHERE UniqueKey > 0 ORDER BY RecTime SELECT id ,RecTime ,value ,flag ,max_value ,note FROM #ResultTable WHERE UniqueKey > 0 AND id = @ProcessID ORDER BY [id],[RecTime] END[/code]Finally, this script will run each batch after the specified time interval and return status messages to the SSMS Messages window.The messages will look like this:Beginning process for batch 42...Processing complete for batch 42Waiting 10 seconds before processing batch 51...Processing complete for batch 51Waiting 10 seconds before processing batch 4429...Processing complete for batch 4429[code="sql"]SET NOCOUNT ONTRUNCATE TABLE dbo.FinalResultTable/* Get the Process IDs */IF OBJECT_ID('tempdb..#BatchTable') IS NOT NULLDROP TABLE #BatchTableCREATE TABLE #BatchTable ( [UniqueKey] INT NOT NULL, [ID] INT NULL, PRIMARY KEY (UniqueKey))INSERT INTO #BatchTableSELECT ROW_NUMBER() OVER (ORDER BY ID) AS UniqueKey ,R.IDFROM ( SELECT DISTINCT ID FROM dbo.TempSourceTable ) RDECLARE @ProcessID NVARCHAR(1000) ,@rowCount INT ,@UniqueKey INT ,@msg VARCHAR(100)SELECT @rowCount = COUNT(ID) FROM #BatchTableSELECT @UniqueKey = MIN(UniqueKey) FROM #BatchTableSELECT @ProcessID = ID FROM #BatchTable WHERE UniqueKey = @UniqueKeySET @msg = 'Beginning process for batch '+@ProcessIDRAISERROR(@msg,10,1) WITH NOWAITWHILE @UniqueKey <= @rowCount BEGIN SELECT @ProcessID = id FROM #BatchTable WHERE UniqueKey = @UniqueKey IF @UniqueKey > 1 BEGIN SET @msg = 'Waiting 10 seconds before processing batch '+@ProcessID RAISERROR(@msg,10,1) WITH NOWAIT WAITFOR DELAY '00:00:10' END INSERT INTO dbo.FinalResultTable EXEC dbo.RunBatchProcessCalculations @ProcessID SET @msg = '...Processing complete for batch '+@ProcessID+CHAR(13) RAISERROR(@msg,10,1) WITH NOWAIT SET @UniqueKey = @UniqueKey + 1 END --display the resultsSELECT * FROM dbo.FinalResultTable[/code] Wed, 13 Mar 2013 13:57:38 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxThank you very much for your help! The cursor helped, but it's pegging the CPU so I still need to see if I can tune it somehow. But at least it's working! :-DWed, 13 Mar 2013 08:56:02 GMTdajonxhttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxMaybe this is getting closer? It still has issues with the first and last entry of a batch. For any time gap more than 1 hour I just add one entry and note the time gap left open in the note column. I also indicate with an asterisk where a new row was inserted.With great trepidation I've used an evil cursor. I'll let someone else work on replacing that with a CTE or something. This whole problem you presented is--as you probably knew before posting your question--a real mind bender! :hehe:I'm pretty sure that to fill the gaps with more than one entry at a time (such as a 3 hour time gap) will require a recursive CTE. If that's what you need then that is where you should head.[code="sql"]/* Create the source data for testing */IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [UniqueKey] INT IDENTITY(1,1) NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[id],[RecTime]));INSERT INTO #TempTableVALUES (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(42, '2008-04-09 07:30:00', 923.00, 4, 1080.00, ''), -- 10min(42, '2008-04-09 07:40:00', 923.00, 4, 1080.00, ''), -- 10min(42, '2008-04-09 08:10: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/* End of sample source data input */[/code][code="sql"]/* Now start processing the batches */IF OBJECT_ID('tempdb..#InterimTable') IS NOT NULLDROP TABLE #InterimTableIF OBJECT_ID('tempdb..#CalcTable') IS NOT NULLDROP TABLE #CalcTableIF OBJECT_ID('tempdb..#ResultTable') IS NOT NULLDROP TABLE #ResultTable/* A working table to hold the results of each batch */CREATE TABLE #InterimTable ( [UniqueKey] INT NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[id],[RecTime])); /* Another working table for holding the *//* results of the duration calculations. */CREATE TABLE #CalcTable ( [UniqueKey] INT NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[RecTime])); /* Finally, a table to hold the output */CREATE TABLE #ResultTable ( [UniqueKey] INT IDENTITY(1,1) NOT NULL, [id] INT NOT NULL, [RecTime] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] VARCHAR(500), PRIMARY KEY([UniqueKey],[id],[RecTime])); DECLARE @ProcessID INT ,@NextUniqueKey INT/* Get the next unique key value */ SELECT @NextUniqueKey = MAX(UniqueKey)+1 FROM #TempTable WHERE UniqueKey > 0 /* Run the calculations for each batch. In this *//* example, there are two batches: 42 and 4429. */DECLARE UpdateList CURSORFOR SELECT DISTINCT id FROM #TempTable WHERE UniqueKey > 0 OPEN UpdateListFETCH NEXT FROM UpdateList INTO @ProcessIDWHILE @@FETCH_STATUS = 0 BEGIN /* Clear the tables for each set of IDs */ TRUNCATE TABLE #InterimTable TRUNCATE TABLE #CalcTable /* Get a working copy of each set so we don't */ /* have to make changes to the source table. */ INSERT INTO #InterimTable (UniqueKey,id,RecTime,value,flag,max_value,note) SELECT UniqueKey ,id ,RecTime ,value ,flag ,max_value ,note FROM #TempTable WHERE ID = @ProcessID /* Calculate the durations and create new rows where necessary. */ INSERT INTO #CalcTable ([UniqueKey],[id],[RecTime],[value],[flag],[max_value],[note]) SELECT ROW_NUMBER() OVER (ORDER BY [RecTime])+(@NextUniqueKey) AS UniqueKey ,[id] ,(CASE WHEN DATEPART(minute,[RecTime2]) = 0 THEN DATEADD(hour,1,RecTime2) WHEN DATEDIFF(minute,[RecTime2],[RecTime]) > 120 THEN DATEADD(hour,-1,DATEADD(minute,DATEPART(minute,RecTime),RecTime)) WHEN DATEDIFF(minute,[RecTime2],[RecTime]) > 60 THEN DATEADD(hour,-1,RecTime) WHEN DATEDIFF(minute,[RecTime2],[RecTime]) = 60 THEN DATEDIFF(minute,[RecTime2],DATEADD(minute,-DATEPART(minute,[RecTime]),[RecTime])) ELSE DATEADD(minute,-DATEPART(minute,[RecTime]),[RecTime]) END) AS [RecTime] ,[value] ,[flag] ,[max_value] ,(CASE WHEN DATEPART(minute,[RecTime2]) = 0 THEN 60 WHEN Diff >= 120 THEN DATEDIFF(minute,[RecTime2],[RecTime]) WHEN Diff > 60 THEN Diff-60 WHEN Diff = 60 THEN DATEPART(minute,[RecTime]) ELSE Diff END) AS Note FROM ( SELECT t1.UniqueKey ,t1.id ,t1.[RecTime] ,t1.[value] ,t1.[flag] ,t1.[max_value] ,t2.[RecTime] AS [RecTime2] ,ISNULL(DATEDIFF(minute,t2.[RecTime],t1.[RecTime]),0) AS Diff FROM #InterimTable t1 LEFT JOIN #InterimTable t2 ON t1.UniqueKey = t2.UniqueKey + 1 WHERE t1.UniqueKey > 0 AND t2.UniqueKey > 0 ) R WHERE Diff >= 60 ORDER BY [RecTime] /* Insert the new rows into the interim working table. */ INSERT INTO #InterimTable SELECT UniqueKey ,id ,RecTime ,value ,flag ,max_value ,(CASE WHEN note > 60 THEN '+'+note ELSE '*' END) AS note FROM #CalcTable WHERE UniqueKey > 0 /* Insert the data into the output table */ /* sorted by datetime with new unique keys. */ INSERT INTO #ResultTable SELECT id ,RecTime ,value ,flag ,max_value ,note FROM #InterimTable WHERE UniqueKey > 0 ORDER BY RecTime /* Repeat for each group of IDs in the source table */ FETCH NEXT FROM UpdateList INTO @ProcessID ENDCLOSE UpdateListDEALLOCATE UpdateList/* Display the final results */SELECT *FROM #ResultTableWHERE UniqueKey > 0ORDER BY [id],[RecTime] [/code]Fri, 08 Mar 2013 19:57:53 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxThank 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:[code="sql"]CREATE TABLE [dbo].[temp] ( [id] INT NOT NULL, [time] SMALLDATETIME NOT NULL, [value] SMALLMONEY, [flag] SMALLINT, [max_value] SMALLMONEY, [note] 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[/code]This is a 5 mil record table so there are a bunch of IDs involved... The records I would need to add are:[code="sql"](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, '')[/code]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... :-PThank you!Fri, 08 Mar 2013 09:19:06 GMTdajonxhttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxThis turned out to be much trickier than I thought. I figured out a way to do it in two passes. May not be optimal and I didn't have time to do a lot of testing, but here's what I got:[code="sql"]IF OBJECT_ID('tempdb..#TempTable') IS NOT NULLDROP TABLE #TempTableCREATE TABLE #TempTable ( [ID] INT IDENTITY(1,1) NOT NULL, [SomeDate] DATETIME NULL, PRIMARY KEY (ID))INSERT INTO #TempTable(SomeDate)VALUES ('2013-03-02 13:40:00'),('2013-03-02 14:10:00'),('2013-03-02 14:20:00'),('2013-03-02 14:30:00'),('2013-03-02 14:50:00'),('2013-03-02 15:00:00'),('2013-03-02 16:20:00'),('2013-03-02 17:20:00'),('2013-03-02 17:30:00'),('2013-03-02 17:50:00'),('2013-03-02 18:00:00'),('2013-03-02 19:20:00'),('2013-03-02 19:30:00')IF OBJECT_ID('tempdb..#ResultTable') IS NOT NULLDROP TABLE #ResultTableCREATE TABLE #ResultTable ( [ID] INT IDENTITY(1,1) NOT NULL, [SomeDate] DATETIME NULL, [Diff] INT NULL, PRIMARY KEY (ID))INSERT INTO #ResultTableSELECT SomeDate ,(CASE WHEN ID = 2 THEN DATEPART(minute,SomeDate) WHEN Diff > 60 THEN Diff-60 WHEN Diff = 60 THEN DATEPART(minute,SomeDate) ELSE Diff END) AS DiffFROM ( SELECT t1.ID ,t1.SomeDate ,t2.ID AS ID2 ,t2.SomeDate AS SomeDate2 ,ISNULL(DATEDIFF(minute,t2.SomeDate,t1.SomeDate),0) Diff FROM #TempTable t1 LEFT JOIN #TempTable t2 ON t1.ID = t2.ID + 1 ) R --SELECT * FROM #ResultTable AS rt;MERGE INTO #ResultTable AS [Target] USING ( SELECT MAX(R.ID) OVER (PARTITION BY 1)+2 AS ID ,(CASE WHEN R.ID = 1 THEN DATEADD(hour,1,DATEADD(minute,-DATEPART(minute,SomeDate),SomeDate)) ELSE DATEADD(minute,-DATEPART(minute,SomeDate),SomeDate) END) AS SomeDate ,(CASE WHEN R.ID = 1 THEN DATEDIFF(minute,SomeDate,DATEADD(minute,60-DATEPART(minute,SomeDate),SomeDate)) WHEN DATEDIFF(minute,SomeDate2,SomeDate) > 60 THEN 60 WHEN DATEDIFF(minute,SomeDate2,SomeDate) = 60 THEN DATEDIFF(minute,SomeDate2,DATEADD(minute,-DATEPART(minute,SomeDate),SomeDate)) ELSE DATEDIFF(minute,SomeDate2,SomeDate) END) AS Diff FROM ( SELECT t1.ID ,t1.SomeDate ,t2.ID AS ID2 ,t2.SomeDate AS SomeDate2 ,ISNULL(DATEDIFF(minute,t2.SomeDate,t1.SomeDate),0) Diff FROM #TempTable t1 LEFT JOIN #TempTable t2 ON t1.ID = t2.ID + 1 ) R WHERE Diff >= 60 OR ID = 1 ) AS [Source] ON [Target].ID = [Source].IDWHEN MATCHED AND [Source].Diff >= 60 THEN UPDATE SET SomeDate = [Source].SomeDate ,Diff = [Source].Diff-60 WHEN NOT MATCHED BY TARGET THEN INSERT ( SomeDate ,Diff ) VALUES ( DATEADD(minute,DATEPART(minute,[Source].SomeDate),[Source].SomeDate) ,[Source].Diff );SELECT SomeDate ,DiffFROM #ResultTableORDER BY SomeDate [/code]Thu, 07 Mar 2013 19:47:08 GMTSteven Willishttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxThank you! Is there a way to figure this out?Time Minute Difference2013-03-02 15:00:00 102013-03-02 16:20:00 802013-03-02 17:20:00 60But since it has to be hour ending, I would essentially need something like this (add another record for each hour):Time Minute Difference 2013-03-02 15:00:00 10*2013-03-02 16:00:00 *60 2013-03-02 16:20:00 *20*2013-03-02 17:00:00 *40 2013-03-02 17:20:00 *20The added records are 2013-03-02 16:00:00 and 2013-03-02 17:00:00 and updated the minute difference... One of the requirements is that there has to be a weighted average value for each hour, every hour even if there isn't an actual record. Is there a way to insert missing hour ending records with the previous value?I thought I was done with this until he mentioned that... Thank you!Thu, 07 Mar 2013 13:24:31 GMTdajonxhttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspx[code="sql"]declare @t table (dt datetime)insert @t values ('2013-03-02 13:40:00'),('2013-03-02 14:00:00'),('2013-03-02 14:20:00'),('2013-03-02 14:30:00'),('2013-03-02 14:50:00'),('2013-03-02 15:00:00'),('2013-03-02 17:00:00');with t as (select dt, ROW_NUMBER() OVER (ORDER BY dt) rn from @t)select t1.dt, DATEDIFF(minute,t2.dt, t1.dt) Difffrom t t1 left join t t2 on t1.rn = t2.rn + 1[/code]Thu, 07 Mar 2013 10:13:18 GMTEugene Elutinhttp://www.sqlservercentral.com/Forums/Topic1428060-392-1.aspxHi,I'm trying to figure out a way to calculate a weighted average, but having trouble with the time part.Here are some example records:Time Value2013-03-02 13:40:00 502013-03-02 14:00:00 1002013-03-02 14:20:00 2002013-03-02 14:30:00 1502013-03-02 14:50:00 3002013-03-02 15:00:00 200What I'm trying to do is figure a way to find the minute differences by each hour. For example:Time Minute Difference2013-03-02 14:00:00 202013-03-02 14:20:00 202013-03-02 14:30:00 102013-03-02 14:50:00 202013-03-02 15:00:00 102013-03-02 17:00:00 120Could someone please help me?Thank you.Thu, 07 Mar 2013 09:27:51 GMTdajonx