# Update a dynamic number of rows with an amount proportionally

Since my English are not perfect I will try explaining with an example

I have tableA that I want to update the Amount

``````groupID reservationID  Date        Amount
26      1              2018-06-01  NULL
26      2              2018-06-01  NULL
26      3              2018-06-01  NULL
27      4              2018-06-04  NULL
27      5              2018-06-04  NULL
``````

And tableB that holds the total amount per Group

``````groupID  Date        Amount
26       2018-06-01      60
27       2018-06-04     100
``````

I trying to find a way that

• groupID 26 amount of 60 can updated the first table groupID26 rows with 20 each (60 / 3)

• groupID 27 amount of 100 can update the first table groupID27 rows with 50 each (100/2)

In other words a query that will be smart enough to divide the total amount in tableB the in the number of rows in tableA where:

``````tableA.Date = tableB.Date
AND tableA.groupId = tableB.groupID
``````

Thanx

Here is a sample code ddl

``````DECLARE @tableA TABLE
(group_id       numeric(10,0)   null,
reservation_id numeric(10,0)   null,
ondate         datetime        null,
amount         numeric(10,2)   null)

DECLARE @tableB TABLE
(group_id       numeric(10,0)   null,
ondate         datetime        null,
amount         numeric(10,2)   null)

INSERT @tableA
SELECT 26,1,'2018-06-01',NULL
INSERT @tableA
SELECT 26,2,'2018-06-01',  NULL
INSERT @tableA
SELECT 26,3,'2018-06-01',  NULL
INSERT @tableA
SELECT 27,4,'2018-06-04',  NULL
INSERT @tableA
SELECT 27,5,'2018-06-04',  NULL

INSERT @tableB
SELECT 26,'2018-06-01',60
INSERT @tableB
SELECT 27,'2018-06-04',100
``````

You can solve it this way:

``````declare @a table
(groupID int, reservationID  int, [Date] date, Amount decimal(10,2));

insert into @a values
(26,      1,              '2018-06-01',  NULL),
(26,      2,              '2018-06-01',  NULL),
(26,      3,              '2018-06-01',  NULL),
(27,      4,              '2018-06-04',  NULL),
(27,      5,              '2018-06-04',  NULL);

declare @b table
(groupID  int, [Date] date,  Amount decimal(10,2));

insert into @b values
(26,       '2018-06-01',      60),
(27,       '2018-06-04',     100);

with cte as
(
select a.*,
b.Amount / count(a.reservationID) over (partition by a.Date, a.groupId) new_amount
from @a a join @b b
on a.Date = b.Date
AND a.groupId = b.groupID
)

update cte
set Amount = new_amount;

select *
from @a;
``````

• I edited my answer and eliminated cast Amount to decimal as you edited your question and Amount is not int Jan 30, 2018 at 10:55
• dear @sepupic it worked marvelously!! One last question. I need to execute this in SQL Server 2005 environments. Is it easy to be converted to non cte? Jan 30, 2018 at 11:41
• SQL Server 2005 does support CTE. If you receive the error, please check if you have ; before CTE and that your database compatibility level is 90 Jan 30, 2018 at 11:58
• I've updated my answer with the image of 2005 (@@version 9.0 sp4) Jan 30, 2018 at 12:02