CODEWITHSUNDEEP
sqlsql-serversql-server-2016hour
curns
curns

Reputation: 21

SQL divide hourly data into half hourly

I have a column which contains data from two separate sources - one set is pulled in at an hourly granularity and one by half hour. 

Datetime           Source    Value  
01/01/2018 14:00   A          20
01/01/2018 14:00   B          15
01/01/2018 14:30   B          11

I would like to create a new column with the net of these on a half hourly basis.

Datetime           Net Value
01/01/2018 14:00   35              --20+15
01/01/2018 14:30   31              --20+11

Any help is appreciated,

Thanks

Upvotes: 0

Views: 481

Answers (1)

APH
APH

Reputation: 4154

Have you tried just joining on date and hour, like this?

SELECT b.Datetime
    , ISNULL(a.val, 0) + ISNULL(b.val, 0)
FROM myTable b
JOIN myTable a
ON CAST(b.Datetime AS DATE) = CAST(a.Datetime AS DATE)
AND DATEPART(HOUR, b.Datetime) = DATEPART(HOUR, a.Datetime)
AND b.source = 'B' 
AND a.source = 'A'

From comments (thanks!): try it in this DB Fiddle.

Upvotes: 1

Related Questions