CODEWITHSUNDEEP
sql-serverincrement
VBStarr
VBStarr

Reputation: 686

Updating a group by with incrementing values

I have a table in SQL Server 2016 that looks like this:

ID  DataDate    DataHour Value  UpdateDate  UpdateTime
23  09/03/2019   11       65.2  09/15/2019   04:15:209
16  09/03/2019   11       66.7  09/15/2019   04:15:209
26  09/03/2019   11       62.3  09/15/2019   04:15:209
27  09/03/2019   11       69.8  09/15/2019   04:15:209
15  09/03/2019   11       59.2  09/15/2019   04:15:209
22  09/02/2019   11       45.2  09/15/2019   04:15:209
41  09/02/2019   11       36.7  09/15/2019   04:15:209
25  09/02/2019   11       62.3  09/15/2019   04:15:209
21  09/02/2019   11       79.8  09/15/2019   04:15:209
12  09/02/2019   11       19.2  09/15/2019   04:15:209
17  09/02/2019   11       19.2  09/15/2019   04:15:209

I'd like to update the UpdateDate and UpdateTime columns, as follows: - Group by DataDate, DataHour - Order by ID Desc (e.g. highest first) - Leave the first row of the group as-is - For each remaining row in the group, subtract one hour from UpdateTime - If the updated UpdateTime goes to the previous day (i.e. hour = 23), decrement the UpdateDate by 1 day

Result should look like:

ID  DataDate    DataHour Value  UpdateDate  UpdateTime
23  09/03/2019   11       65.2  09/15/2019   02:15:209
16  09/03/2019   11       66.7  09/15/2019   01:15:209
26  09/03/2019   11       62.3  09/15/2019   03:15:209
27  09/03/2019   11       69.8  09/15/2019   04:15:209
15  09/03/2019   11       59.2  09/15/2019   00:15:209
22  09/02/2019   11       45.2  09/15/2019   02:15:209
41  09/02/2019   11       36.7  09/15/2019   04:15:209
25  09/02/2019   11       62.3  09/15/2019   03:15:209
21  09/02/2019   11       79.8  09/15/2019   01:15:209
12  09/02/2019   11       19.2  09/14/2019   23:15:209
17  09/02/2019   11       19.2  09/15/2019   00:15:209

Here's what I have so far:

UPDATE t
SET t.UpdateTime = DATEADD(hour,-1,g.UpdateTime), t.UpdateDate = CASE WHEN DATEPART(hour,DATEADD(hour,-1,g.UpdateTime)) = 23 THEN DATEADD(day,-1,g.UpdateDate) ELSE g.UpdateDate END
FROM mytable t
INNER JOIN (
    SELECT MAX(ID) As ID, DataDate, DataHour, MAX(UpdateDate) As UpdateDate, MAX(UpdateTime) As UpdateTime
    FROM mytable
    GROUP BY DataDate, DataHour, ID
    ORDER BY DataDate, DataHour, ID DESC
) g
ON g.id = t.id

But this is not incrementing through the group properly. Not sure if I need a window function to help with this.

Upvotes: 0

Views: 41

Answers (1)

Laughing Vergil
Laughing Vergil

Reputation: 3756

Having your date and time values separated makes this difficult (as did the fact that the time was misformatted), but this should do what you want.

First, create test data:

CREATE TABLE #tmp (ID int, DataDate date, DataHour int, Value decimal(9,1), UpdateDate date, UpdateTime Time(3))
INSERT INTO #tmp Values
    (23,'09/03/2019', 11, 65.2,'09/15/2019', '04:15:20.9'),
    (16  ,'09/03/2019',   11       ,66.7  ,'09/15/2019', '04:15:20.9'),
    (26  ,'09/03/2019',   11       ,62.3  ,'09/15/2019', '04:15:20.9'),
    (27  ,'09/03/2019',   11       ,69.8  ,'09/15/2019', '04:15:20.9'),
    (15  ,'09/03/2019',   11       ,59.2  ,'09/15/2019', '04:15:20.9'),
    (22  ,'09/02/2019',   11       ,45.2  ,'09/15/2019', '04:15:20.9'),
    (41  ,'09/02/2019',   11       ,36.7  ,'09/15/2019', '04:15:20.9'),
    (25  ,'09/02/2019',   11       ,62.3  ,'09/15/2019', '04:15:20.9'),
    (21  ,'09/02/2019',   11       ,79.8  ,'09/15/2019', '04:15:20.9'),
    (12  ,'09/02/2019',   11       ,19.2  ,'09/15/2019', '04:15:20.9'),
    (17  ,'09/02/2019',   11       ,19.2  ,'09/15/2019', '04:15:20.9')

Then here is the code that will generate the desired output. Modify this and put it into UPDATE statements as needed:

;WITH Base as (
    SELECT 
        DataDate, 
        DataHour,
        Max(UpdateDate) as BaseDate,
        Max(UpdateTime) as BaseTime
    FROM #tmp t2
    GROUP BY DataDate, 
        DataHour
),
AdjData as (
    SELECT 
        ID,
        t.DataDate,
        t.DataHour,
        t.Value,
        Cast(BaseDate as Datetime) + Cast(BaseTime as DateTime) as BaseDateTime
        ,row_number() over (Partition by t.DataDate, t.DataHour ORDER BY t.Id Desc) as Adj
    FROM #tmp t
    INNER JOIN Base b
        ON t.DataDate = b.DataDate
        AND t.DataHour = b.DataHour
    )
SELECT Id,
    DataDate,
    DataHour,
    Value,
    Cast(Dateadd(hour, -(Adj-1), BaseDateTime) AS date) as UpdateDate,
    Cast(Dateadd(hour, -(Adj-1), BaseDateTime) AS Time(3)) as UpdateTime
FROM AdjData

Upvotes: 1

Related Questions