I have a table (test) in SQL having the data as follows:
sampletime samplevalue
2017-05-10 12:17:02.000 100.00
2017-05-10 12:20:02.000 100.00
2017-05-10 12:23:02.000 100.00
2017-05-10 12:26:02.000 100.00
2017-05-10 12:29:04.000 0.00 //capture as start time
2017-05-10 12:32:02.000 0.00
2017-05-10 12:35:02.000 0.00
2017-05-10 12:38:02.000 0.00
2017-05-10 12:41:02.000 0.00
2017-05-10 12:44:02.000 100.00 //capture as end time with above start time
2017-05-10 12:47:02.000 100.00
2017-05-10 12:50:02.000 100.00
2017-05-10 12:53:02.000 100.00
2017-05-10 12:56:02.000 0.00 //capture as start time
2017-05-10 12:59:02.000 0.00
2017-05-10 13:02:02.000 0.00
2017-05-10 13:05:02.000 100.00 //capture as end time with above start time
2017-05-10 13:08:02.000 100.00
2017-05-10 13:11:02.000 0.00 //capture as start time
2017-05-10 13:14:02.000 100.00 //capture as end time with above start time
2017-05-10 13:17:02.000 100.00
2017-05-10 13:20:02.000 0.00 //capture as start time
2017-05-10 13:23:02.000 0.00
2017-05-10 13:26:02.000 100.00 //capture as end time with above start time
2017-05-10 13:29:02.000 100.00
2017-05-10 13:32:02.000 100.00
In this table, I want the query that will produce the following output:
start time end time
2017-05-10 12:29:04.000 2017-05-10 12:44:02.000
2017-05-10 12:56:02.000 2017-05-10 13:05:02.000
2017-05-10 13:11:02.000 2017-05-10 13:14:02.000
2017-05-10 13:20:02.000 2017-05-10 13:26:02.000
Means as soon as the samplevalue gets 0, it records that time as start time and as soon as it becomes 100 again, it records the time as end time.
How to design this query and build report in Jasperstudio for this?
Thanks & Regards,
Balram
