Re: Analytical query help

From: Charles Hooper <hooperc2000_at_yahoo.com>
Date: Mon, 7 Dec 2009 19:32:45 -0800 (PST)
Message-ID: <753a0aa0-058f-4b4c-8490-47e5d6e3bb56_at_2g2000prl.googlegroups.com>

On Dec 7, 4: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

You might want to take a look at this link, which shows how to supply the missing dates:
http://hoopercharles.wordpress.com/2009/12/07/sql-filling-in-gaps-in-the-source-data/

The LAG or LEAD analytic functions may be used to "peek" at the previous/next row if the SUM() column produces a NULL value: http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/functions070.htm

You might also want to look through this thread if the LAG analytic function does not work - I think that I had a couple interesting posts in that thread that involved peeking at previous rows: http://groups.google.com/group/comp.databases.oracle.server/browse_thread/thread/de1036e471dba004

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Dec 07 2009 - 21:32:45 CST