Prev: SQL0902C on db2 connect
Next: DDL file usage
From: Serge Rielau on 1 Dec 2009 08:12
Nice! I wasn't aware of this function. Is it new?


--
Serge Rielau
SQL Architect DB2 for LUW
IBM Toronto Lab
From: Tonkuma on 1 Dec 2009 08:47
On Dec 1, 10:12 pm, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> Nice! I wasn't aware of this function. Is it new?
>
> --
> Serge Rielau
> SQL Architect DB2 for LUW
> IBM Toronto Lab
I saw it in DB2 9.1 for LUW.
But, I realized recently usage of the function in this context.
From: portaldev on 2 Dec 2009 13:57
Great code and thanks Tonkuma and Serge for your responses. Much
appreciated.


From: portaldev on 2 Dec 2009 14:57
the problem in detail:
distribute an amount over a month, the amount can change.

table "start": year, month, monthly_total

table "end": date, num_of_items, monthly_running_total

I select a row from "start" and insert a month of data into "end".
I do this daily, for the current date until a year later.

For the current date I know:
items_left = monthly_total - the previous days monthly_running_total
num_of_items = ceiling( items_left / days_left )

For the current date + 1 day I know:
items_left = previous days items_left - previous days num_of_items
num_of_items = ceiling( items_left / days_left )
From: Tonkuma on 2 Dec 2009 17:27
On Dec 3, 4:57 am, portaldev <barrybe...(a)gmail.com> wrote:
> the problem in detail:
> distribute an amount over a month, the amount can change.
>
> table "start": year, month, monthly_total
>
> table "end": date, num_of_items, monthly_running_total
>
> I select a row from "start" and insert a month of data into "end".
If today is '2009-12-03', do you want to insert into "end" from
'2009-12-03' to '2009-12-31'?

> I do this daily, for the current date until a year later.
"I do this daily"?
On '2009-12-03', insert into "end" from '2009-12-03' to '2009-12-31'?
On '2009-12-04', insert into "end" from '2009-12-04' to '2009-12-31'?
On '2009-12-05', insert into "end" from '2009-12-05' to '2009-12-31'?
So on....
Duplicate date in "end"?

"until a year later"? Does it contradict "a month of data"?

>
> For the current date I know:
> items_left = monthly_total - the previous days monthly_running_total
> num_of_items  = ceiling( items_left / days_left )
"items_left" is not in tables.
So, do you want to calculete "items_left" for the calculation of
"num_of_items" only?
In other word, is it a temporary result and throw away after
calculation of "num_of_items"?

What is "days_left"?

I couldn't find a definition of it.

If "days_left" is zero, what result of "ceiling( items_left /
days_left )" do you expect?

How to calculate "monthly_running_total"?

>
> For the current date + 1 day I know:
> items_left = previous days items_left - previous days num_of_items
> num_of_items = ceiling( items_left / days_left )
From whare do you get "previous days items_left"?
"items_left" is not kept in tables "end" or "start".

"For the current date + 2 day" is this right?
items_left = **previous days** items_left - **previous days**
num_of_items
Replace **previous days** with current date + 1 day.

> num_of_items = ceiling( items_left / days_left )



First  |  Prev  |  Next  |  Last
Pages: 1 2 3
Prev: SQL0902C on db2 connect
Next: DDL file usage