Create dummy records using Model clause with condition for date field [message #638014] |
Mon, 01 June 2015 06:23 |
|
OraFerro
Messages: 433 Registered: July 2011
|
Senior Member |
|
|
Hi All,
I have the two tables as below:
create table Interest_date
(
interest_date_field date,
Interest_amount number
);
create table sys_date
(type varchar2(30),
date_value date
);
insert all
INTO Interest_date values (to_date('01-11-2012','DD-MM-YYYY'), 20)
INTO Interest_date values (to_date('01-12-2012','DD-MM-YYYY'), 40)
INTO Interest_date values (to_date('02-01-2013','DD-MM-YYYY'), 60)
INTO Interest_date values (to_date('01-06-2013','DD-MM-YYYY'), 80)
INTO Interest_date values (to_date('01-07-2014','DD-MM-YYYY'), 10)
INTO Interest_date values (to_date('25-11-2014','DD-MM-YYYY'), 20)
INTO SYS_DATE VALUES ('FIRST_DATE',TO_DATE('01-06-1900','DD-MM-YYYY')) --note: year is not important but date is used for other types
INTO SYS_DATE VALUES ('FIRST_DATE',TO_DATE('01-12-1900','DD-MM-YYYY')) --note: year is not important but date is used for other types
select * from dual;
Requirement:
I need to write a select statement that retrieves all records from interest_date and add a dummy record according to the following:
- in case NONE of interest_date_field DD-MM for a certain year (group by year) = DD-MM of the date_value in sys_date where type is 'FIRST DATE' -> add a dummy record (date_value for type FIRST_DATE with YYYY = interest_date_field year, -1)
- in case NONE of interest_date_field DD-MM for a certain year (group by year) = DD-MM of the date_value in sys_date where type is 'SECOND DATE' -> add a dummy record (date_value for type SEOND_DATE with YYYY = interest_date_field year, -1)
Desired output:
interest_date_field Interest_amount
01-06-2012 -1
01-11-2012 20
01-12-2012 40
02-01-2013 60
01-06-2013 80
01-12-2013 -1
01-06-2014 -1
01-07-2014 10
25-11-2014 20
01-12-2014 -1
Many thanks,
Ferro
|
|
|
|
Re: Create dummy records using Model clause with condition for date field [message #638116 is a reply to message #638098] |
Tue, 02 June 2015 09:54 |
bugfox
Messages: 18 Registered: October 2010
|
Junior Member |
|
|
using model clause:
select to_date(interest_date_field||'-'||interest_year, 'DD-MM-YYYY'),
interest_amount
from interest_date
model
dimension by (to_char(interest_date_field, 'DD-MM') interest_date_field,
to_char(interest_date_field, 'YYYY') interest_year)
measures (interest_amount)
rules upsert all(
interest_amount[for interest_date_field in (select distinct to_char(date_value, 'DD-MM') from sys_date), any] = nvl(interest_amount[cv(), cv()], -1))
order by 1
just sql:
select nvl(interest_dt, interest_date_field) interest_date_field,
nvl(interest_amount, -1) interest_amount
from (select date_value,
to_date(to_char(date_value, 'DD-MM')||'-'||interest_date_year, 'DD-MM-YYYY') interest_dt
from sys_date,
(select distinct to_char(interest_date_field, 'YYYY') interest_date_year
from interest_date))
full join interest_date on interest_date_field = interest_dt
order by 1
|
|
|
|