CODEWITHSUNDEEP
sqlsql-serverjointimestamp
Ali
Ali

Reputation: 1336

Table joining issue in SQL Server with TIMESTAMP

I want to take current day details from three tables in SQL server. I can't join the tables with TIMESTAMP, the TIMESTAMP is different in every table. There is no common factor for joining these tables. Is there any way to join these tables. And also I can't modify the tables, those are coming from a third party service.

Queries:

SELECT * 
FROM table1 
WHERE CAST(TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) 
ORDER BY CAST(TIMESTAMP AS DATETIME2(0))

SELECT * 
FROM table2 
WHERE CAST(TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) 
ORDER BY CAST(TIMESTAMP AS DATETIME2(0))

SELECT * 
FROM table3
WHERE CAST(TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) 
ORDER BY CAST(TIMESTAMP AS DATETIME2(0))

SELECT 
    CAST(one.TIMESTAMP AS DATETIME2(0)) AS DATE,
    CONVERT(DECIMAL(10, 2), one.value) AS v1, 
    CONVERT(DECIMAL(10, 2), two.VALUE) AS v2, 
    CONVERT(DECIMAL(10, 2), three.VALUE) AS v3 
FROM
    table1 one 
LEFT JOIN 
    table2 two ON CAST(one.TIMESTAMP AS DATETIME2(0)) = CAST(two.TIMESTAMP AS DATETIME2(0)) 
LEFT JOIN 
    table3 three ON CAST(one.TIMESTAMP AS DATETIME2(0)) = CAST(three.TIMESTAMP AS DATETIME2(0))
WHERE
    CAST(one.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) 
ORDER BY
    CAST(one.TIMESTAMP AS DATETIME2(0))

Result:

enter image description here

I didn't get the 2nd table and 3rd table data. eg: 2018-12-30 00:00:15.657 this time from 3rd table first row. That is not showing in the result. I want the all data.

I hope you understand. Could you please help me to solve this.

Upvotes: 2

Views: 926

Answers (2)

donPablo
donPablo

Reputation: 1959

Or, just modify the WHERE

WHERE
    CAST(one.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) 
 OR CAST(two.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE) 
 OR CAST(three.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE)

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269793

You might want a full join:

SELECT COALESCE(CAST(one.TIMESTAMP AS DATETIME2(0)),
                CAST(two.TIMESTAMP AS DATETIME2(0)),
                CAST(three.TIMESTAMP AS DATETIME2(0))
               ) as dt,
       CONVERT(DECIMAL(10, 2), one.value) AS v1, 
       CONVERT(DECIMAL(10, 2), two.VALUE) AS v2, 
       CONVERT(DECIMAL(10, 2), three.VALUE) AS v3 
FROM (SELECT *
      FROM table1 one
      WHERE CAST(one.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE)
     ) one FULL JOIN 
     (SELECT *
      FROM table2 two
      WHERE CAST(two.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE)
     ) two
     ON CAST(two.TIMESTAMP AS DATETIME2(0)) = CAST(on.TIMESTAMP AS DATETIME2(0)) FULL JOIN
     (SELECT *
      FROM table3 three
      WHERE CAST(three.TIMESTAMP AS DATE) = CAST(GETDATE() AS DATE)
     ) three
     ON CAST(three.TIMESTAMP AS DATETIME2(0)) = COALESCE(CAST(one.TIMESTAMP AS DATETIME2(0)), CAST(one.TIMESTAMP AS DATETIME2(0)))
ORDER BY dt;

FULL JOINs are tricky with filtering, which is why this uses subqueries.

Upvotes: 2

Related Questions