updating daily table with a running amount from another 2

[dinzana]

Hello All and thank you in advance for your input. I am trying to update the tblDailyTEST table (dailyAmount column)with the appropriate value from the tblRunningAmount table using SQL. Any ideas? the resultant table is the third example below. thanks again!


tblRunningAmount

accountId date_id runningAmount
--------------------------------------------------------------------------

123 2005-11-15 00:00:00.000 60000.00
123 2007-02-05 00:00:00.000 43000.00
123 2007-02-15 00:00:00.000 666.00
123 2007-02-18 00:00:00.000 777.00
123 2007-02-19 00:00:00.000 999.00

tblDailyTEST

accountId date_id dailyAmount
--------------------------------------------------------------------------

123 2007-02-01 00:00:00.000 NULL
123 2007-02-02 00:00:00.000 NULL
123 2007-02-03 00:00:00.000 NULL
123 2007-02-04 00:00:00.000 NULL
123 2007-02-05 00:00:00.000 NULL
123 2007-02-06 00:00:00.000 NULL
123 2007-02-07 00:00:00.000 NULL
123 2007-02-08 00:00:00.000 NULL
123 2007-02-09 00:00:00.000 NULL
123 2007-02-10 00:00:00.000 NULL
123 2007-02-11 00:00:00.000 NULL
123 2007-02-12 00:00:00.000 NULL
123 2007-02-13 00:00:00.000 NULL
123 2007-02-14 00:00:00.000 NULL
123 2007-02-15 00:00:00.000 NULL
123 2007-02-16 00:00:00.000 NULL
123 2007-02-17 00:00:00.000 NULL
123 2007-02-18 00:00:00.000 NULL
123 2007-02-19 00:00:00.000 NULL
123 2007-02-20 00:00:00.000 NULL
123 2007-02-21 00:00:00.000 NULL
123 2007-02-22 00:00:00.000 NULL
123 2007-02-23 00:00:00.000 NULL
123 2007-02-24 00:00:00.000 NULL
123 2007-02-25 00:00:00.000 NULL
123 2007-02-26 00:00:00.000 NULL
123 2007-02-27 00:00:00.000 NULL
123 2007-02-28 00:00:00.000 NULL

Result:
accountId date_id dailyAmount
--------------------------------------------------------------------------

123 2007-02-01 00:00:00.000 60000.00
123 2007-02-02 00:00:00.000 60000.00
123 2007-02-03 00:00:00.000 60000.00
123 2007-02-04 00:00:00.000 60000.00
123 2007-02-05 00:00:00.000 43000.00
123 2007-02-06 00:00:00.000 43000.00
123 2007-02-07 00:00:00.000 43000.00
123 2007-02-08 00:00:00.000 43000.00
123 2007-02-09 00:00:00.000 43000.00
123 2007-02-10 00:00:00.000 43000.00
123 2007-02-11 00:00:00.000 43000.00
123 2007-02-12 00:00:00.000 43000.00
123 2007-02-13 00:00:00.000 43000.00
123 2007-02-14 00:00:00.000 43000.00
123 2007-02-15 00:00:00.000 666.00
123 2007-02-16 00:00:00.000 666.00
123 2007-02-17 00:00:00.000 666.00
123 2007-02-18 00:00:00.000 777.00
123 2007-02-19 00:00:00.000 999.00
123 2007-02-20 00:00:00.000 999.00
123 2007-02-21 00:00:00.000 999.00
123 2007-02-22 00:00:00.000 999.00
123 2007-02-23 00:00:00.000 999.00
123 2007-02-24 00:00:00.000 999.00
123 2007-02-25 00:00:00.000 999.00
123 2007-02-26 00:00:00.000 999.00
123 2007-02-27 00:00:00.000 999.00
123 2007-02-28 00:00:00.000 999.00

 

[gmmastros]

What have you tried so far?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Have you made any attempt at this yet?

I've got a working version of what you want, but I would like to know if you at least attempted this.

<.

 

[dinzana]

Sure thing, Monksnake. I think that I have it figured out. Here is what I've got so far.

update tblDailyTEST
set dailyAmount = q2.runningAmount
from tblDailyTEST a
join (select x.accountId,
c.date_id,
x.runningAmount
from (select a.accountId,
a.date_id,
a.runningAmount,
min(isnull(b.date_id-1, a.date_id)) as date_id2
from tblRunningAmount A
left outer join tblRunningAmount b
on a.accountId=b.accountId
and a.date_id < b.date_id
group by a.accountId,
a.date_id,
a.runningAmount) x
INNER JOIN tblDailyTEST c
ON c.date_id BETWEEN x.date_id AND x.date_id2) q2
ON a.accountId = q2.accountId
and a.date_id = q2.date_id

thanks,

 

[monksnake]

Here's what I ended up with:

declare @tblDailyTEST Table(accountId int, date_id datetime, dailyAmount decimal(8, 2))
insert into @tblDailyTEST values (123, '2007-02-01 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-04 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-05 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-06 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-07 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-08 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-09 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-10 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-11 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-12 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-13 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-14 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-15 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-16 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-17 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-18 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-19 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-20 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-21 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-22 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-23 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-24 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-25 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-26 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-27 00:00:00.000', NULL)
insert into @tblDailyTEST values (123, '2007-02-28 00:00:00.000', NULL)


Declare @tblRunningAmount Table(accountId int, date_id datetime, runningAmount decimal(8, 2))

insert into @tblRunningAmount values (123, '2005-11-15 00:00:00.000', 60000.00)
insert into @tblRunningAmount values (123, '2007-02-05 00:00:00.000', 43000.00)
insert into @tblRunningAmount values (123, '2007-02-15 00:00:00.000', 666.00)
insert into @tblRunningAmount values (123, '2007-02-18 00:00:00.000', 777.00)
insert into @tblRunningAmount values (123, '2007-02-19 00:00:00.000', 999.00)

select a.accountId, a.date_id, (select runningAmount from @tblRunningAmount where date_id = (select max(date_id) thisDate from @tblRunningAmount where date_id <= a.date_id)) from @tblDailyTEST a

update @tblDailyTEST set dailyAmount = (select runningAmount from @tblRunningAmount where date_id = (select max(date_id) thisDate from @tblRunningAmount where date_id <= a.date_id)) from @tblDailyTEST a

select * from @tblDailyTEST

<.

 

[dinzana]

Thanks so much for your ideas Monksnake. Yours is certainly more elegant a solution.

Thanks again, I hope someday to be able to reciprocate.

-dinzana

 

[gmmastros]

It's also better than what I came up with. So... have another purply from me.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[monksnake]

Thank you very much, both of you.

<.