Home » SQL & PL/SQL » SQL & PL/SQL » Replacing a Null column value with the value of the above record (Oracle 10.2.0.1.0, Solaris)
Replacing a Null column value with the value of the above record [message #386187] |
Fri, 13 February 2009 01:18  |
mvmdas
Messages: 6 Registered: February 2009
|
Junior Member |
|
|
I am having a customer_status table, which has got the following columns and also given the records in it.
customer_id from_date Status
999 09/27/2008 200
999 10/01/2008 300
999 10/02/2008 400
999 11/05/2008 820
These records tells that the customer was in,
status 200 from 09/27/2008 to 09/30/2008
status 300 on 10/01/2008
status 400 from 10/02/2008 to 11/04/2008
status 820 from 11/05/2008 onwards
I want to generate a result set for a period of 09/27/2008 to 11/05/2008. So first I tried to generate dates dynamicaly using the dual table. And using an outer join I tried to join the dates with the customer_status table.
select customer_id c_id,nvl(FROM_DATE,DY_DATE),STATUS
from (SELECT to_date('09/27/2008','mm/dd/yyyy')+rownum DY_DATE
FROM DUAL
CONNECT BY LEVEL <= 40)
left join customer_status on DY_DATE=FROM_DATE
and customer_id=999
My problem is that. This query will return only the following resultset.
c_id from_date Status
---------------------------
999 09/27/2008 200
999 09/28/2008
999 09/29/2008
999 09/30/2008
999 10/01/2008 300
999 10/02/2008 400
999 10/03/2008
999 10/04/2008
999 10/05/2008
999 10/06/2008
999 10/07/2008
999 10/08/2008
999 10/09/2008
999 10/10/2008
999 10/11/2008
999 10/12/2008
999 10/13/2008
999 10/14/2008
999 10/15/2008
999 10/16/2008
999 10/17/2008
999 10/18/2008
999 10/19/2008
999 10/20/2008
999 10/21/2008
999 10/22/2008
999 10/23/2008
999 10/24/2008
999 10/25/2008
999 10/26/2008
999 10/27/2008
999 10/28/2008
999 10/29/2008
999 10/30/2008
999 10/31/2008
999 11/01/2008
999 11/02/2008
999 11/03/2008
999 11/04/2008
999 11/05/2008 820
where as I need the status of the customer to be returned for other dates as well based on the date range, i.e.,
status 200 from 09/27/2008 to 09/30/2008
status 300 on 10/01/2008
status 400 from 10/02/2008 to 11/04/2008
status 820 from 11/05/2008 onwards
So the result set I want is given below.
c_id from_date Status
---------------------------
999 09/27/2008 200
999 09/28/2008 200
999 09/29/2008 200
999 09/30/2008 200
999 10/01/2008 300
999 10/02/2008 400
999 10/03/2008 400
999 10/04/2008 400
999 10/05/2008 400
999 10/06/2008 400
999 10/07/2008 400
999 10/08/2008 400
999 10/09/2008 400
999 10/10/2008 400
999 10/11/2008 400
999 10/12/2008 400
999 10/13/2008 400
999 10/14/2008 400
999 10/15/2008 400
999 10/16/2008 400
999 10/17/2008 400
999 10/18/2008 400
999 10/19/2008 400
999 10/20/2008 400
999 10/21/2008 400
999 10/22/2008 400
999 10/23/2008 400
999 10/24/2008 400
999 10/25/2008 400
999 10/26/2008 400
999 10/27/2008 400
999 10/28/2008 400
999 10/29/2008 400
999 10/30/2008 400
999 10/31/2008 400
999 11/01/2008 400
999 11/02/2008 400
999 11/03/2008 400
999 11/04/2008 400
999 11/05/2008 820
Can you please help me modify the query to get the above resultset.
|
|
|
|
Re: Replacing a Null column value with the value of the above record [message #386198 is a reply to message #386187] |
Fri, 13 February 2009 01:52   |
 |
MarcS
Messages: 312 Registered: March 2007 Location: Antwerp
|
Senior Member |
|
|
Let's be a bit creative on friday the 13th
WITH data AS
(
-- Mock the data
SELECT 999 as customer_id ,
TO_DATE('09/27/2008','mm/dd/yyyy') as from_date,
200 as status
FROM DUAL
UNION
SELECT 999 as customer_id ,
TO_DATE('10/01/2008','mm/dd/yyyy') as from_date,
300 as status
FROM DUAL
UNION
SELECT 999 as customer_id ,
TO_DATE('10/02/2008','mm/dd/yyyy') as from_date,
400 as status
FROM DUAL
UNION
SELECT 999 as customer_id ,
TO_DATE('11/05/2008','mm/dd/yyyy') as from_date,
820 as status
FROM DUAL
) ,
date_ranges AS
(
-- We're missing the end dates: fix this with LEAD
SELECT customer_id ,
from_date ,
LEAD(from_date,1,SYSDATE)
OVER ( ORDER BY from_date ) as end_date ,
status
FROM data
) ,
date_range AS
(
-- Get the min and max to use further on
SELECT MIN( from_date ) min_date ,
MAX( from_date ) max_date
FROM date_ranges
) ,
date_list AS
(
-- Generate all days between min and max
SELECT min_date + rownum - 1 a_date
FROM date_range
CONNECT BY LEVEL <= max_date - min_date + 1
)
SELECT dl.a_date ,
dr.status
FROM date_list dl ,
date_ranges dr
WHERE dl.a_date >= dr.from_date
AND dl.a_date < dr.end_date
You can do two things:
- Copy and modify with the proper names, but you'll learn nothing from it
- Print this out and build your own query from scratch and see what happens
And I'm sure my example can be written far more better, but it's a step-by-step approach.
|
|
|
|
|
Re: Replacing a Null column value with the value of the above record [message #386296 is a reply to message #386198] |
Fri, 13 February 2009 08:22   |
mvmdas
Messages: 6 Registered: February 2009
|
Junior Member |
|
|
I am a novice in analytic functions. I understood how the Lead() function is working. Out of curiosity I tried to find the status of all the customers on a given day, let's say 15th of October 2008. Customer_status table will be having at least one record corresponding to a customer_mst record. I have used the query given below. I know in this case probably we don't have to generate a range of dates. I tried various options, but haven't succeeded in getting this one right. Can you please let me know what is that I am missing in this query?
WITH data AS
(
-- Show all customers
SELECT customer_id as customer_id ,
from_date as from_date,
status as status
FROM customer_status cs,customer_mst cm
where cm.customer_id=cs.customer_id
) ,
date_ranges AS
(
-- We're missing the end dates: fix this with LEAD
SELECT customer_id ,
from_date ,
LEAD(from_date,1,SYSDATE)
OVER ( ORDER BY from_date ) as end_date ,
status
FROM data
) ,
date_range AS
(
-- Get the min and max to use further on
SELECT TO_DATE ('08/10/2008', 'mm/dd/yyyy') min_date ,
trunc(sysdate) max_date
FROM dual
) ,
date_list AS
(
-- Generate all days between min and max
SELECT min_date + rownum - 1 a_date
FROM date_range
CONNECT BY LEVEL <= max_date - min_date + 1
)
SELECT dl.a_date ,
dr.status,
dr.customer_id
FROM date_list dl ,
date_ranges dr
WHERE dl.a_date >= dr.from_date
AND dl.a_date < dr.end_date
AND dl.a_date=TO_DATE ('10/15/2008', 'mm/dd/yyyy')
|
|
|
|
|
Re: Replacing a Null column value with the value of the above record [message #386430 is a reply to message #386187] |
Sat, 14 February 2009 18:46   |
srinivnp
Messages: 136 Registered: January 2006 Location: stlouis MO USA
|
Senior Member |
|
|
Here is a slightly different version .
create table system.foo (id number , fdt date stat number);
insert into system.foo values
(999,to_date('09/27/2008','mm/dd/yyyy'),200);
insert into system.foo values
(999,to_date('10/01/2008','mm/dd/yyyy'),300);
insert into system.foo values
(999,to_date('10/02/2008','mm/dd/yyyy'),400);
insert into system.foo values
(999,to_date('11/05/2008','mm/dd/yyyy'),820); ==============
SQL> select * from system.foo;
ID FDT STAT
---------- --------- ----------
999 27-SEP-08 200
999 01-OCT-08 300
999 02-OCT-08 400
999 05-NOV-08 820
Now the SQL.
with
dt_range as ( select max(fdt) mxdt , min(fdt)mndt from system.foo) ,
dtset as (select mndt+level-1 dt from dt_range
connect by level <= (mxdt-mndt+1) )
select
max(id) over (order by dt nulls first ) mid ,
dt,max(stat) over ( order by dt nulls first ) st from
system.foo t , dtset d
where t.fdt(+)=d.dt
;
|
|
|
Re: Replacing a Null column value with the value of the above record [message #386431 is a reply to message #386187] |
Sat, 14 February 2009 19:13   |
srinivnp
Messages: 136 Registered: January 2006 Location: stlouis MO USA
|
Senior Member |
|
|
If you have multiple id values , then we need to tweak the SQL a little bit.
Let us say the data id like this.
SQL> select * from system.foo;
ID FDT STAT
---------- --------- ----------
999 27-SEP-08 200
999 01-OCT-08 300
999 02-OCT-08 400
999 05-NOV-08 820
888 06-JAN-08 1000
777 05-FEB-08 33
777 10-FEB-08 33
7 rows selected.
-------------------------------------------
Then the SQL should be modified as
with
dt_range as
( select id, max(fdt) mxdt , min(fdt)mndt from system.foo group by id) ,
dtset as (select id,mndt+level-1 dt from dt_range
connect by level <= (mxdt-mndt+1)
and connect_by_root id= id )
select
max(t.id) over (partition by d.id order by dt ) mid ,
dt,max(stat) over (partition by d.id order by dt ) st from
system.foo t , dtset d
where t.fdt(+)=d.dt
Srini
|
|
|
Re: Replacing a Null column value with the value of the above record [message #386432 is a reply to message #386187] |
Sat, 14 February 2009 19:23   |
srinivnp
Messages: 136 Registered: January 2006 Location: stlouis MO USA
|
Senior Member |
|
|
If you have overlapping dates for various id values , we need to tweak it a little more.
with
dt_range as
( select id, max(fdt) mxdt , min(fdt)mndt from system.foo group by id) ,
dtset as (select id,mndt+level-1 dt from dt_range
connect by level <= (mxdt-mndt+1)
and connect_by_root id= id )
select
max(t.id) over (partition by d.id order by dt ) mid ,
dt,max(stat) over (partition by d.id order by dt ) st from
system.foo t , dtset d
where t.fdt(+)=d.dt and t.id(+)=d.id
Srini
|
|
|
|
Goto Forum:
Current Time: Sat Feb 15 23:45:42 CST 2025
|