I need to store binary data - online status (1,0 or NULL) of about 100k sensors. Data are refreshed every 1 hour and I need to store historical data for 30 days, which gives me 720 records per sensor. Sensor number may vary, some new sensors comes in, some old one are deleted.
I've came with a solution of creating a table with some IDs and 720 BIN columns. I write current data to next column (modulus 720) evey 1 hour. I'm storing current column number in another configuration table. And now querying for data for last 7 days is a nightmare. I did a procedure to calculate all the offsets and generate some dynamic SQL to return what I want, but the thing is now even more complicated, because I need to filter those data ie. by sensor group (joined from another table). I'd like to implement it on a power BI view but it doesn't allow to dynamically filter data retrieved by a storred procedures.
And the question is how to store this kind of data to have easy access to them with views?