Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle 11 - side by side data different date criteria

Posted on 2011-03-07
7
Medium Priority
?
278 Views
Last Modified: 2012-05-11
Hello

My original query ..
select
col1,
col2,
date_year
from table1
where date_year = '2009'

Result set

col1  col2   date_year
a      12      2009
b      14      2009

What I now want to achieve is another result set to the right but with
previous year data - I do not want to hard code this bit - how can I do this.
I only want to parameterize the year 2009
So, my result set would now be...
col1  col2   date_year  col1a  col1b     date_year
a      12      2009      a      45         2008
b      14      2009      b      16         2008
0
Comment
Question by:philsivyer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
7 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35058827
What is the correlation?

What 2009 colb goes with what 2008 colb?

More sample data and expected results would help.
0
 

Author Comment

by:philsivyer
ID: 35058959
Hope this makes sense..
Query 1
select
col1,
col2,
date_year
from table1
where date_year = '2009'

Result set....
col1  col2   date_year
a      12      2009
b      14      2009
..............................
Query 2
select
col1,
col2,
date_year
from table1
where date_year = '2008'
Result set....
col1  col2   date_year
a      15.5    2008
b      11      2008

So, I would like one query which could return the following data

col1  col2   date_year col1(1) col2(1)  date_year(1)
a      12      2009     a       15.5      2008
b      14      2009     b       11        2008
0
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35059001
So for col1 there is always a one-to-one correlation across years?

On other words: for col1='a', there is only EVER a single row per year.
0
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

Author Comment

by:philsivyer
ID: 35059017
Correct
0
 
LVL 77

Accepted Solution

by:
slightwv (䄆 Netminder) earned 2000 total points
ID: 35059159
Try this
drop table tab1 purge;
create table tab1 (col1 char(1), col2 number, datE_year char(4));

insert into tab1 values('a',12,'2009');
insert into tab1 values('b',14,'2009');
insert into tab1 values('a',15.5,'2008');
insert into tab1 values('b',11,'2008');
commit;


select col1, col2, date_year, prev_col1, prev_col2, prev_year
from
(
select col1, col2, date_year,
	lead(col1) over(partition by col1 order by col1, date_year desc) prev_col1,
	lead(col2) over(partition by col1 order by col1, date_year desc) prev_col2,
	lead(date_year) over(partition by col1 order by date_year desc) prev_year
from tab1
)
where date_year = '2009'
/

Open in new window

0
 

Author Comment

by:philsivyer
ID: 35069048
Thanks - this works.
0
 

Author Closing Comment

by:philsivyer
ID: 35069050
Thanks
0

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction A previously published article on Experts Exchange ("Joins in Oracle", http://www.experts-exchange.com/Database/Oracle/A_8249-Joins-in-Oracle.html) makes a statement about "Oracle proprietary" joins and mixes the join syntax with gen…
Background In several of the companies I have worked for, I noticed that corporate reporting is off loaded from the production database and done mainly on a clone database which needs to be kept up to date daily by various means, be it a logical…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
This video shows how to recover a database from a user managed backup

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question