Re: Analytical query help

From: joel garry <joel-garry_at_home.com>
Date: Mon, 7 Dec 2009 13:34:46 -0800 (PST)
Message-ID: <1c5781fa-0823-4dc7-b38f-bbbefdc5731b_at_z4g2000prh.googlegroups.com>

On Dec 7, 1:01 pm, RA <ang..._at_gmail.com> wrote:
> Hi gurus,
>
> Could anyone show me how to write a analytical query for my
> requirement.
>
> Query
> ============
> SELECT distinct bug_when,
>        login_name,
>        SUM(incoming_count - outgoing_count)
>         OVER (PARTITION BY login_name ORDER BY bug_when) AS OPEN
> FROM RM_COMP_INCOM_OUTGO_BUGIDS_GT G,
>      bz_components c,
>      bz_profiles p
> where  G.component_id= c.ID and
> c.manager = p.userid
> order by 2,1 desc;
>
> Query returns data like this
> ===================
> 12/04/2009 ssmit 52
> 12/02/2009 ssmit 48
> 11/30/2009 ssmit 45
> 11/29/2009 ssmit 42
>
> I want the data to be like this
> ======================
> 12/04/2009 ssmit 52
> 12/03/2009 ssmit 48 -- fill the gap with previous value
> 12/02/2009 ssmit 48
> 12/01/2009 ssmit 45
> 11/30/2009 ssmit 45
> 11/30/2009 ssmit 45
> 11/29/2009 ssmit 42
>
> Table Desc
> ==========
> CREATE GLOBAL TEMPORARY TABLE RM_COMP_INCOM_OUTGO_BUGIDS_GT
> (
>   BUG_WHEN        DATE,
>   COMPONENT_ID    NUMBER,
>   INCOMING_COUNT  NUMBER,
>   OUTGOING_COUNT  NUMBER
> );
>
> Regards
> RA

One way is to outer join with the dates: http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/analysis.htm#i1014934 http://www.oracle.com/technology/obe/obe10gdb/bidw/outerjoin/outerjoin.htm

jg

--
_at_home.com is bogus.
http://www.signonsandiego.com/news/2009/dec/07/huge-us-visa-fraud-scheme-broken-up-in-brazil/

Received on Mon Dec 07 2009 - 15:34:46 CST