(Please refer to the attachment images as well to get the clear picture of the data and to understand the question properly.)
I have a data set like this in one of my tables. (This is a mere representation of original data.)
Refer table1.jpg
Id | Type | Value | Start_date | end_date
----------------------------------------------------------------------------------------------------------------------
ZTR0098 | ALLOW | 0 | 1-JUN | 2-JUN |
ZTR0098 | ADTAX | 0 | 1-JUN | 2-JUN |
ZTR0098 | MXTAX | 0 | 1-JUN | 9-JUN |
ZTR0098 | ALLOW | 4 | 3-JUN | 15-JUN |
ZTR0098 | ADTAX | 44.00 | 3-JUN | 17-JUN |
ZTR0098 | MXTAX | 2 | 10-JUN | 17-JUN |
ZTR0098 | ALLOW | 5 | 16-JUN | 20-JUN |
ZTR0098 | ADTAX | 55.34 | 18-JUN | 22-JUN |
ZTR0098 | MXTAX | 1 | 18-JUN | 25-JUN |
ZTR0098 | MXTAX | 6 | 26-JUN | 31-AUG |
ZTR0098 | ADTAX | 20.09 | 23-JUN | 23-JUL |
ZTR0098 | ALLOW | 8 | 21-JUN | 31-AUG |
ZTR0098 | ADTAX | 45 | 24-JUL | 31-AUG |
each row has a type and a id attahced to it. Id belongs to some parent table. the value of each type is given and the validity of each value is tracked by a start_date and end_date field.
All values starts from 1-JUN and expires at 31-AUG. Now my requirement is to get a report which gives three columns for three different type (ALLOW,ADTAX & MXTAX) having combination of unique values in with effective time interval. Let me put the result below.
Refer table2.jpg
Id | ALLOW | ADTAX | MXTAX | Start_date | end_date
--------------------------------------------------------------------------------------------------------------------------------------------------
ZTR0098 | 0 | 0 | 0 | 1-JUN | 2-JUN |
ZTR0098 | 4 | 44.00 | 0 | 3-JUN | 9-JUN |
ZTR0098 | 4 | 44.00 | 2 | 10-JUN | 15-JUN |
ZTR0098 | 5 | 44.00 | 2 | 16-JUN | 17-JUN |
ZTR0098 | 5 | 55.34 | 1 | 18-JUN | 20-JUN |
ZTR0098 | 8 | 55.34 | 1 | 21-JUN | 22-JUN |
ZTR0098 | 8 | 20.09 | 1 | 23-JUN | 25-JUN |
ZTR0098 | 8 | 20.09 | 6 | 26-JUN | 23-JUL |
ZTR0098 | 8 | 45 | 6 | 23-JUL | 31-AUG |
As you can see there are no duplicate rows for a combination of (ALLOW,ADTAX & MXTAX) with thier respective effective dates. in the above resultant table . at first step is to convert rows to column which is pretty obvious to do that by grouping it on start_date and end_date colum, but the real deal is to find out the time interval during which the combination of (ALLOW,ADTAX & MXTAX) values remained constant.
I have written below query using group by .
select
id ,
nvl(max(decode(type,'ALLOW', value)),0) as ALLOW
nvl(max(decode(type,'ADTAX', value)),0) as ADTAX
nvl(max(decode(type,'MXTAX', value)),0) as MXTAX
Start_date,
end_date
from My_table
group by Start_date, end_date,id
order by Start_date, end_date
the result it gives are like this :
Refer table3.jpg
Id | ALLOW | ADTAX | MXTAX | Start_date | end_date
------------------------------------------------------------------------------------------------------------------------------------------------
ZTR0098 | 0 | 0 | 0 | 1-JUN | 2-JUN |
ZTR0098 | 0 | 0 | 2 | 1-JUN | 9-JUN |
ZTR0098 | 4 | 0 | 0 | 3-JUN | 15-JUN |
ZTR0098 | 0 | 44.00 | 0 | 3-JUN | 17-JUN |
ZTR0098 | 0 | 0 | 2 | 10-JUN | 17-JUN |
ZTR0098 | 5 | 0 | 0 | 16-JUN | 20-JUN |
ZTR0098 | 0 | 55.34 | 0 | 18-JUN | 22-JUN |
. .
. .
like wise
but I am not able to figure out the time intervals using SQL query.