Query report for an arduino project

SSAS and SSRS
1

Hi Everyone,

I'm working on a small Arduino project that aims to collect data off of push button state changes. I have finally reached the point where the device is successfully registering every state changes into a database. Now I need to use the data from the database to create some sort of report. This is how my database looks:

TABLE_1

create table switch(id int, board int, button int, time timestamp, value tinyint);
insert into switch values (67, 3, 2, '2016-12-31 00:18:01', 0);
insert into switch values (66, 3, 3, '2016-12-31 00:17:11', 0);
insert into switch values (64, 3, 3, '2016-12-31 00:06:41', 1);
insert into switch values (63, 3, 2, '2016-12-31 00:05:28', 1);
insert into switch values (68, 2, 2, '2016-12-31 00:19:32', 0);
insert into switch values (65, 2, 3, '2016-12-31 00:08:10', 0);
insert into switch values (62, 2, 3, '2016-12-30 23:58:18', 1);
insert into switch values (61, 2, 2, '2016-12-30 23:49:25', 1);
insert into switch values (60, 3, 2, '2016-12-30 23:35:28', 0);

TABLE_2

create table locations (id int, board int, button int, location varchar(13), value_0_funct varchar(13), value_1_funct varchar(13));
insert into locations values (1, 2, 2, 'door_1', 'closed', 'opened');
insert into locations values (2, 2, 3, 'door_1', 'operating', 'not operating');
insert into locations values (3, 3, 2, 'door_2', 'closed', 'opened');
insert into locations values (4, 3, 3, 'door_2', 'operating', 'not operating');

The state change always cycles in the same order - 1. Open, 2. Operating, 3. Not Operating, 4. Closed.....
I want to take a snapshot of every 10 minute time slot and show in what state the door was at that time. If the door was in more than one states within the 10 min window, I will consider the one it has spent the longest time in.

time_slot ___________________ door_1 __________ door_2
2016-12-31 00:20 - 00:30 _____ closed _________closed
2016-12-31 00:10 - 00:20 _____ not operating ___ operating
2016-12-31 00:00 - 00:10 _____ operating ______ closed
2016-12-30 23:50 - 00:00 _____ opened ________closed
2016-12-30 23:50 - 00:00 _____ opened ________closed
2016-12-30 23:40 - 23:50 _____ closed _________closed

Is this possible via an sql query?

Thanks,
Peter

2

Peter,

For a quick answer please post proper DML and DDL

create table #switch(id int, board int, button int, time timestamp, value tinyint)

insert into #switch
select 67, 3,	2,	'2016-12-31 00:18:01', 0 union
select 66,	3,	3,	'2016-12-31 00:17:11',	0

etc

1 Like
3

First post is updated as per instruction above...