CODEWITHSUNDEEP
sqloracle-databasepivot
ashkufaraz
ashkufaraz

Reputation: 5297

Create dynamic column

My table has this data

START_DATE            | STOP_DATE             | SAMPLE_DATE            | SAMPLE_VALUE
:-------------------- | :-------------------- | :--------------------- | -----------:
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:10:00 AM  | 10
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:14:00 AM  | 20
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:20:00 AM  | 15
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:40:00 AM  | 10
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:50:00 AM  | 21
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 11/11/2019 8:55:00 AM  | 20

I want write query to return this result

START_DATE            | STOP_DATE             | part1 |part2 |part3 |part4
:-------------------- | :-------------------- | ------|------|------|-------
11/11/2019 8:00:00 AM | 11/11/2019 9:00:00 AM | 20    |15    |10    |20

I want start date to stop date split to 4 part [8:00-8:15,8:15-8:30,8:30-8:45,8:45-09:00] and set last sample value to the corresponding column.

Please help me

<>fiddle here

Upvotes: 1

Views: 58

Answers (2)

ashkufaraz
ashkufaraz

Reputation: 5297

select start_date, stop_date
     , max(case min15 when 0 then sample_value end) as part1
     , max(case min15 when 15 then sample_value end) as part2
     , max(case min15 when 30 then sample_value end) as part3
     , max(case min15 when 45 then sample_value end) as part4 
from (
select start_date, stop_date, sample_value, min15, row_number() over (partition by min15 order by sample_date desc) as rn
from (
    select h.*, trunc(to_char (sample_date,'MI') / 15) * 15 as min15 
    from heats h
) T
) U
where rn = 1
group by start_date, stop_date;

Upvotes: 0

Barbaros &#214;zhan
Barbaros &#214;zhan

Reputation: 65288

One option would be using connect by level < syntax and applying row_number() analytic function at the successive step :

with t as
(
 select start_date + 
        level * ( stop_date - start_date ) 
               /( ( extract(hour from (stop_date - start_date))*60+
                      extract(minute from (stop_date - start_date)) )
                     / 15  )  as split_date, 
        start_date, stop_date, sample_date, sample_value, level as lvl
   from heats
connect by level <= ( extract(hour from (stop_date - start_date))*60+
                      extract(minute from (stop_date - start_date)) )
                     / 15   
   and prior sys_guid() is not null
   and prior sample_date = sample_date
), t2 as
(
select row_number() over (partition by split_date order by sample_date desc) as rn,
       t.*
  from t     
 where sample_date < split_date   
)
select start_date, stop_date, 
       max( case when lvl = 1 then sample_value end ) as part1,
       max( case when lvl = 2 then sample_value end ) as part2,
       max( case when lvl = 3 then sample_value end ) as part3,
       max( case when lvl = 4 then sample_value end ) as part4
  from t2
 where rn = 1 
 group by start_date, stop_date
 order by rn desc;

Demo

Upvotes: 1

Related Questions