Alternative solution
A reader, August 04, 2008 - 7:21 pm UTC
The query found below returns the same results as Tom's except for the following:
1) It uses overlapping date/times.
2) It contains a row for the time period where there is no degraded status (easily removed, obviously).
3) In the case where a mode that is not entered consistently(i.e. the 6:10 to 6:20 "No note" entry), it creates an apparently redundant line.
4) It will not collapse entries that have the same mode and occur sequentially (i.e. if there is a "No Note" entry from 5:30 to 6:00, then another from 6:00 to 6:30).
SELECT date_time
, end_date_time
, REPLACE (no_note, '-', '') as no_note
, REPLACE (no_10, '-', '') as no_10
, REPLACE (no_coin, '-', '') as no_coin
FROM (SELECT date_time
, LEAD (date_time) OVER (ORDER BY date_time) AS end_date_time
, last_value (CASE
WHEN service_sub_mode = 'No Note Mode' THEN start_stop
ELSE NULL
END ignore nulls) OVER (ORDER BY date_time) AS no_note
, last_value (CASE
WHEN service_sub_mode = 'No 10 cents Mode' THEN start_stop
ELSE NULL
END ignore nulls) OVER (ORDER BY date_time) AS no_10
, last_value (CASE
WHEN service_sub_mode = 'No coin Mode' THEN start_stop
ELSE NULL
END ignore nulls) OVER (ORDER BY date_time) AS no_coin
FROM (SELECT service_sub_mode
, date_time_start AS date_time
, 'x' AS start_stop
FROM test_service
UNION ALL
SELECT service_sub_mode
, date_time_end
, '-'
FROM test_service))
where date_time <> end_date_time
/
Jermin, August 05, 2008 - 3:05 am UTC
Many Thanks. It 's really helpful.
Alternate Solution Using Pivot and Overlaps clause
robin, August 05, 2008 - 11:43 am UTC
One other possible solution (depending on your version of Oracle) could be
to use the OVERLAPS clause coupled with the PIVOT
(please note there is a small type-o in the original Inserts - the last insert
should insert a value of 'No Note Mode' as opposed to 'No note Mode' for the SERVICE_SUB_MODE column)...
select *
from (
select ts.SERVICE_SUB_MODE, time_interval.from_time, time_interval.to_time
from test_service ts,
(select date_time from_time,
lead(date_time) over (order by date_time ) to_time
from (select date_time_start date_time
from test_service
union
select date_time_end date_time
from test_service)) time_interval
where (ts.date_time_start, ts.date_time_end)
overlaps (time_interval.from_time, time_interval.to_time)
) PIVOT (count(1)
for SERVICE_SUB_MODE IN ('No Note Mode','No 10 cents Mode','No coin Mode')
)
order by from_time
August 05, 2008 - 1:10 pm UTC
you cannot use overlaps - stop it, do not use it.
it is not documented
you don't know what it is to do
I don't know what it is to do
it can break (stop doing what you observer it to be doing) anytime and no one will do a thing about it.
Jermin, August 05, 2008 - 10:48 pm UTC
very helpful..thanks Tom.
I've tried to use your logic provided in your response regarding "Recursive Lag Function" as last_value with "ignore nulls" does not work for 9.2.0.6.
The modified version is listed below.
select c.txn_date, lead_date to_date,
substr(max(nn) over (order by txn_date),11) No_Note,
substr(max(nc) over (order by txn_date),11) No_Coin,
substr(max(n10c) over (order by txn_date),11) No_10Cents
from (
select c3.* ,
case when max_no_note is not null then to_char((row_number() over (order by txn_date)) ,'fm0000000000') || max_no_note end nn,
case when max_no_coin is not null then to_char((row_number() over (order by txn_date)) ,'fm0000000000') || max_no_coin end nc,
case when max_no_10cents is not null then to_char((row_number() over (order by txn_date)) ,'fm0000000000') || max_no_10cents end n10c
from
(select c2.* ,
max(No_Note) over (partition by txn_date order by txn_date) max_no_note ,
max(No_coin) over (partition by txn_date order by txn_date) max_no_coin ,
max(No_10cents) over (partition by txn_date order by txn_date) max_no_10cents
from
(
select txn_date,
case when service_sub_mode = 'No Note Mode' and flag = 'S' then 'X'
when service_sub_mode = 'No Note Mode' and flag = 'E' then '-'
end No_Note,
case when service_sub_mode = 'No coin Mode' and flag = 'S' then 'X'
when service_sub_mode = 'No coin Mode' and flag = 'E' then '-'
end No_Coin,
case when service_sub_mode = 'No 10 cents Mode' and flag = 'S' then 'X'
when service_sub_mode = 'No 10 cents Mode' and flag = 'E' then '-'
end No_10cents,
((lead(txn_date) over(order by txn_date) ) - (1/(24*60) )) lead_date
from
(
select date_time_start txn_date, service_sub_mode ,'S' flag
from test_service
union all
select date_time_end txn_date , service_sub_mode ,'E' flag
from test_service
order by 1
) c1
)c2
)c3
where lead_date > txn_date
) c
August 06, 2008 - 8:42 am UTC
I don't understand, I did not use last_value or ignore nulls?
I gave you a query that works in 9iR2.
ops$tkyte%ORA9IR2> select distinct service_sub_mode from test_service;
SERVICE_SUB_MODE
--------------------
No 10 cents Mode
No Note Mode
No coin Mode
ops$tkyte%ORA9IR2> with
2 min_time as (select min(date_time_start) mint from test_service),
3 max_time as (select max(date_time_end) maxt from test_service),
4 data as (select min_time.mint+(level-1)/24/60 tim
5 from min_time, max_time
6 connect by level <= (max_time.maxt-min_time.mint)*24*60+1)
7 select min(tim), max(tim),
8 replace( substr( max(flag),1,1 ), '-', '' ) "No coin Mode",
9 replace( substr( max(flag),2,1 ), '-', '' ) "No Note Mode",
10 replace( substr( max(flag),3,1 ), '-', '' ) "No 10 cents Mode"
11 from (
12 select tim, flag, max(grp) over (order by tim) maxgrp
13 from (
14 select tim, flag,
15 case when nvl(lag(flag) over (order by tim),'a') <> flag then row_number() over (order by tim) end grp
16 from (
17 select data.tim,
18 max(case when test_service.service_sub_mode = 'No coin Mode' then 'x' else '-' end ) ||
19 max(case when test_service.service_sub_mode = 'No Note Mode' then 'x' else '-' end ) ||
20 max(case when test_service.service_sub_mode = 'No 10 cents Mode' then 'x' else '-' end ) flag
21 from data, test_service
22 where data.tim between test_service.date_time_start and test_service.date_time_end
23 group by data.tim
24 )
25 )
26 )
27 group by maxgrp, flag
28 order by 1
29 /
MIN(TIM) MAX(TIM) N N N
--------- --------- - - -
29-JUL-08 29-JUL-08 x
29-JUL-08 29-JUL-08 x x
29-JUL-08 29-JUL-08 x
29-JUL-08 29-JUL-08 x x
29-JUL-08 29-JUL-08 x
29-JUL-08 29-JUL-08 x x
29-JUL-08 29-JUL-08 x
7 rows selected.
so, I'm not sure what you are doing or why or what your goal is here....
You seem to be asking a different question, but you've only posted a query that doesn't work - which means if I reversed engineered it - we would solve a problem that doesn't exist since I only have a non-functioning query to go on.
Another solution
Sankar, August 06, 2008 - 1:40 am UTC
This works in 8i.
SELECT t.date_time_start,
t.nxt_start date_time_end,
MAX(decode(service_sub_mode,
'No Note Mode',
'X')) "No Note Mode",
MAX(decode(service_sub_mode,
'No 10 cents Mode',
'X')) "No 10 cents Mode",
MAX(decode(service_sub_mode,
'No coin Mode',
'X')) "No coin Mode"
FROM (SELECT date_time_start,
lead(date_time_start) over(ORDER BY date_time_start) nxt_start
FROM (SELECT date_time_start
FROM test_service a
UNION
SELECT date_time_end
FROM test_service a)) t,
test_service s
WHERE s.date_time_start <= t.date_time_start AND
s.date_time_end >= t.nxt_start
GROUP BY t.date_time_start,
t.nxt_start
A reader, August 06, 2008 - 3:14 am UTC
excellent!
One more solution
Ravi, August 06, 2008 - 10:51 am UTC
/* Formatted on 2008/08/06 10:32 (Formatter Plus v4.8.8) */
SELECT *
FROM (SELECT TO_CHAR (from_dt, 'dd/mm/yyyy hh:mi:ss')
|| ' To '
|| TO_CHAR (to_dt, 'dd/mm/yyyy hh:mi:ss') date_range,
(SELECT 'X'
FROM test_service
WHERE service_sub_mode = 'No Note Mode'
AND from_dt BETWEEN date_time_start AND date_time_end
AND to_dt BETWEEN date_time_start AND date_time_end)
"No Note Mode",
(SELECT 'X'
FROM test_service
WHERE service_sub_mode =
'No 10 cents Mode'
AND from_dt BETWEEN date_time_start AND date_time_end
AND to_dt BETWEEN date_time_start AND date_time_end)
"No 10 cents Mode",
(SELECT 'X'
FROM test_service
WHERE service_sub_mode = 'No coin Mode'
AND from_dt BETWEEN date_time_start AND date_time_end
AND to_dt BETWEEN date_time_start AND date_time_end)
"No Coin Mode"
FROM (SELECT dt from_dt, LEAD (dt) OVER (ORDER BY rk) to_dt
FROM (SELECT dt, RANK () OVER (ORDER BY dt) rk
FROM (SELECT date_time_start dt
FROM test_service
UNION
SELECT date_time_end dt
FROM test_service) t)))
WHERE COALESCE ("No Coin Mode", "No 10 cents Mode", "No Note Mode") IS NOT NULL
Another similar
Loz, August 06, 2008 - 11:44 pm UTC
Interesting problem. I wrote this before reading Ravi's and it's basically the same thing with CTEs. (I had to use SQL Server but I think this will work for Oracle too).
with TimeRanges(rn,dt) as
(
select row_number() over (order by dt) as rn,dt
from
(select DATE_TIME_START dt from test_service union select DATE_TIME_end from test_service) x
)
select * from
(
select tr1.dt as dtfrom,tr2.dt as dtto,
( select 'X' from test_service where SERVICE_SUB_MODE='No note Mode' and (DATE_TIME_START <=tr1.dt and DATE_TIME_end >=tr2.dt)) as NoNote,
( select 'X' from test_service where SERVICE_SUB_MODE='No 10 cents Mode' and (DATE_TIME_START <=tr1.dt and DATE_TIME_end >=tr2.dt)) as No10Cent,
( select 'X' from test_service where SERVICE_SUB_MODE='No coin Mode' and (DATE_TIME_START <=tr1.dt and DATE_TIME_end >=tr2.dt)) as NoCoin
from timeranges tr1 inner join timeranges tr2 on tr1.rn=tr2.rn-1
)x
WHERE COALESCE (NoNote, No10Cent, NoCoin ) IS NOT NULL
order by dtfrom
Same question without end time of service
Utku, August 12, 2008 - 1:43 pm UTC
Hi Tom,
I have same question but not including and end time of a status.
For instance, I have a table that shows status of an item. Whenever an item's status change, than a new record inserted to reflect the new status of that item. It is assumed that at any time, the item has a status of nearest smaller status_time of that item.
create table itemStatus
(
itemId number,
status_time date,
st1 varchar2(10),
st2 varchar2(10),
st3 varchar2(10)
);
insert into itemStatus values (1, to_date('20/07/2008'), 'A1', 'AA1', 'AAA1');
insert into itemStatus values (1, to_date('24/07/2008'), 'A2', 'AA2', 'AAA2');
insert into itemStatus values (1, to_date('25/07/2008'), 'A3', 'AA3', 'AAA3');
insert into itemStatus values (1, to_date('28/07/2008'), 'A4', 'AA4', 'AAA4');
commit;
select *
from itemStatus
where itemId = 1;
itemId status_time st1 st2 st3
1 20/07/2008 A1 AA1 AAA1
1 24/07/2008 A2 AA2 AAA2
1 25/07/2008 A3 AA3 AAA3
1 28/07/2008 A4 AA4 AAA4
When I want to find status of item for an interval:
select *
from itemStatus
where itemId = 1
and status_time >= to_date('22/07/2008','dd/mm/yyyy')
and status_time <= to_date('26/07/2008','dd/mm/yyyy');
itemId status_time st1 st2 st3
1 24/07/2008 A2 AA2 AAA2
1 25/07/2008 A3 AA3 AAA3
what I want is the status of item for every day at that interval:
itemId status_time st1 st2 st3
1 22/07/2008 A1 AA1 AAA1
1 23/07/2008 A1 AA1 AAA1
1 24/07/2008 A2 AA2 AAA2
1 25/07/2008 A3 AA3 AAA3
1 26/07/2008 A3 AA3 AAA3
Thanks
August 18, 2008 - 8:44 am UTC
first, we need all of the days in your result set, this bit gets that:
ops$tkyte%ORA10GR2> variable sdate varchar2(30)
ops$tkyte%ORA10GR2> variable edate varchar2(30)
ops$tkyte%ORA10GR2> variable itemid number
ops$tkyte%ORA10GR2> exec :sdate := '22/07/2008'; :edate := '26/07/2008'; :itemid := 1
PL/SQL procedure successfully completed.
ops$tkyte%ORA10GR2> with dates
2 as
3 (select to_date( :sdate,'dd/mm/yyyy')+level-1 dt
4 from dual
5 connect by level <= to_date(:edate,'dd/mm/yyyy')-to_date(:sdate,'dd/mm/yyyy')+1 ),
Now, we need the data for the dates in the range, there are two pieces to that - the data that clearly falls in the date range:
6 items
7 as
8 (
9 select *
10 from itemStatus
11 where status_time between to_date(:sdate,'dd/mm/yyyy') and to_date(:edate,'dd/mm/yyyy')
12 and itemid = :itemid
and the last date that falls just SHORT of the range:
13 union all
14 select itemid, to_date(:sdate,'dd/mm/yyyy'), st1, st2, st3
15 from (
16 select *
17 from (
18 select *
19 from itemStatus
20 where status_time <= to_date(:sdate,'dd/mm/yyyy')
21 and itemid = :itemid
22 order by itemid desc, status_time desc
23 )
24 where rownum = 1
25 )
26 where status_time <> to_date(:sdate,'dd/mm/yyyy')
27 )
we do that by going after your itemid and finding the MAX date that is less than or equal to your begin date - sorting by itemid DESC and status_time DESC (index those columns in that order to make this "efficient") and grabbing the first row and throwing it out if we already have it (the <> does that....)
Once we have that - it is a simple matter of outer joining:
28 select dates.dt, :itemid,
29 last_value(st1 ignore nulls) over (order by dates.dt) st1,
30 last_value(st2 ignore nulls) over (order by dates.dt) st2,
31 last_value(st3 ignore nulls) over (order by dates.dt) st3
32 from dates, items
33 where dates.dt = items.status_time (+)
34 order by dates.dt
35 /
and carrying down the last value, you never mentioned a version, so I'll assume "current", putting it all together:
ops$tkyte%ORA10GR2> with dates
2 as
3 (select to_date( :sdate,'dd/mm/yyyy')+level-1 dt
4 from dual
5 connect by level <= to_date(:edate,'dd/mm/yyyy')-to_date(:sdate,'dd/mm/yyyy')+1 ),
6 items
7 as
8 (
9 select *
10 from itemStatus
11 where status_time between to_date(:sdate,'dd/mm/yyyy') and to_date(:edate,'dd/mm/yyyy')
12 and itemid = :itemid
13 union all
14 select itemid, to_date(:sdate,'dd/mm/yyyy'), st1, st2, st3
15 from (
16 select *
17 from (
18 select *
19 from itemStatus
20 where status_time <= to_date(:sdate,'dd/mm/yyyy')
21 and itemid = :itemid
22 order by itemid desc, status_time desc
23 )
24 where rownum = 1
25 )
26 where status_time <> to_date(:sdate,'dd/mm/yyyy')
27 )
28 select dates.dt, :itemid,
29 last_value(st1 ignore nulls) over (order by dates.dt) st1,
30 last_value(st2 ignore nulls) over (order by dates.dt) st2,
31 last_value(st3 ignore nulls) over (order by dates.dt) st3
32 from dates, items
33 where dates.dt = items.status_time (+)
34 order by dates.dt
35 /
DT :ITEMID ST1 ST2 ST3
---------- ---------- ---------- ---------- ----------
22/07/2008 1 A1 AA1 AAA1
23/07/2008 1 A1 AA1 AAA1
24/07/2008 1 A2 AA2 AAA2
25/07/2008 1 A3 AA3 AAA3
26/07/2008 1 A3 AA3 AAA3
Jermin Dawoud, August 13, 2008 - 9:24 am UTC
with
min_time as (select min(STATUS_TIME ) mint from itemStatus ),
max_time as (select max(STATUS_TIME ) maxt from itemStatus ),
data as (select min_time.mint+(level-1) tim
from min_time, max_time
connect by level <= (max_time.maxt-min_time.mint)+1)
select a.*
from
(
select status_time , substr(max(st1) over (order by status_time),11) st1,
substr(max(st2) over (order by status_time) ,11) st2,
substr(max(st3) over (order by status_time) ,11) st3
from (
select d.tim status_time,
case
when st1 is not null
then to_char((row_number() over (order by STATUS_TIME)),'fm0000000000') || st1
end st1,
case
when st2 is not null
then to_char((row_number() over (order by STATUS_TIME)),'fm0000000000') || st2
end st2,
case
when st3 is not null
then to_char((row_number() over (order by STATUS_TIME)),'fm0000000000') || st3
end st3
from data d,itemStatus i
where d.tim = i.status_time(+)
)
)a
where
status_time >= to_date('22/07/2008','dd/mm/yyyy')
and status_time <= to_date('26/07/2008','dd/mm/yyyy')
/
STATUS_TIME ST1 ST2 ST3
-------------------- ----------- ----------- -----------
22-jul-2008:00:00:00 A1 AA1 AAA1
23-jul-2008:00:00:00 A1 AA1 AAA1
24-jul-2008:00:00:00 A2 AA2 AAA2
25-jul-2008:00:00:00 A3 AA3 AAA3
26-jul-2008:00:00:00 A3 AA3 AAA3
August 18, 2008 - 9:33 am UTC
if you have 10g, use last_value and ignore nulls to carry down
Jermin, August 13, 2008 - 7:53 pm UTC
Sorry, did not consider the itemid in the previous sql. Please see the following for the correct version.
SQL> select * from itemstatus;
ITEMID STATUS_TI ST1 ST2 ST3
--------- --------- ---------- ---------- ----------
1 20/JUL/08 A1 AA1 AAA1
1 24/JUL/08 A2 AA2 AAA2
1 25/JUL/08 A3 AA3 AAA3
1 28/JUL/08 A4 AA4 AAA4
2 24/JUL/08 A21 AA21 AAA21
2 26/JUL/08 A22 AA22 AAA22
6 rows selected.
with
min_time as (select min(STATUS_TIME ) mint from itemStatus ),
max_time as (select max(STATUS_TIME ) maxt from itemStatus ),
data as (select min_time.mint+(level-1) tim
from min_time, max_time
connect by level <= (max_time.maxt-min_time.mint)+1)
SELECT TIM STATUS_TIME, ITEMID,ST1,ST2,ST3
FROM
(
select A.*,
CASE WHEN LEAD(status_time) OVER (PARTITION BY ITEMID ORDER BY STATUS_TIME) IS NULL
THEN
STATUS_TIME + 1
ELSE
LEAD(status_time) OVER (PARTITION BY ITEMID ORDER BY STATUS_TIME)
END END_PERIOD
FROM ITEMSTATUS A
)A,DATA
where TIM >= STATUS_TIME AND TIM < END_PERIOD
AND TIM >= to_date('22/07/2008','dd/mm/yyyy')
and tim <= to_date('26/07/2008','dd/mm/yyyy')
ORDER BY ITEMID, STATUS_TIME
STATUS_TI ITEMID ST1 ST2 ST3
--------- --------- ---------- ---------- ----------
22/JUL/08 1 A1 AA1 AAA1
23/JUL/08 1 A1 AA1 AAA1
24/JUL/08 1 A2 AA2 AAA2
25/JUL/08 1 A3 AA3 AAA3
26/JUL/08 1 A3 AA3 AAA3
24/JUL/08 2 A21 AA21 AAA21
25/JUL/08 2 A21 AA21 AAA21
26/JUL/08 2 A22 AA22 AAA22
8 rows selected.
1 with
2 min_time as (select min(STATUS_TIME ) mint from itemStatus ),
3 max_time as (select max(STATUS_TIME ) maxt from itemStatus ),
4 data as (select min_time.mint+(level-1) tim
5 from min_time, max_time
6 connect by level <= (max_time.maxt-min_time.mint)+1)
7 SELECT TIM STATUS_TIME, ITEMID,ST1,ST2,ST3
8 FROM
9 (
10 select A.*,
11 CASE WHEN LEAD(status_time) OVER (PARTITION BY ITEMID ORDER BY STATUS_TIME) IS NULL
12 THEN
13 STATUS_TIME + 1
14 ELSE
15 LEAD(status_time) OVER (PARTITION BY ITEMID ORDER BY STATUS_TIME)
16 END END_PERIOD
17 FROM ITEMSTATUS A
18 )A,DATA
19 where TIM >= STATUS_TIME AND TIM < END_PERIOD
20 AND TIM >= to_date('22/07/2008','dd/mm/yyyy')
21 and tim <= to_date('26/07/2008','dd/mm/yyyy')
22 and itemid=1
23* ORDER BY ITEMID, STATUS_TIME
SQL> /
STATUS_TI ITEMID ST1 ST2 ST3
--------- --------- ---------- ---------- ----------
22/JUL/08 1 A1 AA1 AAA1
23/JUL/08 1 A1 AA1 AAA1
24/JUL/08 1 A2 AA2 AAA2
25/JUL/08 1 A3 AA3 AAA3
26/JUL/08 1 A3 AA3 AAA3
Same question without end time of service
utku, September 08, 2008 - 8:35 am UTC
Thanks for the answer...
what about if a list of items queried?
select *
from itemStatus
where itemId in (1, 2, 3)
and status_time >= to_date('22/07/2008','dd/mm/yyyy')
and status_time <= to_date('26/07/2008','dd/mm/yyyy');
September 08, 2008 - 4:17 pm UTC
what if indeed - be more explicit please - and provide a create/insert set of data to work with - it would be cumbersome at best to page up and down and try to assemble something.
I rather suspect your issue will be "we want to carry down the last value observed BEFORE 22/07/2008 if there was no row for 22/07/2008" right?
if so, we'll accomplish that with a union all inline view to pick up that row if 22/07/2008 doesn't exist (and that itself might be enough for you to go on...)
Same question without end time of service
utku, September 09, 2008 - 2:57 am UTC
In the first question, while computing the ones whose first date falls just SHORT of the range you used the code:
13 union all
14 select itemid, to_date(:sdate,'dd/mm/yyyy'), st1, st2, st3
15 from (
16 select *
17 from (
18 select *
19 from itemStatus
20 where status_time <= to_date(:sdate,'dd/mm/yyyy')
21 and itemid = :itemid
22 order by itemid desc, status_time desc
23 )
24 where rownum = 1
25 )
26 where status_time <> to_date(:sdate,'dd/mm/yyyy')
27 )
get the last day earlier than the beginning by using the predicate "rownum = 1" in time ordered status list for an item. What Im asking is if there is more then one item, what approach should be taken to get the last days for each item. I have written this part by partitioning and getting the first values. Something like:
select unique first_value(st1) over (partition by item order by status_time desc) st1,
first_value(st2) over (partition by item order by status_time desc) st2,
first_value(st3) over (partition by item order by status_time desc) st3
from itemStatus
where status_time <= to_date(:sdate,'dd/mm/yyyy')
and itemId in (1, 2)
I wonder if there is better way to do this.
The script is:
create table itemStatus
(
itemId number,
status_time date,
st1 varchar2(10),
st2 varchar2(10),
st3 varchar2(10)
);
insert into itemStatus values (1, to_date('20/07/2008'), 'A1', 'AA1', 'AAA1');
insert into itemStatus values (1, to_date('24/07/2008'), 'A2', 'AA2', 'AAA2');
insert into itemStatus values (1, to_date('25/07/2008'), 'A3', 'AA3', 'AAA3');
insert into itemStatus values (1, to_date('28/07/2008'), 'A4', 'AA4', 'AAA4');
insert into itemStatus values (2, to_date('25/07/2008'), 'B1', 'BB1', 'BBB1');
insert into itemStatus values (2, to_date('27/07/2008'), 'B2', 'BB2', 'BBB2');
insert into itemStatus values (3, to_date('26/07/2008'), 'C1', 'CC1', 'CCC1');
commit;
select *
from itemStatus
where itemId in (1, 2);
itemId status_time st1 st2 st3
1 20/07/2008 A1 AA1 AAA1
1 24/07/2008 A2 AA2 AAA2
1 25/07/2008 A3 AA3 AAA3
1 28/07/2008 A4 AA4 AAA4
2 25/07/2008 B1 BB1 BBB1
2 27/07/2008 B2 BB2 BBB2
select *
from itemStatus
where itemId in (1, 2)
and status_time >= to_date('22/07/2008','dd/mm/yyyy')
and status_time <= to_date('26/07/2008','dd/mm/yyyy');
And the expected result is:
itemId status_time st1 st2 st3
1 22/07/2008 A1 AA1 AAA1
1 23/07/2008 A1 AA1 AAA1
1 24/07/2008 A2 AA2 AAA2
1 25/07/2008 A3 AA3 AAA3
1 26/07/2008 A3 AA3 AAA3
2 22/07/2008
2 23/07/2008
2 24/07/2008
2 25/07/2008 B1 BB1 BBB1
2 26/07/2008 B1 BB1 BBB1
September 09, 2008 - 7:58 am UTC
you would use this as the starting set:
select *
from itemStatus
where itemId in (1, 2)
and status_time > to_date('22/07/2008','dd/mm/yyyy')
and status_time <= to_date('26/07/2008','dd/mm/yyyy')
union all
select itemId, status_time, st1, st2, st3
from (
select itemId, to_date('22/07/2008','dd/mm/yyyy') status_time, st1, st2, st3 ,
row_number() over (partition by itemId order by status_time) rn
from itemStatus
where itemId in (1,2)
and status_time <= to_date('22/07/2008','dd/mm/yyyy')
)
where rn = 1
/and then either
a) read about partitioned outer joins
http://asktom.oracle.com/pls/ask/search?p_string=%22partitioned+outer+joins%22 b) take the set of dates I had and cartesian product them with a query that generates the set of itemIds and outer join to that....
give it a try :)
Non-Overlapping Ranges
Pet Dav, March 18, 2010 - 8:30 pm UTC
Hi,
I have a table with following data
Account Number Account Type
0001 A
0002 A
....
....
0010 A
0011 L
....
0020 L
0021 A
0022 L
0023 A
....
....
0030 A
I want a query which prints groups by Account Types and the non-overlapping ranges. The result of the output from the above data should be
Min Max Account Type
0001 0010 A
0011 0020 L
0021 0021 A
0022 0022 L
0023 0030 A
Please help. Thank you
March 19, 2010 - 6:57 am UTC
no create
no inserts
(I'm getting repetitive, if only I had 5 cents for every time I wrote this...)
no look
Non-Overlapping Ranges
Pet Dav, March 22, 2010 - 9:02 am UTC
Sorry about that..Here is the create table and insert statements. Account_Type can be either of these following values 'A','L','O','R','E'.
create table temp2(account_no varchar2(20),account_type varchar2(1) );
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0001', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0002', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0003', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0004', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0005', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0006', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0007', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0009', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0010', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0011', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0012', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0013', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0014', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0015', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0016', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0017', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0018', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0019', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0020', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0021', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0022', 'L');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0023', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0024', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0025', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0026', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0027', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0028', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0029', 'A');
Insert into TEMP2
(ACCOUNT_NO, ACCOUNT_TYPE)
Values
('0030', 'A');
COMMIT;
March 22, 2010 - 9:36 am UTC
ops$tkyte%ORA10GR2> select account_type, min(account_no), max(account_no)
2 from (
3 select account_no, account_type, last_value(grp ignore nulls) over (order by account_no) the_grp
4 from (
5 select account_no, account_type,
6 case when lag(account_type) over (order by account_no) <> account_type
7 or
8 row_number() over (order by account_no) = 1
9 then row_number() over (order by account_no)
10 end grp
11 from t
12 )
13 )
14 group by account_type, the_grp
15 order by the_grp
16 /
A MIN(ACCOUNT_NO) MAX(ACCOUNT_NO)
- -------------------- --------------------
A 0001 0010
L 0011 0020
A 0021 0021
L 0022 0022
A 0023 0030
range
A reader, March 22, 2010 - 10:01 am UTC
awesome
Pet Dev, March 22, 2010 - 2:16 pm UTC
Worked Great. Thanks