Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
daveybohh
Starting Member
5 Posts |
 Posted - 2011-11-23 : 10:20:22
|
| I have 2 databases, DB01 and DB02 and within both of these databases there are 2 identically named tables, table1 and table2. In all 4 of the tables (DB01.dbo.table1, DB01.dbo.table2, DB02.dbo.table1 and DB02.dbo.table2) there is a tstamp column as well as a data column, fld7. The tstamp is a 10minute Unix timestamp.I have the following code (thanks to this forum!):SELECT T1_A.tstamp, T1_A.fld7, T1_B.tstamp, T1_B.fld7, T2_A.tstamp, T2_A.fld7, T2_B.tstamp, T2_B.fld7FROM DB01.dbo.table1 T1_A, DB01.dbo.table2 T1_B, DB02.dbo.table1 T2_A, DB02.dbo.table2 T2_B WHERE T1_A.tstamp = T1_B.tstamp and T1_A.tstamp = T2_A.tstamp and T1_A.tstamp = T2_B.tstamp and T1_A.tstamp > 1317427200 and T1_A.tstamp < 1320105600 Order by T1_A.tstampMy problem comes when there is missing data within the dataset, ie there are missing 10minute timestamps with no data.Using my current “where” statement, if there is missing timestamp data in one table, but it is present in another table, then none of this data is included.I would ideally like any missing periods (ie missing timestamps) to be populated with NULL values. Therefore a full data set with all timestamps being returned. I am not sure if this is possible but any help or ideas would be greatly appreciated! |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2011-11-23 : 11:05:33
|
| Use T-SQL joins instead of ANSI standard joins and use a LEFT OUTER JOIN if the data is always missing from one table or a FULL OUTER JOIN if the data can be missing from either. |
 |
|
|
|
|
|
|
|
Populating missing data in series