How to Design this Query and Build Report in Jasperstudio

[balram.deswal_1]

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