SQL select from multiple tables based on datetime

Question

I am working on a script to analyze some data contained in thousands of tables on a SQL Server 2008 database.

For simplicity sakes, the tables can be broken down into groups of 4-8 semi-related tables. By semi-related I mean that they are data collections for the same item but they do not have any actual SQL relationship. Each table consists of a date-time stamp (datetime2 data type), value (can be a bit, int, or float depending on the particular item), and some other columns that are currently not of interest. The date-time stamp is set for every 15 minutes (on the quarter hour) within a few seconds; however, not all of the data is recorded precisely at the same time...

For example:

TABLE1:

TIMESTAMP                 VALUE
2014-11-27 07:15:00.390      1
2014-11-27 07:30:00.390      0
2014-11-27 07:45:00.373      0
2014-11-27 08:00:00.327      0

TABLE2:

TIMESTAMP                 VALUE
2014-11-19 08:00:07.880      0
2014-11-19 08:15:06.867      0.0979999974370003
2014-11-19 08:30:08.593      0.0979999974370003
2014-11-19 08:45:07.397      0.0979999974370003

TABLE3

TIMESTAMP                 VALUE
2014-11-27 07:15:00.390        0
2014-11-27 07:30:00.390        0
2014-11-27 07:45:00.373        1
2014-11-27 08:00:00.327        1

As you can see, not all of the tables will start with the same quarterly TIMESTAMP. Basically, what I am after is a query that will return the VALUE for each of the 3 tables for every 15 minute interval starting with the earliest TIMESTAMP out of the 3 tables. For the example given, I'd want to start at 2014-11-27 07:15 (don't care about seconds... thus, would need to allow for the timestamp to be +- 1 minute or so). Returning NULL for the value when there is no record for the particular TIMESTAMP is ok. So, the query for my listed example would return something like:

TIMESTAMP                 VALUE1   VALUE2             VALUE3
2014-11-27 07:15           1    NULL                  0
2014-11-27 07:30           0    NULL                  0
2014-11-27 07:45           0    NULL                  1
2014-11-27 08:00           0    NULL                  1
...
2014-11-19 08:00           0         0                        1
2014-11-19 08:15           0         0.0979999974370003       0
2014-11-19 08:30           0         0.0979999974370003       0
2014-11-19 08:45           0         0.0979999974370003       0

I hope this makes sense. Any help/pointers/guidance will be appreciated.

Answer 1

Use Full Outer Join

SELECT COALESCE(a.[TIMESTAMP], b.[TIMESTAMP], c.[TIMESTAMP]) [TIMESTAMP],
       Isnull(Max(a.VALUE), 0)                               VALUE1,
       Max(b.VALUE)                                          VALUE2,
       Isnull(Max(c.VALUE), 0)                               VALUE3
FROM   TABLE1 a
       FULL OUTER JOIN TABLE2 b
                    ON CONVERT(SMALLDATETIME, a.[TIMESTAMP]) = CONVERT(SMALLDATETIME, b.[TIMESTAMP])
       FULL OUTER JOIN TABLE3 c
                    ON CONVERT(SMALLDATETIME, a.[TIMESTAMP]) = CONVERT(SMALLDATETIME, c.[TIMESTAMP])
GROUP  BY COALESCE(a.[TIMESTAMP], b.[TIMESTAMP], c.[TIMESTAMP])
ORDER  BY [TIMESTAMP] DESC