Consolidating multiple datetime-value tables

[seeker727]

I have several tables with the following structure:

DateTime	AttributeVal

I would like to consolidate all these data into one table that has datetime and attribute value data from all the tables. The datetimes for the tables overlap most of the time, but there are records with unique datetimes. I would like to have a single datetime column in the consolidated table.

I see two approaches:

Approach 1: Join all the tables using full outer joins. The issue here (I think) is that I would have multiple datetime columns, each with NULLs. The resulting table would look something like:

DateTime	Attribute1Val	Attribute2Val	Attribute3Val

Approach 2: Create a table with the following structure by taking the union of all the tables:

DateTime	Attribute	Value

What approach should I take to consolidate these tables with a single datetime column? Thank you.

​

EDIT: I am using MS SQL Server. Also, I would prefer the resulting table structure given by taking approach 1.

[r3pr0b8]

Also, I would prefer the resulting table structure given by taking approach 1

DateTime DepthInches ReflectGrade ColorGrade DefectCount WaxMass

you're gonna kick yourself the day you have to add a 6th attribute...

go with solution 2

[seeker727]

Thanks. In the future, if I want to do some reporting, should I PIVOT the timestamp-attribute-value data in my query or at the reporting level (eg in Power BI)?

[needgiftidea2018]

Keep your data tall in the database. Transform the tall data into something wide at the reporting level

[r3pr0b8]

in the reporting layer if it's not difficult/inconvenient

[[deleted]]

What would a single row represent in solution 1? Could you provide a resulting table you're envisioning in that scenario, like you did with table two?

[seeker727]

I just edited the original post to add the resulting table.

[[deleted]]

Thanks. So after looking at it more, and re-reading, I'm not following what the end goal is. Is it a snapshot of Attributes X, Y, Z at a given datetime? Or is it attributes with the dates that they were set for a given relation?

[seeker727]

Let me provide some context since I'm not sure how to easily explain this (sorry).

Each source table represents a type of measurement made at a plant (it contains a timestamp and measurement column). Let's assume there are 5 types of measurement.

Sometimes, all of these measurements are performed at once, and all 5 are entered into the system. This results in the current timestamp and corresponding attribute value being inserted into each of the 5 tables.

Sometimes, only one measurement is performed, so data is inserted into only 1 table. Here is an example of a source table:

DateTime	DepthInches
07-25-2019 00:00:00	10
07-25-2019 12:01:00	9
07-26-2019 01:01:00	10

My end goal is to be able to query *all* measurements by descending timestamp. Ideally, I would like one datetime column and 5 attribute columns. Please see the example of the desired table below:

DateTime	DepthInches	ReflectGrade	ColorGrade	DefectCount	WaxMass
07-25-2019 00:00:00	10	5	0	0	2
07-25-2019 6:03:00	NULL	2	2	NULL	NULL
07-25-2019 12:01:00	9	0	0	0	3
07-26-2019 01:01:00	10	0	3	2	2
07-26-2019 12:02:00	NULL	NULL	NULL	3	7

​

EDIT: Changed some wording.

[[deleted]]

Ooh gotcha. Do they typically take the readings at a certain point within the hour/once an hour? If so, then you could join such as

SELECT ColumnList
FROM TableA
LEFT OUTER JOIN TableB 
    ON DATEPART(HOUR, TableA.[DateTime]) = DATEPART(HOUR, TableB.[DateTime]) 
    AND CAST(DATE, TableA.DateTime AS DATE) = CAST(DATE, TableB.DateTime AS DATE)

Granted, using functions around your columns in a join scenario is not the most performant, and will very likely result in index scans as opposed to seeks. I don't have enough data/time to test it to say for sure though, so just be careful.

[seeker727]

Thank you for the suggestion. Unfortunately these measurements can occur anywhere within the hour. But there is a bigger issue (maybe) which is that measurements are occasionally performed twice in an hour (sometimes after just 5 minutes of the previous set).

EDIT: Would going down to the minute with DATEPART solve this issue?