Update a Field Using Last Rows Fields value

Question

Suppose I have the following table:

CustomerId	Amount	Date	LastMonthDate	SumLastMonthAmount
1	500	20220301	20220201	500
1	200	20220304	20220204	700
1	400	20220320	20220220	1100
1	100	20220329	20220229	1200
1	100	20220402	20220302	800

As you can see, I want to have sum of amount for last month; suppose that SumLastMonth is NULL in the table, how should I update this column?

Note that you can't use date functions at all. and our date columns have int type as you see

The query that I wrote for this task is:

UPDATE A
SET SumLastMonthAmount = (SELECT SUM(Amount) 
                          FROM Table B 
                          WHERE A.CustomerId = B.CustomerId
                            AND B.Date > A.LastMonthDate
                            AND B.Date <= A.Date)                          
FROM Table A
Where A.Date=20220402

But it is very slow. Can you suggest a better query?

Please add number of records in your table and logic behind the updates.. — Nitika, Jun 27 '22 at 12:07
Performance issues are often best approached by examining the _actual execution plan_. Please see [paste the plan](https://www.brentozar.com/pastetheplan/instructions/) for a way to include an execution plan in your question. — HABO, Jun 27 '22 at 12:11
The query is slow, in part, because it is doing an `UPDATE` to every row in the table. If your older data are not changing frequently, you should consider adding a `WHERE` clause to the `UPDATE` to limit the set of rows it operates over — paneerakbari, Jun 27 '22 at 12:13
@Nitika number of records for each day is about 1 million . we want to use amount of last month for each customer in our calculation — nasim_bbb, Jun 27 '22 at 13:03
@paneerakbari Yes You are right. i added where clause for date and update for each date individuali but it is timespending. — nasim_bbb, Jun 27 '22 at 13:05
Does table `B` have an index on 'CustomerId' and `Date` that includes `Amount`, i.e. a [covering index](http://www.dbadiaries.com/sql-server-covering-index-and-key-lookup/)? — HABO, Jun 27 '22 at 13:36
@nasim_bbb please update your question with the new `WHERE` clause you have added. — paneerakbari, Jun 27 '22 at 14:34

score 0 · Answer 1 · edited Jun 28 '22 at 01:29

0

your code is modified as below. if last month is not identified "last Month amount" will be null

with cte as(
    select  A.CustomerId , A.LastMonthDate, A.Date,SUM(B.Amount) as newLastMonthAmount 
    from Table A 
    left Join Table B  
    on A.CustomerId = B.CustomerId 
    AND B.Date > A.LastMonthDate 
    AND B.Date <= A.Date 
    group by A.CustomerId , A.LastMonthDate, A.Date
) 
update A 
set SumLastMonthAmount=B.newLastMonthAmount 
from Table A 
join cte B 
on  A.CustomerId = B.CustomerId 
AND B.Date = A.Date 
AND B.LastMonthDate = A.LastMonthDate

edited Jun 28 '22 at 01:29

Shmiel

1,201
10
25

answered Jun 27 '22 at 12:38

Upender Reddy

568
3
8

Use `join on where` instead of `join on and`. For more refer to [this post](https://stackoverflow.com/a/2722834/16019058) – Shmiel Jun 27 '22 at 14:26
Used left join bcz he need not matched records to be null so if use where condition that can't be possible – Upender Reddy Jun 27 '22 at 15:35
`AND B.Date > A.LastMonthDate AND B.Date <= A.Date` should be `where B.Date > A.LastMonthDate AND B.Date <= A.Date` – Shmiel Jun 27 '22 at 15:37

Update a Field Using Last Rows Fields value

1 Answers1