## Time Problem

 Author Message dajonx SSC Veteran Group: General Forum Members Points: 230 Visits: 1292 Hi,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. My Blog! Eugene Elutin Hall of Fame Group: General Forum Members Points: 3046 Visits: 5478 `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` _____________________________________________"The only true wisdom is in knowing you know nothing""O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D(So many miracle inventions provided by MS to us...)How to post your question to get the best and quick help dajonx SSC Veteran Group: General Forum Members Points: 230 Visits: 1292 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! My Blog! Steven Willis SSC-Addicted Group: General Forum Members Points: 483 Visits: 1721 This 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:`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 ` dajonx SSC Veteran Group: General Forum Members Points: 230 Visits: 1292 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, [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`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... :-PThank you! My Blog! Steven Willis SSC-Addicted Group: General Forum Members Points: 483 Visits: 1721 Maybe 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! 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.`/* 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 */``/* 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] ` dajonx SSC Veteran Group: General Forum Members Points: 230 Visits: 1292 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 My Blog! Steven Willis SSC-Addicted Group: General Forum Members Points: 483 Visits: 1721 dajonx (3/13/2013)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! :-DTry 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.`/* 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 */`Create a table to hold the final results.`/* 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`Now create a procedure to to do the actual work.`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`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`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` dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4279 Visits: 6431 I'm not sure if this has been suggested but I think it will be pretty fast:`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);`Edit: Note that solution proposed is for the first post. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables dwain.c SSCarpal Tunnel Group: General Forum Members Points: 4279 Visits: 6431 dajonx (3/7/2013)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!This might work for the change to the problem spec:`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;`Should also be pretty swift, assuming you can put a clustered index on your date.: Solution simplified. My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!My thought question: Have you ever been told that your query runs too fast?My advice:INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.Need to UNPIVOT? Why not CROSS APPLY VALUES instead?Since random numbers are too important to be left to chance, let's generate some!Learn to understand recursive CTEs by example.Splitting strings based on patterns can be fast!My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables