I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

=====================

Let's not use box-and-arrow pictures.

===========

Given Data from the remote unit:

TimeAtStartOfEpoch  TimeOfSample        CurrentRunTimeInEpoch

And Epoch created as the data is processed onsight:

TimeAtStartOfEpoch  CumulativeRunTimeAtStartOfEpoch

============

Where "run time" is the run time of a vehicle "meter" but, the meter gets reset every once in a while as the vehicle is maintained (Epochs).

Let's say there already are some Epochs...

1. Join (all the Data table) with (all the Epoch table) so that Data with no Epoch shows up and can be used to create new Epochs.

2. Create missing Epochs (but with no CumulativeRunTime column values).

3. Join again and all data has Epochs.

4. Write some procedural code to scan the above join, calculate and save running, cumulative run time for Data and Epochs that have no such values.

Some observations:

A) I hope this is accurate and clear. It's been a while.

B) Create an initial attempt stripped of everything but very, very basic SQL and (TSQL) code.

C) Don't be afraid to initially let the DBMS handle the full cost of the above joins. You need the clarity to start. Layer in performance and data integrity as needed. Perhaps the joins have ("date" > "a month ago") phrases added to them after a while. As you add in performance and data integrity, if it doesn't work as expected you can retreat and figure out the cause.

D) Also, don't be afraid to code sequential passes through the data. If the problem requires two passes through the data, trying to "optimize" the solution by arranging things to do all the work in one pass is, false economy at many levels. It is, not SQL.

You have a great start there. Good luck.

Tom Alborough

I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

=====================

Let's not use box-and-arrow pictures.

===========

Given Data from the remote unit:

TimeAtStartOfEpoch  TimeOfSample        CurrentRunTimeInEpoch

And Epoch created as the data is processed onsight:

TimeAtStartOfEpoch  CumulativeRunTimeAtStartOfEpoch

============

Where "run time" is the run time of a vehicle "meter" but, the meter gets reset every once in a while as the vehicle is maintained (Epochs).

Let's say there already are some Epochs...

1. Join (all the Data table) with (all the Epoch table) so that Data with no Epoch shows up and can be used to create new Epochs.

2. Create missing Epochs (but with no CumulativeRunTime column values).

3. Join again and all data has Epochs.

4. Write some procedural code to scan the above join, calculate and save running, cumulative run time for Data and Epochs that have no such values.

Some observations:

A) I hope this is accurate and clear. It's been a while.

B) Create an initial attempt stripped of everything but very, very basic SQL and (TSQL) code.

C) Don't be afraid to initially let the DBMS handle the full cost of the above joins. You need the clarity to start. Layer in performance and data integrity as needed. Perhaps the joins have ("date" > "a month ago") phrases added to them after a while. As you add in performance and data integrity, if it doesn't work as expected you can retreat and figure out the cause.

D) Also, don't be afraid to code sequential passes through the data. If the problem requires two passes through the data, trying to "optimize" the solution by arranging things to do all the work in one pass is, false economy at many levels. It is, not SQL.

You have a great start there. Good luck.

Tom Alborough

9/5/2023

If this still needs answering: I'm seeing if I can share more details about the design - the product is patented in the US and Europe.

I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

=====================

Let's not use box-and-arrow pictures.

First, the data from the remote unit:

===========

Given Data from the remote unit:

TimeAtStartOfEpoch  TimeOfSample        CurrentRunTimeInEpoch

And Epoch created as the data is processed onsight:

TimeAtStartOfEpoch  CumulativeRunTimeAtStartOfEpoch

============

Where "run time" is the run time of a vehicle "meter" but, the meter gets reset every once in a while as the vehicle is maintained (Epochs).

Let's say there already are some Epochs...

1. Join (all the Data table) with (all the Epoch table) so that Data with no Epoch shows up and can be used to create new Epochs.

2. Create missing Epochs (but with no CumulativeRunTime column values).

3. Join again and all data has Epochs.

4. Write some procedural code to scan the above join, calculate and save running, cumulative run time for Data and Epochs that have no such values.

Some observations:

A) I hope this is accurate and clear. It's been a while.

