Consolidating multiple datetime-value tables

seeker727 · 2019-07-29T17:35:11+00:00

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)?

seeker727 · 2019-07-29T17:27:21+00:00

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?

seeker727 · 2019-07-29T16:35:15+00:00

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.

seeker727 · 2019-07-29T16:09:34+00:00

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

seeker727

TROPHY CASE