Time Problem

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » Time Problem

Time Problem

Rate Topic

Display Mode

Topic Options

Author

Message

dajonx

Posted Thursday, March 07, 2013 9:27 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 134, Visits: 860

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.

My Blog!

Post #1428060

Eugene Elutin

Posted Thursday, March 07, 2013 10:13 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 9:40 AM
Points: 2,596, Visits: 4,507

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

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!" BigGrin

(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help

Post #1428110

dajonx

Posted Thursday, March 07, 2013 1:24 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 134, Visits: 860

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!

My Blog!

Post #1428241

Steven Willis

Posted Thursday, March 07, 2013 7:47 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 298, Visits: 1,323

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

Post #1428347

dajonx

Posted Friday, March 08, 2013 9:19 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 134, Visits: 860

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... Tongue

Thank you!

My Blog!

Post #1428640

Steven Willis

Posted Friday, March 08, 2013 7:57 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 298, Visits: 1,323

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,
    [note] 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,
    [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 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],[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

    END

CLOSE UpdateList
DEALLOCATE UpdateList


/* Display the final results */

SELECT
    *
FROM
    #ResultTable
WHERE 
    UniqueKey > 0
ORDER BY
    [id],[RecTime]

Post #1428826

dajonx

Posted Wednesday, March 13, 2013 8:56 AM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Yesterday @ 10:05 AM
Points: 134, Visits: 860

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! BigGrin

My Blog!

Post #1430445

Steven Willis

Posted Wednesday, March 13, 2013 1:57 PM

SSC Veteran

Group: General Forum Members
Last Login: Yesterday @ 9:51 AM
Points: 298, Visits: 1,323

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! BigGrin

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,
    [note] 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,
	[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
    
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,
        [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 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

Post #1430618

dwain.c

Posted Wednesday, March 13, 2013 6:56 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:37 PM
Points: 2,370, Visits: 3,252

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.

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1430706

dwain.c

Posted Wednesday, March 13, 2013 7:21 PM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 8:37 PM
Points: 2,370, Visits: 3,252

dajonx (3/7/2013)

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.

Post #1430707

« Prev Topic | Next Topic »

Permissions