March 7, 2013 at 9:27 am
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 Value
2013-03-02 13:40:00 50
2013-03-02 14:00:00 100
2013-03-02 14:20:00 200
2013-03-02 14:30:00 150
2013-03-02 14:50:00 300
2013-03-02 15:00:00 200
What I'm trying to do is figure a way to find the minute differences by each hour. For example:
Time Minute Difference
2013-03-02 14:00:00 20
2013-03-02 14:20:00 20
2013-03-02 14:30:00 10
2013-03-02 14:50:00 20
2013-03-02 15:00:00 10
2013-03-02 17:00:00 120
Could someone please help me?
Thank you.
March 7, 2013 at 10:13 am
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) Diff
from t t1 left join t t2 on t1.rn = t2.rn + 1
March 7, 2013 at 1:24 pm
Thank you!
Is there a way to figure this out?
Time Minute Difference
2013-03-02 15:00:00 10
2013-03-02 16:20:00 80
2013-03-02 17:20:00 60
But 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 *20
The 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!
March 7, 2013 at 7:47 pm
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 NULL
DROP TABLE #TempTable
CREATE 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 NULL
DROP TABLE #ResultTable
CREATE TABLE #ResultTable (
[ID] INT IDENTITY(1,1) NOT NULL,
[SomeDate] DATETIME NULL,
[Diff] INT NULL,
PRIMARY KEY (ID))
INSERT INTO #ResultTable
SELECT
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 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
--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].ID
WHEN 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
,Diff
FROM
#ResultTable
ORDER BY
SomeDate
March 8, 2013 at 9:19 am
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!
March 8, 2013 at 7:57 pm
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! :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.
/* Create the source data for testing */
IF OBJECT_ID('tempdb..#TempTable') IS NOT NULL
DROP TABLE #TempTable
CREATE TABLE #TempTable (
[UniqueKey] INT IDENTITY(1,1) NOT NULL,
[id] INT NOT NULL,
[RecTime] SMALLDATETIME NOT NULL,
[value] SMALLMONEY,
[flag] SMALLINT,
[max_value] SMALLMONEY,
VARCHAR(500),
PRIMARY KEY([UniqueKey],[id],[RecTime]));
INSERT INTO #TempTable
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
(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 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,
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,
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,
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 CURSOR
FOR
SELECT DISTINCT
id
FROM
#TempTable
WHERE
UniqueKey > 0
OPEN UpdateList
FETCH NEXT FROM UpdateList
INTO @ProcessID
WHILE @@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],)
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
END
CLOSE UpdateList
DEALLOCATE UpdateList
/* Display the final results */
SELECT
*
FROM
#ResultTable
WHERE
UniqueKey > 0
ORDER BY
[id],[RecTime]
March 13, 2013 at 1:57 pm
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! 😀
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.
/* 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,
VARCHAR(500),
PRIMARY KEY([UniqueKey],[id],[RecTime]));
GO
INSERT INTO dbo.TempSourceTable
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
(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, '') -- 10min
GO
/* 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,
[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
AS
BEGIN
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,
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,
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,
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],)
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 42
Waiting 10 seconds before processing batch 51
...Processing complete for batch 51
Waiting 10 seconds before processing batch 4429
...Processing complete for batch 4429
SET NOCOUNT ON
TRUNCATE TABLE dbo.FinalResultTable
/* Get the Process IDs */
IF OBJECT_ID('tempdb..#BatchTable') IS NOT NULL
DROP TABLE #BatchTable
CREATE TABLE #BatchTable (
[UniqueKey] INT NOT NULL,
[ID] INT NULL,
PRIMARY KEY (UniqueKey))
INSERT INTO #BatchTable
SELECT
ROW_NUMBER() OVER (ORDER BY ID) AS UniqueKey
,R.ID
FROM
(
SELECT DISTINCT
ID
FROM
dbo.TempSourceTable
) R
DECLARE
@ProcessID NVARCHAR(1000)
,@rowCount INT
,@UniqueKey INT
,@msg VARCHAR(100)
SELECT @rowCount = COUNT(ID) FROM #BatchTable
SELECT @UniqueKey = MIN(UniqueKey) FROM #BatchTable
SELECT @ProcessID = ID FROM #BatchTable WHERE UniqueKey = @UniqueKey
SET @msg = 'Beginning process for batch '+@ProcessID
RAISERROR(@msg,10,1) WITH NOWAIT
WHILE @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 results
SELECT * FROM dbo.FinalResultTable
March 13, 2013 at 6:56 pm
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 A
WHERE 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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
March 13, 2013 at 7:21 pm
dajonx (3/7/2013)
Thank you!Is there a way to figure this out?
Time Minute Difference
2013-03-02 15:00:00 10
2013-03-02 16:20:00 80
2013-03-02 17:20:00 60
But 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 *20
The 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 A
WHERE NOT EXISTS (
SELECT *
FROM AddHours AS B
WHERE B.dt = A.dt + 1)AND dt < (SELECT MAX(dt) FROM AddHours)
UNION ALL
SELECT dt, value
FROM (
SELECT TOP 1 dt, value
FROM @t2
ORDER BY dt) a
ORDER BY dt, value;
Should also be pretty swift, assuming you can put a clustered index on your date.
[Edit]: Solution simplified.
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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply