Welcome to dbFreaks.com!
FAQFAQ      ProfileProfile    Private MessagesPrivate Messages   Log inLog in

SQL help

  
   Database Help (Home) -> DB2 RSS
Next:  subreport bottom border spacing  
Author Message
annecarterfredi

External


Since: Feb 21, 2008
Posts: 8



(Msg. 1) Posted: Tue Oct 07, 2008 11:36 am
Post subject: SQL help
Archived from groups: comp>databases>ibm-db2 (more info?)
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_TASKS_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.

 >> Stay informed about: SQL help 
Back to top
Login to vote
annecarterfredi

External


Since: Feb 21, 2008
Posts: 8



(Msg. 2) Posted: Tue Oct 07, 2008 11:39 am
Post subject: Re: SQL help [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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....TakeThisOut@gmail.com> wrote:
> 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_TASKS_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.

 >> Stay informed about: SQL help 
Back to top
Login to vote
ChrisC

External


Since: May 11, 2007
Posts: 10



(Msg. 3) Posted: Tue Oct 07, 2008 1:46 pm
Post subject: Re: SQL help [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
> > PENDING_TASKS should be calculated as (NUMBER_OF_TASKS_CREATED -
> > NUMBER_OF_TASKS_CLOSED + PENDING_TASKS from Previous day).

One way to look at this is to use OLAP functions, specifically
SUM(...) over (ORDER BY ...). This construct will give you a running
total over the rows you are returning - that is, adding the values
from the previous rows with the value from the current row to return.

-Chris
 >> Stay informed about: SQL help 
Back to top
Login to vote
Lennart

External


Since: Jan 11, 2008
Posts: 124



(Msg. 4) Posted: Tue Oct 07, 2008 9:11 pm
Post subject: Re: SQL help [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
On 7 Okt, 20:36, "annecarterfr...@gmail.com"
<annecarterfr....RemoveThis@gmail.com> wrote:
> 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_TASKS_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 Smile

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
 >> Stay informed about: SQL help 
Back to top
Login to vote
--CELKO--

External


Since: Jan 11, 2008
Posts: 976



(Msg. 5) Posted: Wed Oct 08, 2008 9:37 am
Post subject: Re: SQL help [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
"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
 >> Stay informed about: SQL help 
Back to top
Login to vote
annecarterfredi

External


Since: Feb 21, 2008
Posts: 8



(Msg. 6) Posted: Wed Oct 08, 2008 10:23 am
Post subject: Re: SQL help [Login to view extended thread Info.]
Archived from groups: per prev. post (more info?)
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....DeleteThis@earthlink.net> wrote:
> "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
 >> Stay informed about: SQL help 
Back to top
Login to vote
Display posts from previous:   
Related Topics:
DB2 UDB LUW 8.2:clp 32 bit connect to 64 bit engine - Environment: DB2 UDB LUW (AIX) 8.2 Database instance: test migration to 64 bit Clients: 32 bit, embedded sql, db2 clp When PATH/LIBPATH point to the 32 lib environment (/usr/opt/db2_08_01/bin and (/usr/opt/db2_08_01/lib) db2..

How to reterive the last 5 records in a table? - Hi I have a table which contains number of rows. I want to fetch the last 5 records from the table. I know for the first 'n' records we can use FETCH FIRST n ROWS ONLY command. But i want to fetch last 5 records. Can any one help in this... ..

&quot;FIND&quot; command is narrowed - In the ISPF editor I am using, for a particullar PO Dataset I am getting the result of FIND statement narrowed down to the colums 48-56. i.e. if I give "FIND 'TO' ALL", the result I am getting as "CHARS 'TO' - found 239 times within colum...

Schema mapper - As do all such projects, my latest venture into DB design has grown as the user becomes more educated to his needs, the design process and the capabilities of those involved. I am looking for a tool which I can point to a db2 dub schema and get a..

Sample package - Hi All, I want to create a very simple db2 package and bind it . The test is to bind multiple versions of a package. So I want to first create a package ,bind it and then modify it to create another version . Any ideas about how to complete this..
   Database Help (Home) -> DB2 All times are: Pacific Time (US & Canada) (change)
Page 1 of 1

 
 You can post new topics in this forum
You can reply to topics in this forum
You can edit your posts in this forum
You can delete your posts in this forum
You can vote in polls in this forum



[ Contact us | Terms of Service/Privacy Policy ]