0

I have a small requirement to solve.

We have data with every half an hour records starts with 27-09-2018 00:00:00.from the start of day.

Meter_id |Realtimeclock      |I_Y|I_B|I_X|
201010   |27-09-2018 00:00:00|1.0|2.0|3.0|
201010   |27-09-2018 00:30:00|1.0|2.0|3.0|
201010   |27-09-2018 01:00:00|1.0|2.0|3.0|
201010   |27-09-2018 01:30:00|1.0|2.0|3.0|
201010   |27-09-2018 02:00:00|1.0| 0 |3.0|
201010   |27-09-2018 02:30:00|1.0| 0 |0  |
201010   |27-09-2018 03:00:00|1.0|2.0|3.0|
201010   |27-09-2018 03:30:00|1.0|2.0|3.0|

From the above table we have to get data as 27-09-2018 00:00:00 of realtimeclock to 27-09-2018 01:30:00 of realtimeclock as one record of count, and see there was value 0 in I_B and I_X in both the timestamps 27-09-2018 02:00:00 and 27-09-2018 02:30:00 ,,so here we can skip these two records and continue with remaining data which starts at 27-09-2018 03:00:00 of realtimeclock and ends at 27-09-2018 03:30:00 and that should be represented as another record with count.

like here output should be,

Meter_id |start_time         |End_time           |I_Y|I_B|I_X|
201010   |27-09-2018 00:00:00|27-09-2018 01:30:00|4  |4  |4  |
201010   |27-09-2018 03:00:00|27-09-2018 03:30:00|2  |2  |2  |
Venki Venkat
  • 65
  • 2
  • 8
  • Meter_id |Realtimeclock |I_Y|I_B|I_X| 201010 |27-09-2018 00:00:00|1.0|2.0|3.0| 201010 |27-09-2018 00:30:00|1.0|2.0|3.0| 201010 |27-09-2018 01:00:00|1.0|2.0|3.0| 201010 |27-09-2018 01:30:00|1.0|2.0|3.0| 201010 |27-09-2018 02:00:00|1.0| 0 |3.0| 201010 |27-09-2018 02:30:00|1.0| 0 |0 | 201010 |27-09-2018 03:00:00|1.0|2.0|3.0| 201010 |27-09-2018 03:30:00|1.0|2.0|3.0| – Venki Venkat Sep 27 '18 at 06:49
  • 1
    Please **edit your question** to include clarifications or additional information. In particular, do not use comments for code or data, as the lack of formatting makes them impossible to read. – APC Sep 27 '18 at 06:53

2 Answers2

0

This is a gaps and islands problem. One approach uses the difference in row numbers method:

WITH cte1 AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Realtimeclock) rn
    FROM yourTable t
),
cte2 AS (
    SELECT t.*, ROW_NUMBER() OVER (PARTITION BY Meter_id ORDER BY Realtimeclock) rn
    FROM yourTable t
    WHERE I_B <> 0
),
cte3 AS (
    SELECT t1.*,
        t1.rn - t2.rn AS diff
    FROM cte1 t1
    INNER JOIN cte2 t2
        ON t1.Meter_id = t2.Meter_id AND t1.Realtimeclock = t2.Realtimeclock
)

SELECT
    Meter_id,
    MIN(Realtimeclock) AS start_time,
    MAX(Realtimeclock) AS end_time,
    COUNT(I_Y) AS I_Y,
    COUNT(I_B) AS I_B,
    COUNT(I_X) AS I_X
FROM cte3
GROUP BY
    Meter_id,
    diff;

enter image description here

Demo

Note: The demo is in SQL Server, but the code should also run on Oracle with no modifications. I struggle with Oracle's syntax when setting up demos sometimes.

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • Thank you so much Tim,Its working good,,,If you dont mind ,can you explain the code. – Venki Venkat Sep 27 '18 at 11:12
  • There is no easy way to explain my code. The best thing to do, if you really want to learn, would be to test each CTE, then build up the query piece by piece to see what is happening. – Tim Biegeleisen Sep 27 '18 at 11:45
  • oh ! Great Tom ,,,, – Venki Venkat Sep 27 '18 at 13:14
  • Thank you so much TOM, I understood the code at cte1 and cte2 ,But when I see cte3 there is condition likeSELECT t1.*, t1.rn - t2.rn AS diff FROM cte1 t1 INNER JOIN cte2 t2 ON t1.Meter_id = t2.Meter_id AND t1.Realtimeclock = t2.Realtimeclock; and you retrieved the final result from cte3 table ,,,how would it possibe. – Venki Venkat Sep 27 '18 at 13:16
  • Also can i print the data as Meter_id |start_time |End_time |I_Y|I_B|I_X|Spell| 201010 |27-09-2018 00:00:00|27-09-2018 01:30:00|4 |4 |4 | 1 | 201010 |27-09-2018 03:00:00|27-09-2018 03:30:00|2 |2 |2 | 2 | – Venki Venkat Sep 27 '18 at 13:21
  • The alias `diff` is the heart of the query. We group by the difference in row numbers, and this is how we identify the islands. – Tim Biegeleisen Sep 27 '18 at 13:21
0

I think either u need to write a stored procedure which will run a loop with specified conditions and get the records, something similar to this:

Row for each hour even if there is no record [duplicate]

or you execute a query every 1 hour or whatever time interval you need, passing all the updated data and get the result, in this case the query can be something like this:

select * from table_name where realtime between start_datetime and end_datetime and I_B != 0 and I_X !=0;

It will return an empty set if any condition is wrong.

Nilesh Jain
  • 106
  • 8