SQL help

[annecarterfredi@gmail.com]

I have a resultset like this:

DATE NUMBER_OF_TASKS _CLOSED NUMBER_OF_TASKS _CREATED
PENDING_TASKS
----------
----------------------
----------------------- -------------
10/31/2007 1591
1723 132
11/01/2007 1821
4511 2690
11/02/2007 2465
5530 3065
11/03/2007 1028
1916 888
11/04/2007
1
1 0


PENDING_TASKS should be calculated as (NUMBER_OF_TASK S_CREATED -
NUMBER_OF_TASKS _CLOSED + PENDING_TASKS from Previous day).

While I can calculate NUMBER_OF_TASKS _CREATED -
NUMBER_OF_TASKS _CLOSED easily(which is shown in the resultset above),
I don't know how to add " PENDING_TASKS from Previous day". Pls help.

Thanks.

[annecarterfredi@gmail.com]

Re: SQL help

DATE NUMBER_OF_TASKS _CLOSED NUMBER_OF_TASKS _CREATED PENDING_TASKS
---------- ---------------------- -----------------------
-------------
10/31/2007 1591 1723
132
11/01/2007 1821 4511
2690
11/02/2007 2465 5530
3065
11/03/2007 1028 1916
888
11/04/2007 1 1
0

On Oct 7, 2:36 pm, "annecarterfr.. .@gmail.com"
<annecarterfr.. .@gmail.comwrot e:

I have a resultset like this:
>
DATE         NUMBER_OF_TASKS _CLOSED     NUMBER_OF_TASKS _CREATED
PENDING_TASKS
----------
----------------------
-----------------------                                 -------------
10/31/2007                   1591
1723                                           132
11/01/2007                   1821
4511                                           2690
11/02/2007                   2465
5530                                           3065
11/03/2007                   1028
1916                                           888
11/04/2007
1
1                                                0
>
PENDING_TASKS should be calculated as (NUMBER_OF_TASK S_CREATED -
NUMBER_OF_TASKS _CLOSED + PENDING_TASKS from Previous day).
>
While  I can calculate NUMBER_OF_TASKS _CREATED -
NUMBER_OF_TASKS _CLOSED easily(which is shown in the resultset above),
I don't know how to add " PENDING_TASKS from Previous day". Pls help.
>
Thanks.

[Lennart]

Re: SQL help

On 7 Okt, 20:36, "annecarterfr.. .@gmail.com"
<annecarterfr.. .@gmail.comwrot e:

I have a resultset like this:
>
DATE         NUMBER_OF_TASKS _CLOSED     NUMBER_OF_TASKS _CREATED
PENDING_TASKS
----------
----------------------
-----------------------                                 -------------
10/31/2007                   1591
1723                                           132
11/01/2007                   1821
4511                                           2690
11/02/2007                   2465
5530                                           3065
11/03/2007                   1028
1916                                           888
11/04/2007
1
1                                                0
>
PENDING_TASKS should be calculated as (NUMBER_OF_TASK S_CREATED -
NUMBER_OF_TASKS _CLOSED + PENDING_TASKS from Previous day).
>
While  I can calculate NUMBER_OF_TASKS _CREATED -
NUMBER_OF_TASKS _CLOSED easily(which is shown in the resultset above),
I don't know how to add " PENDING_TASKS from Previous day". Pls help.
>
Thanks.

Assuming your current query looks like (it is often a good idea to
post ddl + what you have achieved so far :-)

select DATE,
sum(x) as NUMBER_OF_TASKS _CLOSED,
sum(y) as NUMBER_OF_TASKS _CREATED,
(select sum(z) from T T2 where T2.date = T1.DATE - 1 day)
as PENDING_TASKS
from T T1 group by DATE

HTH
/Lennart

[--CELKO--]

Re: SQL help

"A problem well stated is a problem half solved." -- Charles F.
Kettering

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules. Temporal data should use ISO-8601
formats. Code should be in Standard SQL as much as possible and not
local dialect.

Sample data is also a good idea, along with clear specifications. It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:

http://www.catb.org/~esr/faqs/smart-questions.html

[annecarterfredi@gmail.com]

Re: SQL help

Thanks for your reply. I have found the solution after some research.
It's similar to Lennart's solution.

On Oct 8, 12:37 pm, --CELKO-- <jcelko...@eart hlink.netwrote:

"A problem well stated is a problem half solved." -- Charles F.
Kettering
>
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. If you know how, follow ISO-11179 data element naming
conventions and formatting rules.  Temporal data should use ISO-8601
formats.  Code should be in Standard SQL as much as possible and not
local dialect.
>
Sample data is also a good idea, along with clear specifications.  It
is very hard to debug code when you do not let us see it. If you want
to learn how to ask a question on a Newsgroup, look at:http://www.catb.org/~esr/faqs/smart-questions.html