Consoldate Date Range Values

Question

On an every minute interval, I would like to consolidate values in my SQL table to compress space. If I have a table with values such as:

UserId Value Date
1      2     10/08/2010 10:30:00
1      2     10/08/2010 10:30:10
1      2     10/08/2010 10:30:20
1      2     10/08/2010 10:30:30
1      2     10/08/2010 10:30:40
1      2     10/08/2010 10:30:50
1      2     10/08/2010 10:31:00
1      2     10/08/2010 10:31:10
1      2     10/08/2010 10:31:20
1      2     10/08/2010 10:31:30
1      2     10/08/2010 10:31:40
1      2     10/08/2010 10:31:50

Every minute, I would like to compress values into single minute intervals. So the top table would look like:

UserId Value Date
1      12     10/08/2010 10:30:00
1      12     10/08/2010 10:31:00

...after thh query had been run. The previous values would be removed and replaced by the new consolidated values.

How can I accomplish this in TSQL?

Answer 1

Assuming a table called Test, the following script should work.

BEGIN TRANSACTION

UPDATE Test
SET Test.Value = T2.Value
FROM Test
INNER JOIN (
    SELECT UserId, SUM([Value]) as [Value], 
         convert(nvarchar(25), [Date], 100) as [Date]
    FROM Test
    GROUP BY UserId, convert(nvarchar(25), [Date], 100)
) T2 ON Test.UserId = T2.UserId AND Test.[Date] = T2.[Date]
INNER JOIN (
    SELECT UserId, COUNT([Value]) as ValueCount, 
         convert(nvarchar(25), [Date], 100) as [Date]
    FROM Test
    GROUP BY UserId, convert(nvarchar(25), [Date], 100) 
) T3 ON Test.UserId = T3.UserId AND Test.[Date] = T3.[Date] AND ValueCount > 1

DELETE  
FROM Test
WHERE datepart(ss, [Date]) <> 0

COMMIT TRANSACTION

The way the script works is that, within a transaction, it updates Value for the '00' time by summing up all the values for that minute IF there is at least two records for that minute range (i.e. 00 seconds, 10 seconds) so that unnecessary updates aren't performed.

The query then deletes all records that don't have '00' for seconds, as they will all have been included in the update above.

Answer 2

With RawData As
    (
    Select 1 As UserId, 2 As Value, Cast('10/08/2010 10:30:00' As datetime) As [Date]
    Union All Select 1, 2, '10/08/2010 10:30:10'
    Union All Select 1, 2, '10/08/2010 10:30:20'
    Union All Select 1, 2, '10/08/2010 10:30:30'
    Union All Select 1, 2, '10/08/2010 10:30:40'
    Union All Select 1, 2, '10/08/2010 10:30:50'
    Union All Select 1, 2, '10/08/2010 10:31:00'
    Union All Select 1, 2, '10/08/2010 10:31:10'
    Union All Select 1, 2, '10/08/2010 10:31:20'
    Union All Select 1, 2, '10/08/2010 10:31:30'
    Union All Select 1, 2, '10/08/2010 10:31:40'
    Union All Select 1, 2, '10/08/2010 10:31:50'
    )
Select UserId, Value, DateAdd(s, -DatePart(s, [Date]), [Date])
From RawData
Group By UserId, Value, DateAdd(s, -DatePart(s, [Date]), [Date])