Updating a table with a running sum? 1

[bittmapp]

i have the following two data tables

transaction table:

product             Date             parAmt
--------------------------------------------
pen                 01/01/2006      100
pen                 01/04/2006      -100
pen                 01/06/2006      250

daily table

product             Date             parAmt
--------------------------------------------
pen                 01/01/2006          100
pen                 01/02/2006          100
pen                 01/03/2006          100
pen                 01/04/2006          0
pen                 01/05/2006          0
pen                 01/06/2006          250

Is it possible to update the daily table's parAmt with a running sum by date that results in this...

product         Date         par
--------------------------------------------
pen             01/01/2006      100
pen             01/02/2006      100
pen             01/03/2006      100
pen             01/04/2006      0
pen             01/05/2006      0
pen             01/06/2006      250

Any idea would be greatly appreciated.

Thanks,

bitt

 

[vongrunt]

Running sum for each product separately, right?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bittmapp]

Vongrunt,

Thanks for taking a look. Really appreciate it!

Correct, Yes, for each product

and one last thing. the daily table will have the product and par for every day until today. Even though the transaction table does not have. Like this.

product Date par
--------------------------------------------
pen 01/01/2006 100
pen 01/02/2006 100
pen 01/03/2006 100
pen 01/04/2006 0
pen 01/05/2006 0
pen 01/06/2006 250
pen 01/07/2006 250
pen 01/08/2006 250
pen 01/09/2006 250
pen 01/10/2006 250
etc......

Thanks again,

bitt

 

[vongrunt]

One last question: SQL2000 or 2005?

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bittmapp]

Hi Vongrunt,

To answer your question, SQL 2005.

Thanks again for looking.

bitt

 

[vongrunt]

After second though... SQL2005 features won't change much in this case.

This calculates running sums:

select A.product, A.Date as DateFrom, Sum(B.parAmt) as runningAmt
from [Transaction] A
inner join [Transaction] B on A.product=B.product and A.[Date]>= B.[Date]
group by A.product, A.Date

This resolves upper boundary for date interval [DateFrom, DateTo):

select AB.product, AB.DateFrom, AB.runningAmt, min(C.Date) as DateTo
from
(	<insert 1st query here>
) AB
left outer join [Transaction] C on AB.product=C.product and AB.DateFrom < C.Date
group by AB.product, AB.DateFrom, AB.runningAmt

Finally, this can be projected onto daily table:

-- update D set parAmt = ABC.runningAmt
select D.product, D.Date, ABC.runningAmt
from daily D
left outer join
(	<insert 2nd query here>
) ABC
on D.product=ABC.product and D.Date >= ABC.DateFrom and (D.Date < ABC.DateTo or ABC.DateTo is null)

Test SELECT first. If everything is OK uncomment UPDATE, comment SELECT and ... backup database .

------
[small]select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')[/small]

 

[bittmapp]

Vongrunt, you've done it again!

You have saved my hide on many occasions! Thank you so much.

bitt