CODEWITHSUNDEEP
sqlsql-serverview
Ken
Ken

Reputation: 1

Combining timestamp from 2 tables

I am trying to combine 2 tables with similar timestamps but not equal. Can anyone help with this?

ChillerTable

+----------------+------------+-------------+
| t_stamp        | SupplyTemp | Return_temp |
|                |            |             |
| 3/5/2015 12:02 | 15         | 14          |
|                |            |             |
| 3/5/2015 12:45 | 10         | 9           |
|                |            |             |
| 3/5/2015 13:27 | 9          | 7           |
+----------------+------------+-------------+

and

+----------------+----------+-------------+
| t_stamp        | Weight   | Jacket_temp |
|                |          |             |
| 3/5/2015 12:15 | 1500     | 10          |
|                |          |             |
| 3/5/2015 13:02 | 1200     | 9           |
|                |          |             |
| 3/5/2015 13:14 | 900      | 8           |
+----------------+----------+-------------+

Result

+------------------+-------------+-------------+----------+-------------+
| Combined_t_stamp | Supply_temp | Return_temp | Weight   | Jacket_temp |
|                  |             |             |          |             |
| 3/5/2015 12:02   | 15          | 14          | NULL     | NULL        |
|                  |             |             |          |             |
| 3/5/2015 12:15   | NULL        | NULL        | 1500     | 10          |
|                  |             |             |          |             |
| 3/5/2015 12:45   | 10          | 9           | NULL     | NULL        |
|                  |             |             |          |             |
| 3/5/2015 13:02   | NULL        | NULL        | 1200     | 9           |
|                  |             |             |          |             |
| 3/5/2015 13:14   | NULL        | NULL        | 900      | 8           |
|                  |             |             |          |             |
| 3/5/2015 13:27   | 9           | 7           | NULL     | NULL        |
+------------------+-------------+-------------+----------+-------------+

Best I can do is:

 SELECT     dbo.ChillerTable.t_stamp AS Expr1, dbo.ChillerTable.*, dbo.Kettle32Table.*


    FROM         dbo.ChillerTable INNER JOIN
                          dbo.Kettle32Table ON dbo.ChillerTable.t_stamp = dbo.Kettle32Table.t_stamp


WHERE     (dbo.ChillerTable.t_stamp > CONVERT(DATETIME, '2015-03-19 00:00:00', 102))

But that only gives me only the rows that equal on the timestamp When I choose !=, it gives me more rows, but I want the timestamps from both tables to be in the same column Any help in the right direction would be appreciated. I might use a view for this in sql 2008 Thx, Ken

Upvotes: 0

Views: 188

Answers (3)

Iłya Bursov
Iłya Bursov

Reputation: 24146

here is query which will combine data from both tables into one:

SELECT t_stamp, SupplyTemp, Return_temp, null as Weight, null as Jacket_temp
FROM dbo.ChillerTable
UNION ALL
SELECT t_stamp, null, null, Weight, Jacket_temp
FROM dbo.Kettle32Table

if you want to merge rows with same timestamp you could extend it:

select t_stamp, max(SupplyTemp) as SupplyTemp, max(Return_temp) as Return_temp, max(Weight) as Weight, max(Jacket_temp) as Jacket_temp
from (
    SELECT t_stamp, SupplyTemp, Return_temp, null as Weight, null as Jacket_temp
    FROM dbo.ChillerTable
    UNION ALL
    SELECT t_stamp, null, null, Weight, Jacket_temp
    FROM dbo.Kettle32Table
) as a
group by t_stamp

Upvotes: 1

M.Ali
M.Ali

Reputation: 69504

SELECT COALESCE(t1.t_stamp, t2.t_stamp) t_stamp
      ,SupplyTemp
      ,Return_temp
      ,[Weight]
      ,Jacket_temp
FROM Table1 t1 FULL JOIN Table2 t2
ON t1.t_stamp = t2.t_stamp

Result

╔═════════════════════════╦════════════╦═════════════╦════════╦═════════════╗
║         t_stamp         ║ SupplyTemp ║ Return_temp ║ Weight ║ Jacket_temp ║
╠═════════════════════════╬════════════╬═════════════╬════════╬═════════════╣
║ 2015-03-05 12:02:00.000 ║ 15         ║ 14          ║ NULL   ║ NULL        ║
║ 2015-03-05 12:15:00.000 ║ NULL       ║ NULL        ║ 1500   ║ 10          ║
║ 2015-03-05 12:45:00.000 ║ 10         ║ 9           ║ NULL   ║ NULL        ║
║ 2015-03-05 13:02:00.000 ║ NULL       ║ NULL        ║ 1200   ║ 9           ║
║ 2015-03-05 13:14:00.000 ║ NULL       ║ NULL        ║ 900    ║ 8           ║
║ 2015-03-05 13:27:00.000 ║ 9          ║ 7           ║ NULL   ║ NULL        ║
╚═════════════════════════╩════════════╩═════════════╩════════╩═════════════╝

Upvotes: 2

horHAY
horHAY

Reputation: 788

I'm not sure exactly what you want but, the DATEPART function should help you in finding similar times from two tables

INNER JOIN bo.Kettle32Table ON DATEPART(HOUR, dbo.ChillerTable.t_stamp) = DATEPART(HOUR, dbo.Kettle32Table.t_stamp)

Upvotes: 0

Related Questions