Home » SQL & PL/SQL » SQL & PL/SQL » plsql table partitions
|
|
|
|
|
|
|
|
|
Re: plsql table partitions [message #629968 is a reply to message #629917] |
Thu, 18 December 2014 03:53 |
|
palpali
Messages: 138 Registered: December 2014 Location: India
|
Senior Member |
|
|
Hallo
Sorry for my previous post, so here i have tried to find someway to solve my Problem, but still i am not satisfied with it
my Problem is: the Output Shows two rows of same record of same item number and having same rate, (as i have made bold in my Output)
but i would like to have my Output just one row of record if they are same (including first (from_date) to last(to_date).
may be i have bit ellaborate my Problem nao
can anyone please help me on this regards?
thanking you
select distinct PID, FROM_DATE, TO_DATE, RATE
from
(
select s.*,
case when before_date = 0 then now else
lag (now, 1, 0) over (partition by rate order by pid, rate, now) end FROM_DATE,
case when after = 1 then now else
lead (now, 1, 0) over (partition by rate order by pid, rate, now) end TO_DATE
from (
select pid, rate,
case when
lag(period,1,0) over (partition by rate order by pid, rate, period) = period - 1 then
lag(period,1,0) over (partition by rate order by pid, rate, period) else 0 end before,
period now,
case when
lead(period,1,0) over (partition by rate order by pid, rate, period) = period + 1 then
lead(period,1,0) over (partition by rate order by pid, rate, period) else 1 end after
from pruductsale
where pid = 999
order by pid, rate, period) s
where ((before = 0) and (after = 1))
or ((before = 0 and after != 1 )or (after = 1 and before != 0))
order by pid, now;
---------------
Output:
PID FROM_DATE TO_DATE RATE
----- ------------- --------- -------
999 200105 200130 55
999 200131 200131 72
999 200132 200140 55
999 200141 200152 72
999 200201 200225 72
|
|
|
|
Re: plsql table partitions [message #629983 is a reply to message #629969] |
Thu, 18 December 2014 07:56 |
|
palpali
Messages: 138 Registered: December 2014 Location: India
|
Senior Member |
|
|
hallo Michael
thankx for your reply..
wel
here is my test case.
Thank you for your guidance
create table TEST_S (
PID Number,
From_we Number,
To_we Number,
Rate Number
);
insert into TEST_S (pid, from_we, to_we, rate) values (10, 200101, 200111, 50);
insert into TEST_S (pid, from_we, to_we, rate) values (10, 200102, 200102, 55);
insert into TEST_S (pid, from_we, to_we, rate) values (10, 200103, 200120, 50);
insert into TEST_S (pid, from_we, to_we, rate) values (10, 200121, 200122, 39);
insert into TEST_S (pid, from_we, to_we, rate) values (10, 200123, 200152, 199);
insert into TEST_S (pid, from_we, to_we, rate) values (10, 200201, 200212, 199);
|
|
|
|
|
|
Re: plsql table partitions [message #629991 is a reply to message #629989] |
Thu, 18 December 2014 08:37 |
|
palpali
Messages: 138 Registered: December 2014 Location: India
|
Senior Member |
|
|
NO, i don't have
my Problem is,
as i am still getting two rows of records, that they have same item number, and rate ( but the time interval is different)
which i want to optimize this row into one,
in my case is
item no. 10 and rate 199 the last two records.
thank you in advance.
Output of TEST_S:
PID,FROM_WE,TO_WE,RATE
10,200101,200111,50
10,200102,200102,55
10,200103,200120,50
10,200121,200122,39
10,200123,200152,199
10,200201,200212,199
|
|
|
|
Re: plsql table partitions [message #630002 is a reply to message #629985] |
Thu, 18 December 2014 09:22 |
cookiemonster
Messages: 13917 Registered: September 2008 Location: Rainy Manchester
|
Senior Member |
|
|
palpali wrote on Thu, 18 December 2014 14:04ahh sorry.. it was my fault.. please just choose the one table Name,, either TEST_S or PRODUCTSALE
sorry.. :/
They don't have the same columns. So they aren't interchangeable.
Michel keeps asking for a detailed explanation. If you don't supply one you aren't going to get the help you need and you are just wasting everyone's time, including your own.
|
|
|
Goto Forum:
Current Time: Thu Mar 28 21:32:56 CDT 2024
|