Re: This year vs Last year queries

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Mon, 20 Jul 1998 20:27:28 GMT
Message-ID: <35b6a67a.21339444@192.86.155.100>

A copy of this was sent to Kevin Skaalrud <kevin_skaalrud_at_neptune.serca.com> (if that email address didn't require changing) On Mon, 20 Jul 1998 11:50:12 -0700, you wrote:

>I suspect this is the wrong group for this but here goes.
>
>We have oracle 7.3x running (but I suspect this is a generic
>SQL question)
>
>I need to do this year vs last year queries. I can't do it
>with outer joins because either some of this year or some
>of last year (new sales or last sales) will not show up (left or right
>outer joins)
>
>I don't think I can use cursors because I can only issue
>one request to return a table of rows that include both
>this year and last year data, i.e.:
>
>Custno, desc, TY sales, LY sales, Diff Sales etc.
>
>Anybody have any thoughts?
>

assuming the table looks something like:

create table T ( custno number, descript varchar2, sale_amount number, sale_date );

Then a query such as:

select custno, descript, sum(ty_sales) ty_sales, sum(ly_sales) ly_sales,

                         sum(ty_sales-ly_sales) diff

  from
(
select custno, descript, sale_amount ty_sales, 0 ly_sales   from T
where sale_date between to_date( '....' ) and to_date( '....' ) union all
select custno, descript, 0, sale_amount   from T
where sale_date between to_date( '....' ) and to_date( '....' ) )
group by custno, descript
/

will do it as long as the descript field is constant for all of the same values of custno...

>Thanks in advance.
>
>Kevin Skaalrud

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government
Herndon VA

http://govt.us.oracle.com/ -- downloadable utilities

Opinions are mine and do not necessarily reflect those of Oracle Corporation

Anti-Anti Spam Msg: if you want an answer emailed to you, you have to make it easy to get email to you. Any bounced email will be treated the same way i treat SPAM-- I delete it. Received on Mon Jul 20 1998 - 15:27:28 CDT