M.Okumus M.Okumus - 12 days ago 7
SQL Question

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

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.

Answer

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])
Comments