Joining Daily and Hourly data where daily data repeats for every hour

Question

I'm using SQL Server 2008

I have 2 tables;

1st. Currencies with 2 Columns: Date, USD (daily data)

Sample Data:

  
Date        USD     
2009-12-01  1,5005  
2009-12-02  1,4898  
2009-12-03  1,4802  
2009-12-04  1,4757

2nd. Prices with 2 Columns: Date,Price (Hourly data)

Sample Data:

  


Date                   Price
2009-12-01 00:00:00    169,44   
2009-12-01 01:00:00    139  
2009-12-01 02:00:00    70   
2009-12-01 03:00:00    65,89    
2009-12-01 04:00:00    66,47

What i want is joining these tables with data for every hour like:

  
Date                   Price       USD
2009-12-01 00:00:00    169,44   1,5005  
2009-12-01 01:00:00    139      1,5005  
2009-12-01 02:00:00    70       1,5005  
2009-12-01 03:00:00    65,89    1,5005  
2009-12-01 04:00:00    66,47    1,5005

Thanks.

Possible duplicate of [SQL join against date ranges?](http://stackoverflow.com/questions/2306462/sql-join-against-date-ranges) — Anurag_Soni, Nov 21 '16 at 12:50
maybe you should check this answer: http://stackoverflow.com/questions/1843395/compare-two-datetime-only-by-date-not-time-in-sql-server-2008 — JuanN, Nov 21 '16 at 12:51

score 0 · Answer 1 · answered Nov 21 '16 at 12:51

0

Should be simple. Since you're using 2008 you can use the DATE datatype, which has no time component, to match against:

SELECT Prices.Date,
       Prices.Price,
       Currencies.USD
FROM   Prices
       INNER JOIN Currencies
               ON Currencies.Date = CAST(Prices.Date AS DATE)

answered Nov 21 '16 at 12:51

Bridge

29,818
9
60
82

Thank you too. Now it is ok and understood. – M.Okumus Nov 21 '16 at 12:58

score 0 · Accepted Answer · answered Nov 21 '16 at 12:52

0

Just convert your prices table date column data type from datetime to date

 select B.DATE,B.PRICE,A.USD 
       from Currencies a join Prices b
          on a.[Date]=convert(date,b.[Date])

answered Nov 21 '16 at 12:52

Tharunkumar Reddy

2,773
18
32

1

Thanks a lot. This makes it. – M.Okumus Nov 21 '16 at 12:58
@M.Okumus Your welcome. And accept any one answer if you don't mind . – Tharunkumar Reddy Nov 21 '16 at 12:59

Joining Daily and Hourly data where daily data repeats for every hour

2 Answers2