Sum, upgrade and delete

Try this approach:

;
with Q as
(
    select [user], max([datetime]) as [datetime], sum(amount) as amount
    from @MyTable
    group by [user], cast([datetime] as date)
)
merge @MyTable as t using Q on Q.[user] = t.[user] and Q.[datetime] = t.[datetime]
when matched then update set amount = Q.amount
when not matched by source then delete;

It keeps the last time per day and assumes that the last time is not repeated per user.

thanks Viorel-1

I need one sum for each day of the year... one sum for each user for each day. in this way i have only one, is it right? how can i modify it?

thanks again

I think what you are asking for is just this (modified from the script above)

;
 with Q as
 (
     select [user], cast([datetime] as date) as [date], sum(amount) as amount
     from @MyTable
     group by [user], cast([datetime] as date)
 )
 merge @MyTable as t using Q on Q.[user] = t.[user] and Q.[date] = t.[datetime]
 when matched then update set amount = Q.amount
 when not matched by source then delete;

Hi. I also propose a solution. declare @users table ( [User] varchar(50) , Amount integer , [Datetime] datetime ) ; INSERT INTO @users ([User], Amount, [Datetime]) values ('john', 245, '2021-12-1 20:21:00'), ('john', 143, '2021-12-1 10:20:00'), ('john', 763, '2021-12-1 22:23:00'), ('john', 600, '2021-12-1 07:56:00'), -- ('john', 845, '2021-12-25 14:15:00'), -- ('ted', 125, '2021-08-1 16:12:01'), ('ted', 325, '2021-11-9 18:21:00'), -- ('ted', 600, '2021-11-14 19:40:00'), -- ('Mike', 131, '2021-12-1 20:21:00'), ('Mike', 115, '2021-12-1 10:21:00'), ('Mike', 160, '2021-12-1 20:00:00') -- select * from @users -- ;with T as ( select row_number() over (partition by [user] order by [user]) as rn , * from @users ) , Q as ( select row_number() over (partition by [user] order by [user]) as rn , [user] , sum(amount) as amount , max([datetime]) as [datetime] from @users group by [user], cast([datetime] as date) ) merge T using Q on Q.rn = T.rn and Q.[User] = T.[User] when matched then update set amount = Q.amount, [datetime] = Q.[datetime] when not matched by source then delete; -- select * from @users -- ![172104-immagine-2022-02-08-075839.png][1] [1]: /api/attachments/172104-immagine-2022-02-08-075839.png?platform=QnA

Hi @Gianluca Appetito
Please have a check on this:

create table #test  
([user] nvarchar(10),  
 amount int,  
 [datetime] datetime  
)  
insert into #test VALUES  
('john' , 245 ,' 2021-12-1 20:21:00 '),  
('john' , 143 ,' 2021-12-1 10:20:00 '),  
('john' , 763 ,' 2021-12-2 22:15:00 '),  
('ted' , 125 ,' 2021-12-1 15:14:00 '),  
('ted' , 325 ,' 2021-12-1 18:21:00 '),  
('Mike' , 131 ,' 2021-12-1 20:21:00 '),  
('Mike' , 115 ,' 2021-12-1 10:21:00 '),  
('Mike' , 160 ,' 2021-12-2 20:00:00 '),  
('Mike' , 180 ,' 2021-12-2 22:00:00 ')  
SELECT * FROM #test;  
ALTER TABLE #test ADD identity_no INT;  
GO  
;WITH CTE AS  
(  
 SELECT [user],SUM(amount)AS amount,CONVERT(DATE,datetime) AS DATE,1 as identity_no  
 FROM #test  
 GROUP BY [user],CONVERT(DATE,datetime)  
)  
INSERT INTO #test ([user],amount,[datetime],identity_no)   SELECT * FROM CTE;  
  
DELETE FROM #test WHERE identity_no IS NULL;  
ALTER TABLE #test DROP COLUMN identity_no;  
  
SELECT * FROM #test  

Output:

Best regards,
LiHong

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.