B) Create an initial attempt stripped of everything but very, very basic SQL and (TSQL) code.

C) Don't be afraid to initially let the DBMS handle the full cost of the above joins. You need the clarity to start. Layer in performance and data integrity as needed. Perhaps the joins have ("date" > "a month ago") phrases added to them after a while. As you add in performance and data integrity, if it doesn't work as expected you can retreat and figure out the cause.

D) Also, don't be afraid to code sequential passes through the data. If the problem requires two passes through the data, trying to "optimize" the solution by arranging things to do all the work in one pass is, false economy at many levels. It is, not SQL.

You have a great start there. Good luck.

Tom Alborough

I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

=====================

Let's not use box-and-arrow pictures.

===========

Given Data from the remote unit:

TimeAtStartOfEpoch  TimeOfSample        CurrentRunTimeInEpoch

And Epoch created as the data is processed onsight:

TimeAtStartOfEpoch  CumulativeRunTimeAtStartOfEpoch

============

Where "run time" is the run time of a vehicle "meter" but, the meter gets reset every once in a while as the vehicle is maintained (Epochs).

Let's say there already are some Epochs...

1. Join (all the Data table) with (all the Epoch table) so that Data with no Epoch shows up and can be used to create new Epochs.

2. Create missing Epochs (but with no CumulativeRunTime column values).

3. Join again and all data has Epochs.

4. Write some procedural code to scan the above join, calculate and save running, cumulative run time for Data and Epochs that have no such values.

Some observations:

A) I hope this is accurate and clear. It's been a while.

B) Create an initial attempt stripped of everything but very, very basic SQL and (TSQL) code.

C) Don't be afraid to initially let the DBMS handle the full cost of the above joins. You need the clarity to start. Layer in performance and data integrity as needed. Perhaps the joins have ("date" > "a month ago") phrases added to them after a while. As you add in performance and data integrity, if it doesn't work as expected you can retreat and figure out the cause.

D) Also, don't be afraid to code sequential passes through the data. If the problem requires two passes through the data, trying to "optimize" the solution by arranging things to do all the work in one pass is, false economy at many levels. It is, not SQL.

You have a great start there. Good luck.

Tom Alborough

I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

I developed such a schema many years ago for a commercially available equipment tracking system where the data-gathering component turned off and on at odd times (epochs).

You are on the right track with epochs but I don't think the active flag is necessary as the schema / processing should be "good for all time".

Let me think how I did it and re-read your progress. I will need a day or so to get back into it and if no one else has thoughts, get back to you.

=====================

Let's not use box-and-arrow pictures.

First, the data from the remote unit:

===========

Given Data from the remote unit:

TimeAtStartOfEpoch  TimeOfSample        CurrentRunTimeInEpoch

And Epoch created as the data is processed onsight:

TimeAtStartOfEpoch  CumulativeRunTimeAtStartOfEpoch

============

Where "run time" is the run time of a vehicle "meter" but, the meter gets reset every once in a while as the vehicle is maintained (Epochs).

Let's say there already are some Epochs...

1. Join (all the Data table) with (all the Epoch table) so that Data with no Epoch shows up and can be used to create new Epochs.

2. Create missing Epochs (but with no CumulativeRunTime column values).

3. Join again and all data has Epochs.

4. Write some procedural code to scan the above join, calculate and save running, cumulative run time for Data and Epochs that have no such values.

Some observations:

A) I hope this is accurate and clear. It's been a while.

B) Create an initial attempt stripped of everything but very, very basic SQL and (TSQL) code.

C) Don't be afraid to initially let the DBMS handle the full cost of the above joins. You need the clarity to start. Layer in performance and data integrity as needed. Perhaps the joins have ("date" > "a month ago") phrases added to them after a while. As you add in performance and data integrity, if it doesn't work as expected you can retreat and figure out the cause.

D) Also, don't be afraid to code sequential passes through the data. If the problem requires two passes through the data, trying to "optimize" the solution by arranging things to do all the work in one pass is, false economy at many levels. It is, not SQL.

You have a great start there. Good luck.

Tom Alborough