SQL convert hour to half hourly observation

Question

I've searched to forums, bout couldn't quite find an answer that helped me. Hence, the reason fro this thread.

I have a database which contains hourly price observations. I also have this SQL query statement, which goes in and selects/returns the hourly price observations within a certain date range.

However, I need the query to return the price observations in half-hour instead of hourly --> I guess this means, that every observations basically needs to be repeated twice, hence converting it from hourly to half-hourly.

My code is as such:

SELECT A.DELIVERY_START,
  A.PRICE
FROM DMA_PREP.DMA_PRICE_V a
WHERE 1                         =1
AND A.PRODUCT_BID               ='1000286'
AND a.DELIVERY_START              >= TRUNC(TRUNC(TRUNC(sysdate,'YYYY'),'YY'),'YY')
AND a.DELIVERY_PERIOD_TYPE_NAME = 'Hour'
ORDER BY A.delivery_start

I've tried to change 'Hour' to 'Half-hour' but without luck, as it then starts to return some completely other values from the database, which has a half-hourly tag.

So basically, I need the query to extract the data as it already does, but then convert it to half-hourly observations before printing it.

This is what the output looks like now:

01-JAN-14 35

01-JAN-14 34.97

01-JAN-14 34.03

01-JAN-14 30

01-JAN-14 28.04

01-JAN-14 29.79

01-JAN-14 29.79

01-JAN-14 29.79

01-JAN-14 29.9

01-JAN-14 30.63

01-JAN-14 33.58

01-JAN-14 34.97

01-JAN-14 44.92

01-JAN-14 35.99

01-JAN-14 35.46

01-JAN-14 37.94

01-JAN-14 59.94

01-JAN-14 71.99

01-JAN-14 57.91

01-JAN-14 47.95

01-JAN-14 44.95

01-JAN-14 40.04

01-JAN-14 34.56

01-JAN-14 34.01

02-JAN-14 36.71

Any suggestions?

Answer 1

You can do this with union all:

with t as (<your query here>)
    select t.DELIVERY_START, t.PRICE
    union all
    select t.DELIVERY_START + 1.0/(24*2), t.PRICE;

This uses Oracle syntax for adding half an hour because your query seems to use Oracle features.