0

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
2

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;

enter image description here

  • I edited my answer and eliminated cast Amount to decimal as you edited your question and Amount is not int – sepupic Jan 30 '18 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? – PanosPlat Jan 30 '18 at 11:41
  • 1
    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 – sepupic Jan 30 '18 at 11:58
  • 1
    I've updated my answer with the image of 2005 (@@version 9.0 sp4) – sepupic Jan 30 '18 at 12:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.