CODEWITHSUNDEEP
sqlsql-servert-sql
Seán-Pól
Seán-Pól

Reputation: 23

duplicating rows for another period

I have a query as shown below:

select cast(DATEADD(day,-1,DATEADD(hour,1, INTERVAL_PERIOD_TIMESTAMP)) as date) as 'TradeDate'
   , 'P' as 'Currency' 
   , cast(DATEADD(hour,1, INTERVAL_PERIOD_TIMESTAMP) as date) as 'DeliveryDate'
   , cast(INTERVAL_PERIOD_TIMESTAMP as time) as 'DeliveryTime'
   , cast(CLEARED_DAM_PRICE as decimal(16,2)) as 'SMP'
   , 0 as 'TimeChange'
   , GETDATE() as 'DateAdded'
   from DAM_RESULT

This returns a list of results as follows:

2018-08-11  P   2018-08-12  23:00:00.0000000    37.12   0   2018-09-26 10:52:27.157
2018-08-11  P   2018-08-12  00:00:00.0000000    37.12   0   2018-09-26 10:52:27.157
2018-08-11  P   2018-08-12  01:00:00.0000000    37.12   0   2018-09-26 10:52:27.157
2018-08-11  P   2018-08-12  02:00:00.0000000    35.86   0   2018-09-26 10:52:27.157
2018-08-11  P   2018-08-12  03:00:00.0000000    35.79   0   2018-09-26 10:52:27.157
2018-08-11  P   2018-08-12  04:00:00.0000000    37.12   0   2018-09-26 10:52:27.157

At the moment I am getting granularity at one hour periods but a previous data model which I am trying to integrate with is based on half hourly granularity. The values below shown that 23:00 hours is 37.12 how can I amend my query so that another row is added for 23:30 hours at the same price?

Is there a better option than doing this by adding 30 minutes to the time and joining the two tables that are output?

Upvotes: 0

Views: 49

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269445

I would add in additional minutes to add. If I understand correctly:

select cast(DATEADD(day, -1, DATEADD(hour, 1, dr.INTERVAL_PERIOD_TIMESTAMP)) as date) as TradeDate,
       'P' as Currency,
       cast(DATEADD(minute, 60 + v.additional_minutes, dr.INTERVAL_PERIOD_TIMESTAMP) as date) as DeliveryDate,
       cast(dr.INTERVAL_PERIOD_TIMESTAMP as time) as DeliveryTime,
       cast(dr.CLEARED_DAM_PRICE as decimal(16, 2)) as SMP,
       0 as TimeChange,
       GETDATE() as DateAdded
from DAM_RESULT dr CROSS JOIN
     (VALUES (0), (30)) as v(additional_minutes);

Notice that I added a table alias and qualified all column references. Also, I removed the single quotes from the column names. Only use single quotes for string and date constants.

Upvotes: 1

Related Questions