storing sensor historical binary data for last 720 reads (SQL Server) [closed]

Question

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?

Answer 1

how to store this kind of data

In a relational database you use multiple rows, not columns.

eg:

create table SensorStatus
(
   SensorId int not null,
   StatusTime datetime2(0) not null,
   Status bit null,
   primary key (SensorId, StatusTime)
)

Or you could store one row per (day,sensor) and store the status by hour as a bitmask in an int, or as 24 bit columns.

   create table SensorStatus
    (
       SensorId int not null,
       StatusDate date not null,
       StatusByHour int not null default 0x00000000, 
       primary key (SensorId, StatusDate)
    )

or

create table SensorStatus
(
   SensorId int not null,
   StatusDate date not null,
   StatusHour0 bit not null default 0,
   StatusHour1 bit not null default 0,
   ...
   StatusHour23 bit not null default 0,
   primary key (SensorId, StatusDate)
)