The problem is that I am dealing with a source table that has around 5 million rows. (Around 5 rows per computer per day) My second query that creates #SourceTableStates will actually blow this table up from 5 millions to ~288 millions... because I slize the day up into 5 mins piece - 288 - instead of ~5 rows each containing a start and end time. So the actual end result table contains around 1 million rows.
So I really need to look into ways to no save the intermediate 288million table, but I am unsure how to do this with minimal extra processing.
Well I am taking from a table that contains data in starttime > endtime format for different events and want to aggregate them daily into a simply string, where each character in the string represents the event that took place during that time. The idea is that afterwards I can very quickly query this table and for instance create a timeline for that machine.
i.e the source contains. Was in state 5 from 0am to 12am and from 12am to 12pm in state 3. I now want a row that says 555555555555333333333333 In this example the time slize is 1h instead of 5mins. This will be more efficient permanent storage because some machines literally have 20-30 such events during a day.
Without actually creating any SQL i will offer two suggestions.
1) Single column of datatype varchar(X) where X = number of data points you need to collect and each position within the field represents the value of the data point. At each collection time you do some math to determine what position in the string needs to be updated and set that position equal to your event.
2) If you had fewer events and few data points to collect a consideration might be to use bit field and use bitwise operations in your selection query. Though with so many data points to collect the bit field probably couldn't handle the large value that would be necessary.