SQL selects from multiple tables based on date and time

Question

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

For simplicity, tables can be divided into groups of 4-8 semi-linked tables. Semi-connected, I mean that they are data collection for the same element, but they have no real relationship with SQL. Each table consists of a date and time stamp ( datetime2data type), a value (maybe bit, intor floatdepending on a specific element) and some other columns that are not currently of interest. The date-time stamp is set every 15 minutes (for a quarter of an hour) for several seconds; however, not all data is recorded exactly at the same time ...

For instance:

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 tables will start from the same quarterly TIMESTAMP. Basically, I get a query that will return VALUE for each of the three tables for every 15-minute interval, starting with the earliest TIMESTAMPof the three tables. In the above example, I would like to start from 2014-11-27 07:15 (do not worry about seconds ... so you will need to specify a timestamp + 1 minute or so). Returning NULL for a value when there is no record for a specific TIMESTAMP, this is normal. So, the query for my above example will 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

Hope this makes sense. Any help / pointers / guidance would be appreciated.

Answer 1

, , .

UPDATE TABLENAME
  SET TIMESTAMP = dateadd(minute,datediff(minute,0,TIMESTAMP),0)

ALTER TABLE TABLENAME ADD COLUMN NORMTIME DATETIME;

UPDATE TABLENAME
  SET NORMTIME = dateadd(minute,datediff(minute,0,TIMESTAMP),0)

. : SQL-

---

(), , - 15 - . TIME_PERIOD EVENT_TIME ( , ).

​​ CTE, ROW_NUMBER(), . .

---

:

SELECT TP.EVENT_TIME, a.VALUE as VALUE1, b.VALUE as VALUE2, c.VALUE as VALUE3
FROM  TIME_PERIOD TP
LEFT JOIN TABLE1 a ON a.[TIMESTAMP] = TP.EVENT_TIME
LEFT JOIN TABLE2 b ON b.[TIMESTAMP] = TP.EVENT_TIME
LEFT JOIN TABLE3 c ON c.[TIMESTAMP] = TP.EVENT_TIME
WHERE COALESCE(a.[TIMESTAMP], b.[TIMESTAMP], c.[TIMESTAMP]) is not null
ORDER  BY TP.EVENT_TIME DESC 

, , ( , , ):

WHERE a.[TIMESTAMP] IS NOT NULL OR
      b.[TIMESTAMP] IS NOT NULL OR
      c.[TIMESTAMP] IS NOT NULL

Answer 2

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 

Answer 3

Here is an updated version of NoDisplayName's answer that does what you want. It works for SQL 2012, but you can replace the DATETIMEFROMPARTS function with a number of other functions to get the same result.

;WITH 
NewT1 as (
SELECT DATETimeFROMPARTS( DATEPART(year,Timestamp) , DATEPART(month,timestamp) , datepart(day,timestamp),datepart(hour,timestamp), datepart(minute,timestamp),0,0 ) as TimeStamp, Value
FROM Table1),
NewT2 as (
SELECT DATETimeFROMPARTS( DATEPART(year,Timestamp) , DATEPART(month,timestamp) , datepart(day,timestamp),datepart(hour,timestamp), datepart(minute,timestamp),0,0 ) as TimeStamp, Value
FROM Table2),
NewT3 as (
SELECT DATETimeFROMPARTS( DATEPART(year,Timestamp) , DATEPART(month,timestamp) , datepart(day,timestamp),datepart(hour,timestamp), datepart(minute,timestamp),0,0 ) as TimeStamp, Value
FROM Table3)
SELECT COALESCE(a.[TIMESTAMP], b.[TIMESTAMP], c.[TIMESTAMP]) [TIMESTAMPs],
       Isnull(Max(a.VALUE), 0)                            VALUE1,
       Isnull(Max(b.VALUE), 0)                                       VALUE2,
       Isnull(Max(c.VALUE), 0)                                       VALUE3
FROM   NewT1 a
       FULL OUTER JOIN NewT2 b
                    ON a.[TIMESTAMP] = b.[TIMESTAMP]
       FULL OUTER JOIN TABLE3 c
                    ON a.[TIMESTAMP] = b.[TIMESTAMP]
GROUP  BY COALESCE(a.[TIMESTAMP], b.[TIMESTAMP], c.[TIMESTAMP])
ORDER  BY [TIMESTAMPs]