Link to home
Start Free TrialLog in
Avatar of Mi-Jack
Mi-Jack

asked on

DB2 SQL - find uptime values in a table

Hi all,

I have hundreds of identical tables - one for every hour:
        I_YEAR INTEGER NOT NULL,
      I_MONTH INTEGER NOT NULL,
      I_DAY INTEGER NOT NULL,
      I_HOUR INTEGER NOT NULL,
      I_MINUTE INTEGER NOT NULL,
      I_SECOND INTEGER NOT NULL,
      I_MILLISECOND INTEGER NOT NULL,
      I_RADIO_ID INTEGER NOT NULL,
        ...
Every hour a new table is created.
Records are coming from working units, once a second.
When a unit is powered down, no records from that unit are sent.
If there are no records from a particular I_RADIO_ID for 3 minutes, the unit is considered powered down.
I want to create a single table, and parse each hourly table into it - just start-up and power down records for each I_RADIO_ID.
It would be something like this:
CREATE TABLE REPORT_UPTIME (
      I_YEAR INTEGER NOT NULL,
      I_MONTH INTEGER NOT NULL,
      I_DAY INTEGER NOT NULL,
      ...
      I_RADIO_ID INTEGER NOT NULL,
      I_TYPE INTEGER
)
with I_TYPE being - 0-power down, 1-startup.

The purpose is to create report where I could easily see which units are currently running, the last power down or start-up time etc - all in one table, without having to query multiple tables.

Any help is appreciated,

Thank you
Avatar of momi_sabag
momi_sabag
Flag of United States of America image

the simplest solution i can think off would be to modify the process that creates the hourly tables, and make it also create insert triggers on those tables.
the insert trigger will only be fired for power down and power up events, and add the same record into your report table
this might not be the best solution performance wise, but it is the simplest one to implement and maintain
Avatar of Mi-Jack
Mi-Jack

ASKER

>>momi_sabag
How would I know if it the inserted record is a power down,  power up or just normal work in between?
how many different units do you have?
Avatar of Kent Olsen
Hi Jack,

How many rows are in each table?  The solution would seem to be a "self join", where the table is joined to itself with a 1 second offset.  If you've got 10 work-units, there are potentially 36,000 rows in the table.  With 1,000 work-units, there are 3.6 million rows.  That's a lot to self join if the tables aren't indexed on the timestamp.
SELECT * 
FROM table t0
LEFT JOIN table t1
  ON t0.workunit = t1.workunit
 AND t0.timefield = t1.timefield - 1 second

Open in new window


Using that as a shell, you can find when a work-unit went offline by selecting the rows where t1.workunit is null.  From there, you can build up the rest of the data that you need.

Turning the JOIN around, you can also detect when a work-unit went back online.



Kent
Avatar of Mi-Jack

ASKER

>>momi_sabag
up to 200
Avatar of Mi-Jack

ASKER

>>Kdo
I was thinking more like parsing them 1 table at a time.
Inevitably, a unit will be powered down in one table, but started in another - but that is not a problem.
Avatar of Mi-Jack

ASKER

There's about 400,000 records per table, as only half of the units are operational at any given time
if you have only 200 units you can create a table that will have for each unit the last time that unit reported in
that value can be updated using that trigger i mentioned
once you have that table, it should be easy to determine for each incoming record if it is a power up record
when you get a power up record, you can generate a power down record by looking on the last report in record

was that clear?
Hi Jack,

The only issue is if if the unit went down or came up (a status change) on the first entry in the table.  You'll need to process the last timestamp of the previous table along with the current table to detect a change at the table switch.

If your entries look like

  yyyy-mm-dd.hh.mm.ss UNITxxxx  ONLINE
  yyyy-mm-dd.hh.mm.ss.UNITxxxx  OFLINE

then detecting the start and/or stop of the unit's recordings is sufficient to populate the data.


Kent
Avatar of Mi-Jack

ASKER

>>momi_sabag
That would mean each of 100/second inserted records will trigger additional action - this is way too costly! (if I understand what you said correctly - I am not very bright)
Avatar of Mi-Jack

ASKER

>>Kent
>>The only issue is if if the unit went down or came up (a status change) on the first entry >>in the table.
That is not a problem. If the unit went off line in the previous table, and the first record in the new table is its going on line, then there wouldn't be any entries in the report table that would violate the primary key (which should be such that it protects against just that case)
Avatar of Mi-Jack

ASKER

I just have year, day... radio_id in the hourly tables, nothing else. The presence or absence of a record from a unit is the only information.
Are the tables indexed?  With 400,000 rows and a 7 or 8 column join key, indexing would certainly be nice.

The biggest concern (to me) is the millisecond column.  It suggests that you don't really have a row every second, but rows that average to 1 a second.  If so, a simple self join just got a bit more complex as the join will have to be on the next logical row, no matter the time gap, and the SQL will need to include the maximum allowable gap before the unit is considered down.

Is it down if 3 seconds elapse without an entry?  30 seconds?  What if there is a hiccup and 10 seconds elapse, but there are really 3600 entries for that hour?   etc.


Kent
Avatar of Mi-Jack

ASKER

Yes, there's a composite key comprising 10 columns.
As I mentioned above, the unit is down if there's no communication for 3 minutes.
I need to select the last record before that gap, and the first one after - for each unit ID, in the space of just 1 table.
Can you give me an example of what that could look like?
Avatar of Mi-Jack

ASKER

I think this query gives me all instances after the gap.

select I_RADIO_ID, I_YEAR, I_MONTH, I_DAY, I_HOUR, I_MINUTE, (I_MINUTE - I_PRE_MINUTE) as Gaps
from (
select t1.*
     , (
        select max(I_MINUTE)
        from DBO.PT t2
        where t2.I_RADIO_ID = t1.I_RADIO_ID
        and t2.I_YEAR = t1.I_YEAR
        and t2.I_MONTH = t1.I_MONTH
        and t2.I_DAY = t1.I_DAY
        and t2.I_HOUR = t1.I_HOUR
        and t2.I_MINUTE < t1.I_MINUTE
     ) as I_PRE_MINUTE
from DBO.PT t1
) derived
where I_MINUTE - I_PRE_MINUTE > 3
group by I_RADIO_ID, I_YEAR, I_MONTH, I_DAY, I_HOUR, I_MINUTE, (I_MINUTE - I_PRE_MINUTE)

How can I get the before?

Please help
Avatar of Mi-Jack

ASKER

I've figured out the before:

select I_RADIO_ID, I_YEAR, I_MONTH, I_DAY, I_HOUR, I_MINUTE, (I_MINUTE - I_PRE_MINUTE) as Gaps
from (
select t1.*
     , (
        select min(I_MINUTE)
        from DBO.PT t2
        where t2.I_RADIO_ID = t1.I_RADIO_ID
        and t2.I_YEAR = t1.I_YEAR
        and t2.I_MONTH = t1.I_MONTH
        and t2.I_DAY = t1.I_DAY
        and t2.I_HOUR = t1.I_HOUR
        and t2.I_MINUTE > t1.I_MINUTE
     ) as I_PRE_MINUTE
from DBO.PT t1
) derived
where I_PRE_MINUTE - I_MINUTE > 3
group by I_RADIO_ID, I_YEAR, I_MONTH, I_DAY, I_HOUR, I_MINUTE, (I_PRE_MINUTE - I_MINUTE)

All I need now is to figure out is how to combine the two and cover all possible scenarios
Hi Jack,

Sorry, but I was away for a spell.

How strict is the 3-minute rule?  Can you live with a "moving" window of 2-3 or 3-4 minutes where the test is "was the unit active during this minute on the clock" and we use 3 consecutive 1-minute increments to answer the up/down question?


Kent
Avatar of Mi-Jack

ASKER

Hi Kent,
Yes, absolutely. I think if I have something to start with, there will be a way to fine-tune it later. It's a process.
There's also a question of when the unit was off in the previous hour and starts up again in the current table - then I don't know how to figure out that that was indeed a start-up.
Ok.  This is getting more straight-forward.  Once there is a solution, we can fine tune it if needed.

Try the query below on one of your tables.  It should run pretty quickly and show all of the units that went up and/or down during that period.

--
--  Collapse the time to a single 64-bit integer value.
--  Should result in a very manageable 12,000 rows.
--
WITH minute_data (unit, time)
AS
(
  SELECT DISTINCT I_radio_id,
    i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute
  FROM table
)
SELECT t0.*, "Unit went offline"
FROM minute_data t0
LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.time = t1.time - 1
WHERE t1.unit is NULL

UNION ALL

SELECT t0.*, "Unit went online"
FROM minute_data t0
RIGHT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.time = t1.time - 1
WHERE t0.unit is NULL;

Open in new window


Kent
Avatar of Mi-Jack

ASKER

To sum this up, there's only one problem:
1. unit is OFF in the previous hour
2. unit is turned ON - current hour
3. unit is OFF again - current hour

If I can recognize 2 as start and 3 as end - I have no problem.

I'm off for today.

Thank you for your help.
Avatar of Mi-Jack

ASKER

Sorry, I just saw your post.
I can't even figure out where to plug in the table name...
Replace the word "table" on line 10 with you table name.


Kent
Avatar of Mi-Jack

ASKER

I got 2200 records, with exactly have of them online (unit and time are all nulls), and half offline (both unit and time have valid values)
That's good.  :)  

All we tested for was 1 consecutive minute of downtime.  That displayed every 1-minute up/down event and did not include the units that were up (or down) the entire period.

 We need to make some SQL tuning but that suggests that we're on the right track.
Ok.  New query.  :)  This one should pick up only those rows where there is a 3+ minute gap and show the times when the unit went down or up.

--
--  Collapse the time to a single 64-bit integer value.
--  Should result in a very manageable 12,000 rows.
--
WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
		SELECT DISTINCT I_radio_id,
			i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time,
		FROM table
	) t2
)
SELECT t0.*, "Unit went offline"
FROM minute_data t0
LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn + 1
WHERE (t1.time - t0.time) >= 3

UNION ALL

SELECT t1.*, "Unit went online"
FROM minute_data t0
RIGHT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t1.time - t0.time) >= 3;

Open in new window



Kent
Avatar of Mi-Jack

ASKER

OK, I ran it. There are no  "Unit went offline" rows.
Here's some of the 886 rows:

UNIT      TIME      RN      4
1207      1207110604      2      Unit went online
1207      1207110607      3      Unit went online
1207      1207110610      4      Unit went online
1207      1207110613      5      Unit went online
1207      1207110616      6      Unit went online
1207      1207110619      7      Unit went online
1207      1207110622      8      Unit went online
1207      1207110625      9      Unit went online
1207      1207110628      10      Unit went online
1207      1207110631      11      Unit went online
1207      1207110634      12      Unit went online
1207      1207110637      13      Unit went online
1207      1207110640      14      Unit went online
1207      1207110643      15      Unit went online
1207      1207110646      16      Unit went online
1207      1207110649      17      Unit went online
1207      1207110652      18      Unit went online
1207      1207110655      19      Unit went online
1207      1207110658      20      Unit went online
1221      1207240605      4      Unit went online
1223      1207210604      2      Unit went online
1223      1207210607      3      Unit went online
1223      1207210610      4      Unit went online
1223      1207210613      5      Unit went online
1223      1207210616      6      Unit went online
1223      1207210619      7      Unit went online
1223      1207210622      8      Unit went online
1223      1207210625      9      Unit went online
This is why we debug.  :)

Let's see what the data really looks like.  Can you run this query and post the results?

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time,
    FROM table
  ) t2
)
SELECT * 
FROM minute_data 
WHERE unit = 1207
ORDER BY time;

Open in new window


I'm curious about the 3 minute interval that we're seeing and want to verify the data.
Avatar of Mi-Jack

ASKER

Here it is:

UNIT      TIME      RN
1207      1207110601      1
1207      1207110604      2
1207      1207110607      3
1207      1207110610      4
1207      1207110613      5
1207      1207110616      6
1207      1207110619      7
1207      1207110622      8
1207      1207110625      9
1207      1207110628      10
1207      1207110631      11
1207      1207110634      12
1207      1207110637      13
1207      1207110640      14
1207      1207110643      15
1207      1207110646      16
1207      1207110649      17
1207      1207110652      18
1207      1207110655      19
1207      1207110658      20
Avatar of Mi-Jack

ASKER

It's easy to see the pattern - time difference is 3.
BTW, is that seconds?
That's minutes.  Unit 1207 seems to be reporting in every 3 minutes.

Let's make sure.  Let's see how many items are in the table for this unit.

SELECT count(*) 
FROM table 
WHERE unit = 1207;

Open in new window

Avatar of Mi-Jack

ASKER

20
Avatar of Mi-Jack

ASKER

Looks like most the unit ID listed as a result of your query (ID: 38222887) have the count of 20!
I tried all of them now - some of them have other counts, much larger.
I also tried other units, not on the list, and count is in thousands
Ok.  What we're seeing is that the unit has reported in 20 times during that hour, once every three minutes.  Unit 1223 follows the same pattern, but isn't up for the entire hour.  Do all of the units follow this same pattern?  

SELECT count(*) FROM table;

SELECT count(*) FROM (select distinct unit FROM table) t0;

Open in new window



This could change things a bit.  If all of the units report in every 3 minutes, it might be that a small timing delay of less than a second could make it appear that a report is missing if the delay causes the report to appears as if it's 4 minutes after the previous.  That's easy enough to handle, but we need to know if 3 minutes is the norm before making that call.
That does change things a bit.  Some units seem to be reporting every 3 minutes, others down to possibly the second level.  If 3 minutes is the maximum interval, using a 4 minute window should work.  The only caveat is that if a unit reports every second, you won't detect missed reports where the span is less than 3 minutes.
Avatar of Mi-Jack

ASKER

I'm really stupid, sorry. Those units listed are something else, they are what we call dark machines.
I have a process that inserts those records every 3 minutes. They have different i_code.
I am modifying your original query now.
Here's the query now:
WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
            SELECT DISTINCT I_radio_id,
                  i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
            FROM DBO.PositionRecord2012072410 where i_code=0
      ) t2
)
SELECT t0.*, 'Unit went offline'
FROM minute_data t0
LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn + 1
WHERE (t1.time - t0.time) >= 3
UNION ALL
SELECT t1.*, 'Unit went online'
FROM minute_data t0
RIGHT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t1.time - t0.time) >= 3;

and here is the results:
UNIT      TIME      RN      4
1221      1207240605      4      Unit went online
1227      1207240615      14      Unit went online
1227      1207240641      34      Unit went online
1228      1207240654      52      Unit went online
1233      1207240628      26      Unit went online
1234      1207240617      16      Unit went online
1234      1207240627      23      Unit went online
1238      1207240637      33      Unit went online
1238      1207240650      43      Unit went online
1251      1207240606      4      Unit went online
1251      1207240631      23      Unit went online
1251      1207240652      40      Unit went online
1252      1207240620      18      Unit went online
1258      1207240628      26      Unit went online
1260      1207240641      37      Unit went online
1262      1207240649      47      Unit went online
1265      1207240629      16      Unit went online
1267      1207240622      20      Unit went online
1270      1207240632      22      Unit went online
1270      1207240637      25      Unit went online
1272      1207240621      19      Unit went online
1273      1207240612      10      Unit went online
1274      1207240626      16      Unit went online
1274      1207240638      25      Unit went online
1276      1207240655      53      Unit went online
1277      1207240642      27      Unit went online
1282      1207240649      45      Unit went online
1282      1207240655      47      Unit went online
1286      1207240611      10      Unit went online
1286      1207240644      41      Unit went online
1286      1207240659      52      Unit went online
1287      1207240621      20      Unit went online
1287      1207240640      36      Unit went online
1291      1207240612      11      Unit went online
1291      1207240648      36      Unit went online
1291      1207240652      38      Unit went online
1292      1207240640      38      Unit went online
1293      1207240614      13      Unit went online
1293      1207240635      27      Unit went online
1297      1207240631      28      Unit went online
1297      1207240647      42      Unit went online
1301      1207240616      13      Unit went online
1305      1207240608      5      Unit went online
1305      1207240620      15      Unit went online
1308      1207240660      54      Unit went online
1309      1207240642      22      Unit went online
1311      1207240609      5      Unit went online
1311      1207240646      39      Unit went online
1311      1207240655      43      Unit went online
1313      1207240612      5      Unit went online
1313      1207240624      14      Unit went online
1315      1207240612      9      Unit went online
1318      1207240653      49      Unit went online
1319      1207240620      18      Unit went online
1319      1207240643      37      Unit went online
1320      1207240617      12      Unit went online
1320      1207240622      14      Unit went online
1323      1207240614      13      Unit went online
1323      1207240657      52      Unit went online
1324      1207240617      14      Unit went online
1326      1207240642      30      Unit went online
1328      1207240624      2      Unit went online
1328      1207240651      27      Unit went online
1329      1207240656      54      Unit went online
1332      1207240647      40      Unit went online
1334      1207240660      56      Unit went online
1335      1207240636      33      Unit went online
1335      1207240659      54      Unit went online
1354      1207240640      38      Unit went online
1366      1207240612      4      Unit went online
1366      1207240629      15      Unit went online
1366      1207240641      22      Unit went online
1366      1207240650      27      Unit went online
1367      1207240631      30      Unit went online
1371      1207240641      36      Unit went online
1371      1207240653      46      Unit went online
1375      1207240615      12      Unit went online
1375      1207240636      31      Unit went online
1375      1207240646      36      Unit went online
1377      1207240614      12      Unit went online
1377      1207240636      32      Unit went online
1378      1207240624      23      Unit went online
1379      1207240614      12      Unit went online
1382      1207240614      10      Unit went online
1384      1207240642      41      Unit went online
1385      1207240623      19      Unit went online
1386      1207240611      7      Unit went online
1387      1207240654      29      Unit went online
1388      1207240643      36      Unit went online

I'm sorry
Avatar of Mi-Jack

ASKER

Sorry.
It's 84 rows now - much closer to what I'd expect.
Ok.  :)

What that report shows is the unit number, a "computed" timestamp, the relative row number (by unit) where the status changed, and the change type (though we're missing the offline entries).

The compute timestamp in the first row is:

   1207240605

  20012/07/24, 605 minutes into the day (10:05AM).

The report is tolerant of a table that doesn't start/end exactly at the top of the hour, but there could be a reporting issue if the midnight is spanned in a table.  (We can address that later.)

Let's try another query.  I'm suspicious that this will give us too many rows, but let's try it.

--
--  Collapse the time to a single 64-bit integer value.
--  Should result in a very manageable 12,000 rows.
--
WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
		SELECT DISTINCT I_radio_id,
			i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time,
		FROM table
	) t2
)
SELECT t0.*, "Unit went offline"
FROM minute_data t0
LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn + 1
WHERE (t1.time - t0.time) >= 3

UNION ALL

SELECT t1.*, "Unit went online"
FROM minute_data t0
RIGHT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t1.time - t0.time) >= 3
  OR t0.unit is NULL
ORDER BY 1, 2;

Open in new window

Avatar of Mi-Jack

ASKER

That results in 199 rows, here's a portion:

UNIT      TIME      RN      4
1209      1207240600      1      Unit went online
1221      1207240600      1      Unit went online
1221      1207240605      4      Unit went online
1227      1207240600      1      Unit went online
1227      1207240615      14      Unit went online
1227      1207240641      34      Unit went online
1228      1207240600      1      Unit went online
1228      1207240654      52      Unit went online
1230      1207240600      1      Unit went online
1231      1207240600      1      Unit went online
1233      1207240600      1      Unit went online
1233      1207240628      26      Unit went online
1234      1207240600      1      Unit went online
1234      1207240617      16      Unit went online
1234      1207240627      23      Unit went online
1235      1207240600      1      Unit went online
1236      1207240602      1      Unit went online
1237      1207240600      1      Unit went online
1238      1207240600      1      Unit went online
1238      1207240637      33      Unit went online
1238      1207240650      43      Unit went online
1242      1207240600      1      Unit went online
1244      1207240600      1      Unit went online
1245      1207240600      1      Unit went online
1246      1207240600      1      Unit went online
1247      1207240600      1      Unit went online
1249      1207240600      1      Unit went online
1251      1207240600      1      Unit went online
1251      1207240606      4      Unit went online
1251      1207240631      23      Unit went online
1251      1207240652      40      Unit went online
1252      1207240600      1      Unit went online
1252      1207240620      18      Unit went online
1254      1207240606      1      Unit went online
1255      1207240600      1      Unit went online
1257      1207240600      1      Unit went online
1258      1207240600      1      Unit went online
1258      1207240628      26      Unit went online
1260      1207240600      1      Unit went online
1260      1207240641      37      Unit went online
1261      1207240601      1      Unit went online
1262      1207240600      1      Unit went online
1262      1207240649      47      Unit went online
1264      1207240600      1      Unit went online
1265      1207240600      1      Unit went online
1265      1207240629      16      Unit went online
1267      1207240600      1      Unit went online
1267      1207240622      20      Unit went online
1268      1207240600      1      Unit went online
1269      1207240600      1      Unit went online
1270      1207240600      1      Unit went online
1270      1207240632      22      Unit went online
1270      1207240637      25      Unit went online
1271      1207240601      1      Unit went online
1272      1207240600      1      Unit went online
1272      1207240621      19      Unit went online
1273      1207240601      1      Unit went online
1273      1207240612      10      Unit went online
1274      1207240600      1      Unit went online
1274      1207240626      16      Unit went online
1274      1207240638      25      Unit went online
1276      1207240600      1      Unit went online
1276      1207240655      53      Unit went online
1277      1207240613      1      Unit went online
1277      1207240642      27      Unit went online
1282      1207240600      1      Unit went online
...
Avatar of Mi-Jack

ASKER

out for about 1.5 hours
thank you!
Hmm...  that looks a little odd still.

So let's change the selection approach.  :)

--
--  Collapse the time to a single 64-bit integer value.
--  Should result in a very manageable 12,000 rows.
--
WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
		SELECT DISTINCT I_radio_id,
			i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time,
		FROM table
	) t2
)
SELECT t0.*, t1.*
  CASE "Unit went offline"
FROM minute_data t0
FULL LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is NULL 
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0,time, t1.time);

Open in new window

Avatar of Mi-Jack

ASKER

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
            SELECT DISTINCT I_radio_id,
                  i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
            FROM DBO.PositionRecord2012072410  where i_code=0
      ) t2
)
SELECT t0.*, t1.*
  CASE 'Unit went offline'
FROM minute_data t0
FULL LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is NULL
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0,time, t1.time);

getting an error:
ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CASE;)
SELECT
t0.*, t1.*
;,, DRIVER=3.57.82
Error Code: -104
Apologies, didn't finish the CASE statement.

This should be pretty close, except for the first/last items in the period.  Filtering those will be the last step.

--
--  Collapse the time to a single 64-bit integer value.
--  Should result in a very manageable 12,000 rows.
--
WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
		SELECT DISTINCT I_radio_id,
			i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time,
		FROM table
	) t2
)
SELECT t0.*, t1.*
  CASE WHEN t0.unit is NULL then "Unit came online"
       WHEN t1.unit is NULL then "Unit went offline"
       ELSE "*" END
  END
FROM minute_data t0
FULL LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is NULL 
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0,time, t1.time)

Open in new window

Avatar of Mi-Jack

ASKER

Still an error:
ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=CASE;)
SELECT
t0.*, t1.*
;,, DRIVER=3.57.82
Error Code: -104


Here's the query exactly as I ran it:
WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
            SELECT DISTINCT I_radio_id,
                  i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
            FROM DBO.PositionRecord2012072410  where i_code=0
      ) t2
)
SELECT t0.*, t1.*
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
  END
FROM minute_data t0
FULL LEFT JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is NULL
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0,time, t1.time)
Sorry again...

Put a comma after

SELECT t0.*, t1.*
Avatar of Mi-Jack

ASKER

Actually, I tried that before. Here's the error:

ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=LEFT;minute_data
t0
FULL;<space>, DRIVER=3.57.82
Error Code: -104
This is what happens when you (me/I) can't test the code before posting.  :(

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is NULL
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0,time, t1.time)
Avatar of Mi-Jack

ASKER

I got it, here's the query:

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is NULL
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

and the result: (278 rows)

UNIT      TIME      RN      UNIT      TIME      RN      7
null      null      null      1209      1207240600      1      Unit came online
1209      1207240660      61      null      null      null      Unit went offline
null      null      null      1221      1207240600      1      Unit came online
1221      1207240660      58      null      null      null      Unit went offline
null      null      null      1227      1207240600      1      Unit came online
1227      1207240635      33      1227      1207240641      34      *
1227      1207240660      52      null      null      null      Unit went offline
null      null      null      1228      1207240600      1      Unit came online
1228      1207240650      51      1228      1207240654      52      *
1228      1207240659      57      null      null      null      Unit went offline
null      null      null      1230      1207240600      1      Unit came online
1230      1207240660      61      null      null      null      Unit went offline
null      null      null      1231      1207240600      1      Unit came online
1231      1207240660      61      null      null      null      Unit went offline
null      null      null      1233      1207240600      1      Unit came online
1233      1207240658      54      null      null      null      Unit went offline
null      null      null      1234      1207240600      1      Unit came online
1234      1207240623      22      1234      1207240627      23      *
1234      1207240660      56      null      null      null      Unit went offline
null      null      null      1235      1207240600      1      Unit came online
1235      1207240660      61      null      null      null      Unit went offline
null      null      null      1236      1207240602      1      Unit came online
1236      1207240660      57      null      null      null      Unit went offline
null      null      null      1237      1207240600      1      Unit came online
1237      1207240660      61      null      null      null      Unit went offline
null      null      null      1238      1207240600      1      Unit came online
1238      1207240631      32      1238      1207240637      33      *
1238      1207240646      42      1238      1207240650      43      *
1238      1207240660      52      null      null      null      Unit went offline
null      null      null      1242      1207240600      1      Unit came online
1242      1207240660      60      null      null      null      Unit went offline
null      null      null      1244      1207240600      1      Unit came online
1244      1207240660      60      null      null      null      Unit went offline
null      null      null      1245      1207240600      1      Unit came online
1245      1207240660      61      null      null      null      Unit went offline
null      null      null      1246      1207240600      1      Unit came online
1246      1207240660      61      null      null      null      Unit went offline
null      null      null      1247      1207240600      1      Unit came online
1247      1207240608      9      null      null      null      Unit went offline
null      null      null      1249      1207240600      1      Unit came online
1249      1207240646      47      null      null      null      Unit went offline
null      null      null      1251      1207240600      1      Unit came online
1251      1207240602      3      1251      1207240606      4      *
1251      1207240626      22      1251      1207240631      23      *
1251      1207240647      39      1251      1207240652      40      *
1251      1207240660      47      null      null      null      Unit went offline
null      null      null      1252      1207240600      1      Unit came online
1252      1207240660      55      null      null      null      Unit went offline
null      null      null      1254      1207240606      1      Unit came online
1254      1207240610      5      null      null      null      Unit went offline
null      null      null      1255      1207240600      1      Unit came online
1255      1207240660      61      null      null      null      Unit went offline
null      null      null      1257      1207240600      1      Unit came online
1257      1207240660      60      null      null      null      Unit went offline
null      null      null      1258      1207240600      1      Unit came online
1258      1207240624      25      1258      1207240628      26      *
1258      1207240660      58      null      null      null      Unit went offline
null      null      null      1260      1207240600      1      Unit came online
1260      1207240636      36      1260      1207240641      37      *
1260      1207240660      55      null      null      null      Unit went offline
null      null      null      1261      1207240601      1      Unit came online
1261      1207240660      59      null      null      null      Unit went offline
null      null      null      1262      1207240600      1      Unit came online
1262      1207240645      46      1262      1207240649      47      *
1262      1207240660      58      null      null      null      Unit went offline
null      null      null      1264      1207240600      1      Unit came online
1264      1207240660      61      null      null      null      Unit went offline
null      null      null      1265      1207240600      1      Unit came online
1265      1207240614      15      1265      1207240629      16      *
1265      1207240660      47      null      null      null      Unit went offline
null      null      null      1267      1207240600      1      Unit came online
1267      1207240660      56      null      null      null      Unit went offline
null      null      null      1268      1207240600      1      Unit came online
1268      1207240660      61      null      null      null      Unit went offline
null      null      null      1269      1207240600      1      Unit came online
1269      1207240660      61      null      null      null      Unit went offline
null      null      null      1270      1207240600      1      Unit came online
1270      1207240620      21      1270      1207240632      22      *
1270      1207240660      48      null      null      null      Unit went offline
null      null      null      1271      1207240601      1      Unit came online
1271      1207240661      61      null      null      null      Unit went offline
null      null      null      1272      1207240600      1      Unit came online
1272      1207240617      18      1272      1207240621      19      *
1272      1207240660      57      null      null      null      Unit went offline
null      null      null      1273      1207240601      1      Unit came online
1273      1207240660      58      null      null      null      Unit went offline
null      null      null      1274      1207240600      1      Unit came online
1274      1207240614      15      1274      1207240626      16      *
1274      1207240634      24      1274      1207240638      25      *
1274      1207240653      39      null      null      null      Unit went offline
null      null      null      1276      1207240600      1      Unit came online
1276      1207240660      58      null      null      null      Unit went offline
null      null      null      1277      1207240613      1      Unit came online
1277      1207240654      39      null      null      null      Unit went offline
null      null      null      1282      1207240600      1      Unit came online
1282      1207240644      44      1282      1207240649      45      *
1282      1207240650      46      1282      1207240655      47      *
1282      1207240660      51      null      null      null      Unit went offline
null      null      null      1283      1207240600      1      Unit came online
1283      1207240660      59      null      null      null      Unit went offline
null      null      null      1284      1207240599      1      Unit came online
1284      1207240658      59      null      null      null      Unit went offline
null      null      null      1285      1207240600      1      Unit came online
1285      1207240660      60      null      null      null      Unit went offline
null      null      null      1286      1207240600      1      Unit came online
1286      1207240654      51      1286      1207240659      52      *
1286      1207240660      53      null      null      null      Unit went offline
null      null      null      1287      1207240600      1      Unit came online
1287      1207240636      35      1287      1207240640      36      *
1287      1207240660      56      null      null      null      Unit went offline
null      null      null      1289      1207240600      1      Unit came online
1289      1207240660      61      null      null      null      Unit went offline
null      null      null      1291      1207240600      1      Unit came online
1291      1207240636      35      1291      1207240648      36      *
1291      1207240660      46      null      null      null      Unit went offline
null      null      null      1292      1207240600      1      Unit came online
1292      1207240660      55      null      null      null      Unit went offline
null      null      null      1293      1207240600      1      Unit came online
1293      1207240627      26      1293      1207240635      27      *
1293      1207240660      51      null      null      null      Unit went offline
null      null      null      1294      1207240601      1      Unit came online
1294      1207240660      59      null      null      null      Unit went offline
null      null      null      1296      1207240600      1      Unit came online
1296      1207240660      61      null      null      null      Unit went offline
null      null      null      1297      1207240600      1      Unit came online
1297      1207240626      27      1297      1207240631      28      *
1297      1207240657      52      null      null      null      Unit went offline
null      null      null      1298      1207240601      1      Unit came online
1298      1207240660      59      null      null      null      Unit went offline
null      null      null      1301      1207240600      1      Unit came online
1301      1207240611      12      1301      1207240616      13      *
1301      1207240659      55      null      null      null      Unit went offline
null      null      null      1303      1207240600      1      Unit came online
1303      1207240660      61      null      null      null      Unit went offline
null      null      null      1305      1207240600      1      Unit came online
1305      1207240603      4      1305      1207240608      5      *
1305      1207240660      54      null      null      null      Unit went offline
null      null      null      1306      1207240612      1      Unit came online
1306      1207240660      49      null      null      null      Unit went offline
null      null      null      1307      1207240600      1      Unit came online
1307      1207240660      60      null      null      null      Unit went offline
null      null      null      1308      1207240600      1      Unit came online
1308      1207240654      53      1308      1207240660      54      *
1308      1207240660      54      null      null      null      Unit went offline
null      null      null      1309      1207240601      1      Unit came online
1309      1207240621      21      1309      1207240642      22      *
1309      1207240656      36      null      null      null      Unit went offline
null      null      null      1310      1207240600      1      Unit came online
1310      1207240660      61      null      null      null      Unit went offline
null      null      null      1311      1207240600      1      Unit came online
1311      1207240603      4      1311      1207240609      5      *
1311      1207240642      38      1311      1207240646      39      *
1311      1207240649      42      1311      1207240655      43      *
1311      1207240656      44      null      null      null      Unit went offline
null      null      null      1313      1207240603      1      Unit came online
1313      1207240606      4      1313      1207240612      5      *
1313      1207240620      13      1313      1207240624      14      *
1313      1207240660      50      null      null      null      Unit went offline
null      null      null      1315      1207240600      1      Unit came online
1315      1207240607      8      1315      1207240612      9      *
1315      1207240660      56      null      null      null      Unit went offline
null      null      null      1318      1207240600      1      Unit came online
1318      1207240648      48      1318      1207240653      49      *
1318      1207240660      56      null      null      null      Unit went offline
null      null      null      1319      1207240600      1      Unit came online
1319      1207240616      17      1319      1207240620      18      *
1319      1207240638      36      1319      1207240643      37      *
1319      1207240660      54      null      null      null      Unit went offline
null      null      null      1320      1207240600      1      Unit came online
1320      1207240610      11      1320      1207240617      12      *
1320      1207240618      13      1320      1207240622      14      *
1320      1207240660      52      null      null      null      Unit went offline
null      null      null      1321      1207240605      1      Unit came online
1321      1207240657      52      null      null      null      Unit went offline
null      null      null      1322      1207240600      1      Unit came online
1322      1207240660      60      null      null      null      Unit went offline
null      null      null      1323      1207240600      1      Unit came online
1323      1207240659      54      null      null      null      Unit went offline
null      null      null      1324      1207240600      1      Unit came online
1324      1207240612      13      1324      1207240617      14      *
1324      1207240660      57      null      null      null      Unit went offline
null      null      null      1325      1207240600      1      Unit came online
1325      1207240660      59      null      null      null      Unit went offline
null      null      null      1326      1207240600      1      Unit came online
1326      1207240628      29      1326      1207240642      30      *
1326      1207240660      48      null      null      null      Unit went offline
1328      1207240608      1      1328      1207240624      2      *
null      null      null      1328      1207240608      1      Unit came online
1328      1207240660      36      null      null      null      Unit went offline
null      null      null      1329      1207240600      1      Unit came online
1329      1207240660      58      null      null      null      Unit went offline
null      null      null      1331      1207240600      1      Unit came online
1331      1207240660      61      null      null      null      Unit went offline
null      null      null      1332      1207240600      1      Unit came online
1332      1207240639      39      1332      1207240647      40      *
1332      1207240660      53      null      null      null      Unit went offline
null      null      null      1333      1207240600      1      Unit came online
1333      1207240660      61      null      null      null      Unit went offline
null      null      null      1334      1207240600      1      Unit came online
1334      1207240656      55      1334      1207240660      56      *
1334      1207240660      56      null      null      null      Unit went offline
null      null      null      1335      1207240600      1      Unit came online
1335      1207240631      32      1335      1207240636      33      *
1335      1207240660      55      null      null      null      Unit went offline
null      null      null      1337      1207240600      1      Unit came online
1337      1207240660      61      null      null      null      Unit went offline
null      null      null      1339      1207240618      1      Unit came online
1339      1207240619      2      null      null      null      Unit went offline
null      null      null      1340      1207240600      1      Unit came online
1340      1207240660      61      null      null      null      Unit went offline
null      null      null      1341      1207240600      1      Unit came online
1341      1207240660      61      null      null      null      Unit went offline
null      null      null      1343      1207240600      1      Unit came online
1343      1207240660      60      null      null      null      Unit went offline
null      null      null      1344      1207240600      1      Unit came online
1344      1207240660      61      null      null      null      Unit went offline
null      null      null      1350      1207240600      1      Unit came online
1350      1207240660      61      null      null      null      Unit went offline
null      null      null      1351      1207240600      1      Unit came online
1351      1207240660      60      null      null      null      Unit went offline
null      null      null      1353      1207240600      1      Unit came online
1353      1207240660      61      null      null      null      Unit went offline
null      null      null      1354      1207240600      1      Unit came online
1354      1207240660      58      null      null      null      Unit went offline
null      null      null      1355      1207240600      1      Unit came online
1355      1207240660      60      null      null      null      Unit went offline
null      null      null      1357      1207240600      1      Unit came online
1357      1207240660      59      null      null      null      Unit went offline
null      null      null      1359      1207240600      1      Unit came online
1359      1207240660      59      null      null      null      Unit went offline
null      null      null      1364      1207240663      1      Unit came online
1364      1207240720      58      null      null      null      Unit went offline
null      null      null      1366      1207240600      1      Unit came online
1366      1207240602      3      1366      1207240612      4      *
1366      1207240622      14      1366      1207240629      15      *
1366      1207240635      21      1366      1207240641      22      *
1366      1207240645      26      1366      1207240650      27      *
1366      1207240660      37      null      null      null      Unit went offline
null      null      null      1367      1207240600      1      Unit came online
1367      1207240660      58      null      null      null      Unit went offline
null      null      null      1368      1207240600      1      Unit came online
1368      1207240660      60      null      null      null      Unit went offline
null      null      null      1371      1207240603      1      Unit came online
1371      1207240637      35      1371      1207240641      36      *
1371      1207240660      53      null      null      null      Unit went offline
null      null      null      1375      1207240600      1      Unit came online
1375      1207240610      11      1375      1207240615      12      *
1375      1207240640      35      1375      1207240646      36      *
1375      1207240660      50      null      null      null      Unit went offline
null      null      null      1377      1207240600      1      Unit came online
1377      1207240610      11      1377      1207240614      12      *
1377      1207240660      55      null      null      null      Unit went offline
null      null      null      1378      1207240600      1      Unit came online
1378      1207240660      59      null      null      null      Unit went offline
null      null      null      1379      1207240600      1      Unit came online
1379      1207240610      11      1379      1207240614      12      *
1379      1207240660      58      null      null      null      Unit went offline
null      null      null      1380      1207240603      1      Unit came online
1380      1207240660      57      null      null      null      Unit went offline
null      null      null      1382      1207240600      1      Unit came online
1382      1207240608      9      1382      1207240614      10      *
1382      1207240660      54      null      null      null      Unit went offline
null      null      null      1383      1207240600      1      Unit came online
1383      1207240656      57      null      null      null      Unit went offline
null      null      null      1384      1207240600      1      Unit came online
1384      1207240660      59      null      null      null      Unit went offline
null      null      null      1385      1207240600      1      Unit came online
1385      1207240617      18      1385      1207240623      19      *
1385      1207240660      56      null      null      null      Unit went offline
null      null      null      1386      1207240600      1      Unit came online
1386      1207240605      6      1386      1207240611      7      *
1386      1207240660      55      null      null      null      Unit went offline
null      null      null      1387      1207240611      1      Unit came online
1387      1207240638      28      1387      1207240654      29      *
1387      1207240660      35      null      null      null      Unit went offline
null      null      null      1388      1207240601      1      Unit came online
1388      1207240638      35      1388      1207240643      36      *
1388      1207240660      53      null      null      null      Unit went offline
Ok.  :)

We can filter out the items where t1.RN = 1 and the unit comes ONLINE.  That's the start of the hour and there's probably no point in assuming that it was offline.

The other question is the '*' comment

1227      1207240635      33      1227      1207240641      34      *
1228      1207240650      51      1228      1207240654      52      *
1234      1207240623      22      1234      1207240627      23      *
1238      1207240631      32      1238      1207240637      33      *
1238      1207240646      42      1238      1207240650      43      *

In each of these cases, the interval is more than 3 minutes.  3 of the items shown have 4 minute intervals and are probably not an issue.  (This could be the scenario that I mentioned earlier.)  

The other 2 lines have 6 minutes intervals.  Does this represent a unit going down and coming right back up?


Kent
Avatar of Mi-Jack

ASKER

How do you mean? If the interval between data received from a unit is 6 minutes, how did it go down and came right back up?
I filtered out all but "live" records with "i_code=0", so if a unit is silent for more than 3 (or 5, or 10 - we can do that to make the result smaller) minutes - it went down, and if it started transmitting records again after that - it started back up.
I re-read your comments -  which one did you have in mind?
Avatar of Mi-Jack

ASKER

I ran the query again, this time with an interval > 20 minutes.
It came back with 221 records - can this be right?
Avatar of Mi-Jack

ASKER

Increasing the interval to 40 minutes only gets rid of 1 record - 220 total
1238      1207240631      32      1238      1207240637      33      *

In that line, unit 1238 reported at 10:31AM and 10:37AM.  Is it an issue that the unit went 6 minutes between reports?  Does it mean that the unit just missed an update?  Does it mean that the unit is suspect?  Etc.

With 3 minutes as our benchmark, I'm trying to get a handle on what we should do if the interval is more than 3 minutes.
Let's filter the top-of-the-hour 'ONLINE' entries.  

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL
  OR t1.unit is NULL
  OR (t1.time - t0.time) > 3)
 AND not (T1.rn =1 AND t0.unit is NULL)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

Finally got it - the only explanation is wireless availability.
The unit may wonder off to a zone with poor or no reception.
BTW, why the hour in the returned rows is 06?
Avatar of Mi-Jack

ASKER

168 records:

UNIT      TIME      RN      UNIT      TIME      RN      7
1209      1207240660      61      null      null      null      Unit went offline
1221      1207240660      58      null      null      null      Unit went offline
1227      1207240635      33      1227      1207240641      34      *
1227      1207240660      52      null      null      null      Unit went offline
1228      1207240650      51      1228      1207240654      52      *
1228      1207240659      57      null      null      null      Unit went offline
1230      1207240660      61      null      null      null      Unit went offline
1231      1207240660      61      null      null      null      Unit went offline
1233      1207240658      54      null      null      null      Unit went offline
1234      1207240623      22      1234      1207240627      23      *
1234      1207240660      56      null      null      null      Unit went offline
1235      1207240660      61      null      null      null      Unit went offline
1236      1207240660      57      null      null      null      Unit went offline
1237      1207240660      61      null      null      null      Unit went offline
1238      1207240631      32      1238      1207240637      33      *
1238      1207240646      42      1238      1207240650      43      *
1238      1207240660      52      null      null      null      Unit went offline
1242      1207240660      60      null      null      null      Unit went offline
1244      1207240660      60      null      null      null      Unit went offline
1245      1207240660      61      null      null      null      Unit went offline
1246      1207240660      61      null      null      null      Unit went offline
1247      1207240608      9      null      null      null      Unit went offline
1249      1207240646      47      null      null      null      Unit went offline
1251      1207240602      3      1251      1207240606      4      *
1251      1207240626      22      1251      1207240631      23      *
1251      1207240647      39      1251      1207240652      40      *
1251      1207240660      47      null      null      null      Unit went offline
1252      1207240660      55      null      null      null      Unit went offline
1254      1207240610      5      null      null      null      Unit went offline
1255      1207240660      61      null      null      null      Unit went offline
1257      1207240660      60      null      null      null      Unit went offline
1258      1207240624      25      1258      1207240628      26      *
1258      1207240660      58      null      null      null      Unit went offline
1260      1207240636      36      1260      1207240641      37      *
1260      1207240660      55      null      null      null      Unit went offline
1261      1207240660      59      null      null      null      Unit went offline
1262      1207240645      46      1262      1207240649      47      *
1262      1207240660      58      null      null      null      Unit went offline
1264      1207240660      61      null      null      null      Unit went offline
1265      1207240614      15      1265      1207240629      16      *
1265      1207240660      47      null      null      null      Unit went offline
1267      1207240660      56      null      null      null      Unit went offline
1268      1207240660      61      null      null      null      Unit went offline
1269      1207240660      61      null      null      null      Unit went offline
1270      1207240620      21      1270      1207240632      22      *
1270      1207240660      48      null      null      null      Unit went offline
1271      1207240661      61      null      null      null      Unit went offline
1272      1207240617      18      1272      1207240621      19      *
1272      1207240660      57      null      null      null      Unit went offline
1273      1207240660      58      null      null      null      Unit went offline
1274      1207240614      15      1274      1207240626      16      *
1274      1207240634      24      1274      1207240638      25      *
1274      1207240653      39      null      null      null      Unit went offline
1276      1207240660      58      null      null      null      Unit went offline
1277      1207240654      39      null      null      null      Unit went offline
1282      1207240644      44      1282      1207240649      45      *
1282      1207240650      46      1282      1207240655      47      *
1282      1207240660      51      null      null      null      Unit went offline
1283      1207240660      59      null      null      null      Unit went offline
1284      1207240658      59      null      null      null      Unit went offline
1285      1207240660      60      null      null      null      Unit went offline
1286      1207240654      51      1286      1207240659      52      *
1286      1207240660      53      null      null      null      Unit went offline
1287      1207240636      35      1287      1207240640      36      *
1287      1207240660      56      null      null      null      Unit went offline
1289      1207240660      61      null      null      null      Unit went offline
1291      1207240636      35      1291      1207240648      36      *
1291      1207240660      46      null      null      null      Unit went offline
1292      1207240660      55      null      null      null      Unit went offline
1293      1207240627      26      1293      1207240635      27      *
1293      1207240660      51      null      null      null      Unit went offline
1294      1207240660      59      null      null      null      Unit went offline
1296      1207240660      61      null      null      null      Unit went offline
1297      1207240626      27      1297      1207240631      28      *
1297      1207240657      52      null      null      null      Unit went offline
1298      1207240660      59      null      null      null      Unit went offline
1301      1207240611      12      1301      1207240616      13      *
1301      1207240659      55      null      null      null      Unit went offline
1303      1207240660      61      null      null      null      Unit went offline
1305      1207240603      4      1305      1207240608      5      *
1305      1207240660      54      null      null      null      Unit went offline
1306      1207240660      49      null      null      null      Unit went offline
1307      1207240660      60      null      null      null      Unit went offline
1308      1207240654      53      1308      1207240660      54      *
1308      1207240660      54      null      null      null      Unit went offline
1309      1207240621      21      1309      1207240642      22      *
1309      1207240656      36      null      null      null      Unit went offline
1310      1207240660      61      null      null      null      Unit went offline
1311      1207240603      4      1311      1207240609      5      *
1311      1207240642      38      1311      1207240646      39      *
1311      1207240649      42      1311      1207240655      43      *
1311      1207240656      44      null      null      null      Unit went offline
1313      1207240606      4      1313      1207240612      5      *
1313      1207240620      13      1313      1207240624      14      *
1313      1207240660      50      null      null      null      Unit went offline
1315      1207240607      8      1315      1207240612      9      *
1315      1207240660      56      null      null      null      Unit went offline
1318      1207240648      48      1318      1207240653      49      *
1318      1207240660      56      null      null      null      Unit went offline
1319      1207240616      17      1319      1207240620      18      *
1319      1207240638      36      1319      1207240643      37      *
1319      1207240660      54      null      null      null      Unit went offline
1320      1207240610      11      1320      1207240617      12      *
1320      1207240618      13      1320      1207240622      14      *
1320      1207240660      52      null      null      null      Unit went offline
1321      1207240657      52      null      null      null      Unit went offline
1322      1207240660      60      null      null      null      Unit went offline
1323      1207240659      54      null      null      null      Unit went offline
1324      1207240612      13      1324      1207240617      14      *
1324      1207240660      57      null      null      null      Unit went offline
1325      1207240660      59      null      null      null      Unit went offline
1326      1207240628      29      1326      1207240642      30      *
1326      1207240660      48      null      null      null      Unit went offline
1328      1207240608      1      1328      1207240624      2      *
1328      1207240660      36      null      null      null      Unit went offline
1329      1207240660      58      null      null      null      Unit went offline
1331      1207240660      61      null      null      null      Unit went offline
1332      1207240639      39      1332      1207240647      40      *
1332      1207240660      53      null      null      null      Unit went offline
1333      1207240660      61      null      null      null      Unit went offline
1334      1207240656      55      1334      1207240660      56      *
1334      1207240660      56      null      null      null      Unit went offline
1335      1207240631      32      1335      1207240636      33      *
1335      1207240660      55      null      null      null      Unit went offline
1337      1207240660      61      null      null      null      Unit went offline
1339      1207240619      2      null      null      null      Unit went offline
1340      1207240660      61      null      null      null      Unit went offline
1341      1207240660      61      null      null      null      Unit went offline
1343      1207240660      60      null      null      null      Unit went offline
1344      1207240660      61      null      null      null      Unit went offline
1350      1207240660      61      null      null      null      Unit went offline
1351      1207240660      60      null      null      null      Unit went offline
1353      1207240660      61      null      null      null      Unit went offline
1354      1207240660      58      null      null      null      Unit went offline
1355      1207240660      60      null      null      null      Unit went offline
1357      1207240660      59      null      null      null      Unit went offline
1359      1207240660      59      null      null      null      Unit went offline
1364      1207240720      58      null      null      null      Unit went offline
1366      1207240602      3      1366      1207240612      4      *
1366      1207240622      14      1366      1207240629      15      *
1366      1207240635      21      1366      1207240641      22      *
1366      1207240645      26      1366      1207240650      27      *
1366      1207240660      37      null      null      null      Unit went offline
1367      1207240660      58      null      null      null      Unit went offline
1368      1207240660      60      null      null      null      Unit went offline
1371      1207240637      35      1371      1207240641      36      *
1371      1207240660      53      null      null      null      Unit went offline
1375      1207240610      11      1375      1207240615      12      *
1375      1207240640      35      1375      1207240646      36      *
1375      1207240660      50      null      null      null      Unit went offline
1377      1207240610      11      1377      1207240614      12      *
1377      1207240660      55      null      null      null      Unit went offline
1378      1207240660      59      null      null      null      Unit went offline
1379      1207240610      11      1379      1207240614      12      *
1379      1207240660      58      null      null      null      Unit went offline
1380      1207240660      57      null      null      null      Unit went offline
1382      1207240608      9      1382      1207240614      10      *
1382      1207240660      54      null      null      null      Unit went offline
1383      1207240656      57      null      null      null      Unit went offline
1384      1207240660      59      null      null      null      Unit went offline
1385      1207240617      18      1385      1207240623      19      *
1385      1207240660      56      null      null      null      Unit went offline
1386      1207240605      6      1386      1207240611      7      *
1386      1207240660      55      null      null      null      Unit went offline
1387      1207240638      28      1387      1207240654      29      *
1387      1207240660      35      null      null      null      Unit went offline
1388      1207240638      35      1388      1207240643      36      *
1388      1207240660      53      null      null      null      Unit went offline
Getting there.  That list of "offline" items needs a similar paring as did the "onlines".  I.e. the first ONLINE and the last OFFLINE need to be checked for time.

Let's try this.  It selects the row if
-  The first row for the unit is more than 3 minutes past the hour
-  The last row for the unit is more than 3 minutes before the hour
-  More than 3 minutes are elapsed between rows



WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and mod(t1.time, 60) < 57)
   OR (T1.unit is NULL and mod(t0.time, 50 > 3))
   OR (t0.unit is not NULL and t1.unit is not NULL and t1.time - t0.time > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

I had to slightly modify the sql, please check:

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 60 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and mod(t1.time, 60) < 57)
   OR (t1.unit is NULL and mod(t0.time, 50) > 3)
   OR (t0.unit is not NULL and t1.unit is not NULL and t1.time - t0.time > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

The results:

UNIT      TIME      RN      UNIT      TIME      RN      7
null      null      null      1209      1207240600      1      Unit came online
1209      1207240660      61      null      null      null      Unit went offline
null      null      null      1221      1207240600      1      Unit came online
1221      1207240660      58      null      null      null      Unit went offline
null      null      null      1227      1207240600      1      Unit came online
1227      1207240635      33      1227      1207240641      34      *
1227      1207240660      52      null      null      null      Unit went offline
null      null      null      1228      1207240600      1      Unit came online
1228      1207240650      51      1228      1207240654      52      *
1228      1207240659      57      null      null      null      Unit went offline
null      null      null      1230      1207240600      1      Unit came online
1230      1207240660      61      null      null      null      Unit went offline
null      null      null      1231      1207240600      1      Unit came online
1231      1207240660      61      null      null      null      Unit went offline
null      null      null      1233      1207240600      1      Unit came online
1233      1207240658      54      null      null      null      Unit went offline
null      null      null      1234      1207240600      1      Unit came online
1234      1207240623      22      1234      1207240627      23      *
1234      1207240660      56      null      null      null      Unit went offline
null      null      null      1235      1207240600      1      Unit came online
1235      1207240660      61      null      null      null      Unit went offline
null      null      null      1236      1207240602      1      Unit came online
1236      1207240660      57      null      null      null      Unit went offline
null      null      null      1237      1207240600      1      Unit came online
1237      1207240660      61      null      null      null      Unit went offline
null      null      null      1238      1207240600      1      Unit came online
1238      1207240631      32      1238      1207240637      33      *
1238      1207240646      42      1238      1207240650      43      *
1238      1207240660      52      null      null      null      Unit went offline
null      null      null      1242      1207240600      1      Unit came online
1242      1207240660      60      null      null      null      Unit went offline
null      null      null      1244      1207240600      1      Unit came online
1244      1207240660      60      null      null      null      Unit went offline
null      null      null      1245      1207240600      1      Unit came online
1245      1207240660      61      null      null      null      Unit went offline
null      null      null      1246      1207240600      1      Unit came online
1246      1207240660      61      null      null      null      Unit went offline
null      null      null      1247      1207240600      1      Unit came online
1247      1207240608      9      null      null      null      Unit went offline
null      null      null      1249      1207240600      1      Unit came online
1249      1207240646      47      null      null      null      Unit went offline
null      null      null      1251      1207240600      1      Unit came online
1251      1207240602      3      1251      1207240606      4      *
1251      1207240626      22      1251      1207240631      23      *
1251      1207240647      39      1251      1207240652      40      *
1251      1207240660      47      null      null      null      Unit went offline
null      null      null      1252      1207240600      1      Unit came online
1252      1207240660      55      null      null      null      Unit went offline
null      null      null      1254      1207240606      1      Unit came online
1254      1207240610      5      null      null      null      Unit went offline
null      null      null      1255      1207240600      1      Unit came online
1255      1207240660      61      null      null      null      Unit went offline
null      null      null      1257      1207240600      1      Unit came online
1257      1207240660      60      null      null      null      Unit went offline
null      null      null      1258      1207240600      1      Unit came online
1258      1207240624      25      1258      1207240628      26      *
1258      1207240660      58      null      null      null      Unit went offline
null      null      null      1260      1207240600      1      Unit came online
1260      1207240636      36      1260      1207240641      37      *
1260      1207240660      55      null      null      null      Unit went offline
null      null      null      1261      1207240601      1      Unit came online
1261      1207240660      59      null      null      null      Unit went offline
null      null      null      1262      1207240600      1      Unit came online
1262      1207240645      46      1262      1207240649      47      *
1262      1207240660      58      null      null      null      Unit went offline
null      null      null      1264      1207240600      1      Unit came online
1264      1207240660      61      null      null      null      Unit went offline
null      null      null      1265      1207240600      1      Unit came online
1265      1207240614      15      1265      1207240629      16      *
1265      1207240660      47      null      null      null      Unit went offline
null      null      null      1267      1207240600      1      Unit came online
1267      1207240660      56      null      null      null      Unit went offline
null      null      null      1268      1207240600      1      Unit came online
1268      1207240660      61      null      null      null      Unit went offline
null      null      null      1269      1207240600      1      Unit came online
1269      1207240660      61      null      null      null      Unit went offline
null      null      null      1270      1207240600      1      Unit came online
1270      1207240620      21      1270      1207240632      22      *
1270      1207240660      48      null      null      null      Unit went offline
null      null      null      1271      1207240601      1      Unit came online
1271      1207240661      61      null      null      null      Unit went offline
null      null      null      1272      1207240600      1      Unit came online
1272      1207240617      18      1272      1207240621      19      *
1272      1207240660      57      null      null      null      Unit went offline
null      null      null      1273      1207240601      1      Unit came online
1273      1207240660      58      null      null      null      Unit went offline
null      null      null      1274      1207240600      1      Unit came online
1274      1207240614      15      1274      1207240626      16      *
1274      1207240634      24      1274      1207240638      25      *
null      null      null      1276      1207240600      1      Unit came online
1276      1207240660      58      null      null      null      Unit went offline
null      null      null      1277      1207240613      1      Unit came online
1277      1207240654      39      null      null      null      Unit went offline
null      null      null      1282      1207240600      1      Unit came online
1282      1207240644      44      1282      1207240649      45      *
1282      1207240650      46      1282      1207240655      47      *
1282      1207240660      51      null      null      null      Unit went offline
null      null      null      1283      1207240600      1      Unit came online
1283      1207240660      59      null      null      null      Unit went offline
null      null      null      1284      1207240599      1      Unit came online
1284      1207240658      59      null      null      null      Unit went offline
null      null      null      1285      1207240600      1      Unit came online
1285      1207240660      60      null      null      null      Unit went offline
null      null      null      1286      1207240600      1      Unit came online
1286      1207240654      51      1286      1207240659      52      *
1286      1207240660      53      null      null      null      Unit went offline
null      null      null      1287      1207240600      1      Unit came online
1287      1207240636      35      1287      1207240640      36      *
1287      1207240660      56      null      null      null      Unit went offline
null      null      null      1289      1207240600      1      Unit came online
1289      1207240660      61      null      null      null      Unit went offline
null      null      null      1291      1207240600      1      Unit came online
1291      1207240636      35      1291      1207240648      36      *
1291      1207240660      46      null      null      null      Unit went offline
null      null      null      1292      1207240600      1      Unit came online
1292      1207240660      55      null      null      null      Unit went offline
null      null      null      1293      1207240600      1      Unit came online
1293      1207240627      26      1293      1207240635      27      *
1293      1207240660      51      null      null      null      Unit went offline
null      null      null      1294      1207240601      1      Unit came online
1294      1207240660      59      null      null      null      Unit went offline
null      null      null      1296      1207240600      1      Unit came online
1296      1207240660      61      null      null      null      Unit went offline
null      null      null      1297      1207240600      1      Unit came online
1297      1207240626      27      1297      1207240631      28      *
1297      1207240657      52      null      null      null      Unit went offline
null      null      null      1298      1207240601      1      Unit came online
1298      1207240660      59      null      null      null      Unit went offline
null      null      null      1301      1207240600      1      Unit came online
1301      1207240611      12      1301      1207240616      13      *
1301      1207240659      55      null      null      null      Unit went offline
null      null      null      1303      1207240600      1      Unit came online
1303      1207240660      61      null      null      null      Unit went offline
null      null      null      1305      1207240600      1      Unit came online
1305      1207240603      4      1305      1207240608      5      *
1305      1207240660      54      null      null      null      Unit went offline
null      null      null      1306      1207240612      1      Unit came online
1306      1207240660      49      null      null      null      Unit went offline
null      null      null      1307      1207240600      1      Unit came online
1307      1207240660      60      null      null      null      Unit went offline
null      null      null      1308      1207240600      1      Unit came online
1308      1207240654      53      1308      1207240660      54      *
1308      1207240660      54      null      null      null      Unit went offline
null      null      null      1309      1207240601      1      Unit came online
1309      1207240621      21      1309      1207240642      22      *
1309      1207240656      36      null      null      null      Unit went offline
null      null      null      1310      1207240600      1      Unit came online
1310      1207240660      61      null      null      null      Unit went offline
null      null      null      1311      1207240600      1      Unit came online
1311      1207240603      4      1311      1207240609      5      *
1311      1207240642      38      1311      1207240646      39      *
1311      1207240649      42      1311      1207240655      43      *
1311      1207240656      44      null      null      null      Unit went offline
null      null      null      1313      1207240603      1      Unit came online
1313      1207240606      4      1313      1207240612      5      *
1313      1207240620      13      1313      1207240624      14      *
1313      1207240660      50      null      null      null      Unit went offline
null      null      null      1315      1207240600      1      Unit came online
1315      1207240607      8      1315      1207240612      9      *
1315      1207240660      56      null      null      null      Unit went offline
null      null      null      1318      1207240600      1      Unit came online
1318      1207240648      48      1318      1207240653      49      *
1318      1207240660      56      null      null      null      Unit went offline
null      null      null      1319      1207240600      1      Unit came online
1319      1207240616      17      1319      1207240620      18      *
1319      1207240638      36      1319      1207240643      37      *
1319      1207240660      54      null      null      null      Unit went offline
null      null      null      1320      1207240600      1      Unit came online
1320      1207240610      11      1320      1207240617      12      *
1320      1207240618      13      1320      1207240622      14      *
1320      1207240660      52      null      null      null      Unit went offline
null      null      null      1321      1207240605      1      Unit came online
1321      1207240657      52      null      null      null      Unit went offline
null      null      null      1322      1207240600      1      Unit came online
1322      1207240660      60      null      null      null      Unit went offline
null      null      null      1323      1207240600      1      Unit came online
1323      1207240659      54      null      null      null      Unit went offline
null      null      null      1324      1207240600      1      Unit came online
1324      1207240612      13      1324      1207240617      14      *
1324      1207240660      57      null      null      null      Unit went offline
null      null      null      1325      1207240600      1      Unit came online
1325      1207240660      59      null      null      null      Unit went offline
null      null      null      1326      1207240600      1      Unit came online
1326      1207240628      29      1326      1207240642      30      *
1326      1207240660      48      null      null      null      Unit went offline
1328      1207240608      1      1328      1207240624      2      *
null      null      null      1328      1207240608      1      Unit came online
1328      1207240660      36      null      null      null      Unit went offline
null      null      null      1329      1207240600      1      Unit came online
1329      1207240660      58      null      null      null      Unit went offline
null      null      null      1331      1207240600      1      Unit came online
1331      1207240660      61      null      null      null      Unit went offline
null      null      null      1332      1207240600      1      Unit came online
1332      1207240639      39      1332      1207240647      40      *
1332      1207240660      53      null      null      null      Unit went offline
null      null      null      1333      1207240600      1      Unit came online
1333      1207240660      61      null      null      null      Unit went offline
null      null      null      1334      1207240600      1      Unit came online
1334      1207240656      55      1334      1207240660      56      *
1334      1207240660      56      null      null      null      Unit went offline
null      null      null      1335      1207240600      1      Unit came online
1335      1207240631      32      1335      1207240636      33      *
1335      1207240660      55      null      null      null      Unit went offline
null      null      null      1337      1207240600      1      Unit came online
1337      1207240660      61      null      null      null      Unit went offline
1339      1207240619      2      null      null      null      Unit went offline
null      null      null      1340      1207240600      1      Unit came online
1340      1207240660      61      null      null      null      Unit went offline
null      null      null      1341      1207240600      1      Unit came online
1341      1207240660      61      null      null      null      Unit went offline
null      null      null      1343      1207240600      1      Unit came online
1343      1207240660      60      null      null      null      Unit went offline
null      null      null      1344      1207240600      1      Unit came online
1344      1207240660      61      null      null      null      Unit went offline
null      null      null      1350      1207240600      1      Unit came online
1350      1207240660      61      null      null      null      Unit went offline
null      null      null      1351      1207240600      1      Unit came online
1351      1207240660      60      null      null      null      Unit went offline
null      null      null      1353      1207240600      1      Unit came online
1353      1207240660      61      null      null      null      Unit went offline
null      null      null      1354      1207240600      1      Unit came online
1354      1207240660      58      null      null      null      Unit went offline
null      null      null      1355      1207240600      1      Unit came online
1355      1207240660      60      null      null      null      Unit went offline
null      null      null      1357      1207240600      1      Unit came online
1357      1207240660      59      null      null      null      Unit went offline
null      null      null      1359      1207240600      1      Unit came online
1359      1207240660      59      null      null      null      Unit went offline
null      null      null      1364      1207240663      1      Unit came online
1364      1207240720      58      null      null      null      Unit went offline
null      null      null      1366      1207240600      1      Unit came online
1366      1207240602      3      1366      1207240612      4      *
1366      1207240622      14      1366      1207240629      15      *
1366      1207240635      21      1366      1207240641      22      *
1366      1207240645      26      1366      1207240650      27      *
1366      1207240660      37      null      null      null      Unit went offline
null      null      null      1367      1207240600      1      Unit came online
1367      1207240660      58      null      null      null      Unit went offline
null      null      null      1368      1207240600      1      Unit came online
1368      1207240660      60      null      null      null      Unit went offline
null      null      null      1371      1207240603      1      Unit came online
1371      1207240637      35      1371      1207240641      36      *
1371      1207240660      53      null      null      null      Unit went offline
null      null      null      1375      1207240600      1      Unit came online
1375      1207240610      11      1375      1207240615      12      *
1375      1207240640      35      1375      1207240646      36      *
1375      1207240660      50      null      null      null      Unit went offline
null      null      null      1377      1207240600      1      Unit came online
1377      1207240610      11      1377      1207240614      12      *
1377      1207240660      55      null      null      null      Unit went offline
null      null      null      1378      1207240600      1      Unit came online
1378      1207240660      59      null      null      null      Unit went offline
null      null      null      1379      1207240600      1      Unit came online
1379      1207240610      11      1379      1207240614      12      *
1379      1207240660      58      null      null      null      Unit went offline
null      null      null      1380      1207240603      1      Unit came online
1380      1207240660      57      null      null      null      Unit went offline
null      null      null      1382      1207240600      1      Unit came online
1382      1207240608      9      1382      1207240614      10      *
1382      1207240660      54      null      null      null      Unit went offline
null      null      null      1383      1207240600      1      Unit came online
1383      1207240656      57      null      null      null      Unit went offline
null      null      null      1384      1207240600      1      Unit came online
1384      1207240660      59      null      null      null      Unit went offline
null      null      null      1385      1207240600      1      Unit came online
1385      1207240617      18      1385      1207240623      19      *
1385      1207240660      56      null      null      null      Unit went offline
null      null      null      1386      1207240600      1      Unit came online
1386      1207240605      6      1386      1207240611      7      *
1386      1207240660      55      null      null      null      Unit went offline
null      null      null      1387      1207240611      1      Unit came online
1387      1207240638      28      1387      1207240654      29      *
1387      1207240660      35      null      null      null      Unit went offline
null      null      null      1388      1207240601      1      Unit came online
1388      1207240638      35      1388      1207240643      36      *
1388      1207240660      53      null      null      null      Unit went offline
3 lines up from the bottom.  Change the 50 to 60.  

Sorry.  Getting to be a long day and I'm letting little things slip past....
Avatar of Mi-Jack

ASKER

UNIT      TIME      RN      UNIT      TIME      RN      7
null      null      null      1209      1207240600      1      Unit came online
1209      1207240660      61      null      null      null      Unit went offline
null      null      null      1221      1207240600      1      Unit came online
1221      1207240660      58      null      null      null      Unit went offline
null      null      null      1227      1207240600      1      Unit came online
1227      1207240635      33      1227      1207240641      34      *
1227      1207240660      52      null      null      null      Unit went offline
null      null      null      1228      1207240600      1      Unit came online
1228      1207240650      51      1228      1207240654      52      *
1228      1207240659      57      null      null      null      Unit went offline
null      null      null      1230      1207240600      1      Unit came online
1230      1207240660      61      null      null      null      Unit went offline
null      null      null      1231      1207240600      1      Unit came online
1231      1207240660      61      null      null      null      Unit went offline
null      null      null      1233      1207240600      1      Unit came online
1233      1207240658      54      null      null      null      Unit went offline
null      null      null      1234      1207240600      1      Unit came online
1234      1207240623      22      1234      1207240627      23      *
1234      1207240660      56      null      null      null      Unit went offline
null      null      null      1235      1207240600      1      Unit came online
1235      1207240660      61      null      null      null      Unit went offline
null      null      null      1236      1207240602      1      Unit came online
1236      1207240660      57      null      null      null      Unit went offline
null      null      null      1237      1207240600      1      Unit came online
1237      1207240660      61      null      null      null      Unit went offline
null      null      null      1238      1207240600      1      Unit came online
1238      1207240631      32      1238      1207240637      33      *
1238      1207240646      42      1238      1207240650      43      *
1238      1207240660      52      null      null      null      Unit went offline
null      null      null      1242      1207240600      1      Unit came online
1242      1207240660      60      null      null      null      Unit went offline
null      null      null      1244      1207240600      1      Unit came online
1244      1207240660      60      null      null      null      Unit went offline
null      null      null      1245      1207240600      1      Unit came online
1245      1207240660      61      null      null      null      Unit went offline
null      null      null      1246      1207240600      1      Unit came online
1246      1207240660      61      null      null      null      Unit went offline
null      null      null      1247      1207240600      1      Unit came online
1247      1207240608      9      null      null      null      Unit went offline
null      null      null      1249      1207240600      1      Unit came online
1249      1207240646      47      null      null      null      Unit went offline
null      null      null      1251      1207240600      1      Unit came online
1251      1207240602      3      1251      1207240606      4      *
1251      1207240626      22      1251      1207240631      23      *
1251      1207240647      39      1251      1207240652      40      *
1251      1207240660      47      null      null      null      Unit went offline
null      null      null      1252      1207240600      1      Unit came online
1252      1207240660      55      null      null      null      Unit went offline
null      null      null      1254      1207240606      1      Unit came online
1254      1207240610      5      null      null      null      Unit went offline
null      null      null      1255      1207240600      1      Unit came online
1255      1207240660      61      null      null      null      Unit went offline
null      null      null      1257      1207240600      1      Unit came online
1257      1207240660      60      null      null      null      Unit went offline
null      null      null      1258      1207240600      1      Unit came online
1258      1207240624      25      1258      1207240628      26      *
1258      1207240660      58      null      null      null      Unit went offline
null      null      null      1260      1207240600      1      Unit came online
1260      1207240636      36      1260      1207240641      37      *
1260      1207240660      55      null      null      null      Unit went offline
null      null      null      1261      1207240601      1      Unit came online
1261      1207240660      59      null      null      null      Unit went offline
null      null      null      1262      1207240600      1      Unit came online
1262      1207240645      46      1262      1207240649      47      *
1262      1207240660      58      null      null      null      Unit went offline
null      null      null      1264      1207240600      1      Unit came online
1264      1207240660      61      null      null      null      Unit went offline
null      null      null      1265      1207240600      1      Unit came online
1265      1207240614      15      1265      1207240629      16      *
1265      1207240660      47      null      null      null      Unit went offline
null      null      null      1267      1207240600      1      Unit came online
1267      1207240660      56      null      null      null      Unit went offline
null      null      null      1268      1207240600      1      Unit came online
1268      1207240660      61      null      null      null      Unit went offline
null      null      null      1269      1207240600      1      Unit came online
1269      1207240660      61      null      null      null      Unit went offline
null      null      null      1270      1207240600      1      Unit came online
1270      1207240620      21      1270      1207240632      22      *
1270      1207240660      48      null      null      null      Unit went offline
null      null      null      1271      1207240601      1      Unit came online
1271      1207240661      61      null      null      null      Unit went offline
null      null      null      1272      1207240600      1      Unit came online
1272      1207240617      18      1272      1207240621      19      *
1272      1207240660      57      null      null      null      Unit went offline
null      null      null      1273      1207240601      1      Unit came online
1273      1207240660      58      null      null      null      Unit went offline
null      null      null      1274      1207240600      1      Unit came online
1274      1207240614      15      1274      1207240626      16      *
1274      1207240634      24      1274      1207240638      25      *
1274      1207240653      39      null      null      null      Unit went offline
null      null      null      1276      1207240600      1      Unit came online
1276      1207240660      58      null      null      null      Unit went offline
null      null      null      1277      1207240613      1      Unit came online
1277      1207240654      39      null      null      null      Unit went offline
null      null      null      1282      1207240600      1      Unit came online
1282      1207240644      44      1282      1207240649      45      *
1282      1207240650      46      1282      1207240655      47      *
1282      1207240660      51      null      null      null      Unit went offline
null      null      null      1283      1207240600      1      Unit came online
1283      1207240660      59      null      null      null      Unit went offline
null      null      null      1284      1207240599      1      Unit came online
1284      1207240658      59      null      null      null      Unit went offline
null      null      null      1285      1207240600      1      Unit came online
1285      1207240660      60      null      null      null      Unit went offline
null      null      null      1286      1207240600      1      Unit came online
1286      1207240654      51      1286      1207240659      52      *
1286      1207240660      53      null      null      null      Unit went offline
null      null      null      1287      1207240600      1      Unit came online
1287      1207240636      35      1287      1207240640      36      *
1287      1207240660      56      null      null      null      Unit went offline
null      null      null      1289      1207240600      1      Unit came online
1289      1207240660      61      null      null      null      Unit went offline
null      null      null      1291      1207240600      1      Unit came online
1291      1207240636      35      1291      1207240648      36      *
1291      1207240660      46      null      null      null      Unit went offline
null      null      null      1292      1207240600      1      Unit came online
1292      1207240660      55      null      null      null      Unit went offline
null      null      null      1293      1207240600      1      Unit came online
1293      1207240627      26      1293      1207240635      27      *
1293      1207240660      51      null      null      null      Unit went offline
null      null      null      1294      1207240601      1      Unit came online
1294      1207240660      59      null      null      null      Unit went offline
null      null      null      1296      1207240600      1      Unit came online
1296      1207240660      61      null      null      null      Unit went offline
null      null      null      1297      1207240600      1      Unit came online
1297      1207240626      27      1297      1207240631      28      *
1297      1207240657      52      null      null      null      Unit went offline
null      null      null      1298      1207240601      1      Unit came online
1298      1207240660      59      null      null      null      Unit went offline
null      null      null      1301      1207240600      1      Unit came online
1301      1207240611      12      1301      1207240616      13      *
1301      1207240659      55      null      null      null      Unit went offline
null      null      null      1303      1207240600      1      Unit came online
1303      1207240660      61      null      null      null      Unit went offline
null      null      null      1305      1207240600      1      Unit came online
1305      1207240603      4      1305      1207240608      5      *
1305      1207240660      54      null      null      null      Unit went offline
null      null      null      1306      1207240612      1      Unit came online
1306      1207240660      49      null      null      null      Unit went offline
null      null      null      1307      1207240600      1      Unit came online
1307      1207240660      60      null      null      null      Unit went offline
null      null      null      1308      1207240600      1      Unit came online
1308      1207240654      53      1308      1207240660      54      *
1308      1207240660      54      null      null      null      Unit went offline
null      null      null      1309      1207240601      1      Unit came online
1309      1207240621      21      1309      1207240642      22      *
1309      1207240656      36      null      null      null      Unit went offline
null      null      null      1310      1207240600      1      Unit came online
1310      1207240660      61      null      null      null      Unit went offline
null      null      null      1311      1207240600      1      Unit came online
1311      1207240603      4      1311      1207240609      5      *
1311      1207240642      38      1311      1207240646      39      *
1311      1207240649      42      1311      1207240655      43      *
1311      1207240656      44      null      null      null      Unit went offline
null      null      null      1313      1207240603      1      Unit came online
1313      1207240606      4      1313      1207240612      5      *
1313      1207240620      13      1313      1207240624      14      *
1313      1207240660      50      null      null      null      Unit went offline
null      null      null      1315      1207240600      1      Unit came online
1315      1207240607      8      1315      1207240612      9      *
1315      1207240660      56      null      null      null      Unit went offline
null      null      null      1318      1207240600      1      Unit came online
1318      1207240648      48      1318      1207240653      49      *
1318      1207240660      56      null      null      null      Unit went offline
null      null      null      1319      1207240600      1      Unit came online
1319      1207240616      17      1319      1207240620      18      *
1319      1207240638      36      1319      1207240643      37      *
1319      1207240660      54      null      null      null      Unit went offline
null      null      null      1320      1207240600      1      Unit came online
1320      1207240610      11      1320      1207240617      12      *
1320      1207240618      13      1320      1207240622      14      *
1320      1207240660      52      null      null      null      Unit went offline
null      null      null      1321      1207240605      1      Unit came online
1321      1207240657      52      null      null      null      Unit went offline
null      null      null      1322      1207240600      1      Unit came online
1322      1207240660      60      null      null      null      Unit went offline
null      null      null      1323      1207240600      1      Unit came online
1323      1207240659      54      null      null      null      Unit went offline
null      null      null      1324      1207240600      1      Unit came online
1324      1207240612      13      1324      1207240617      14      *
1324      1207240660      57      null      null      null      Unit went offline
null      null      null      1325      1207240600      1      Unit came online
1325      1207240660      59      null      null      null      Unit went offline
null      null      null      1326      1207240600      1      Unit came online
1326      1207240628      29      1326      1207240642      30      *
1326      1207240660      48      null      null      null      Unit went offline
1328      1207240608      1      1328      1207240624      2      *
null      null      null      1328      1207240608      1      Unit came online
1328      1207240660      36      null      null      null      Unit went offline
null      null      null      1329      1207240600      1      Unit came online
1329      1207240660      58      null      null      null      Unit went offline
null      null      null      1331      1207240600      1      Unit came online
1331      1207240660      61      null      null      null      Unit went offline
null      null      null      1332      1207240600      1      Unit came online
1332      1207240639      39      1332      1207240647      40      *
1332      1207240660      53      null      null      null      Unit went offline
null      null      null      1333      1207240600      1      Unit came online
1333      1207240660      61      null      null      null      Unit went offline
null      null      null      1334      1207240600      1      Unit came online
1334      1207240656      55      1334      1207240660      56      *
1334      1207240660      56      null      null      null      Unit went offline
null      null      null      1335      1207240600      1      Unit came online
1335      1207240631      32      1335      1207240636      33      *
1335      1207240660      55      null      null      null      Unit went offline
null      null      null      1337      1207240600      1      Unit came online
1337      1207240660      61      null      null      null      Unit went offline
1339      1207240619      2      null      null      null      Unit went offline
null      null      null      1340      1207240600      1      Unit came online
1340      1207240660      61      null      null      null      Unit went offline
null      null      null      1341      1207240600      1      Unit came online
1341      1207240660      61      null      null      null      Unit went offline
null      null      null      1343      1207240600      1      Unit came online
1343      1207240660      60      null      null      null      Unit went offline
null      null      null      1344      1207240600      1      Unit came online
1344      1207240660      61      null      null      null      Unit went offline
null      null      null      1350      1207240600      1      Unit came online
1350      1207240660      61      null      null      null      Unit went offline
null      null      null      1351      1207240600      1      Unit came online
1351      1207240660      60      null      null      null      Unit went offline
null      null      null      1353      1207240600      1      Unit came online
1353      1207240660      61      null      null      null      Unit went offline
null      null      null      1354      1207240600      1      Unit came online
1354      1207240660      58      null      null      null      Unit went offline
null      null      null      1355      1207240600      1      Unit came online
1355      1207240660      60      null      null      null      Unit went offline
null      null      null      1357      1207240600      1      Unit came online
1357      1207240660      59      null      null      null      Unit went offline
null      null      null      1359      1207240600      1      Unit came online
1359      1207240660      59      null      null      null      Unit went offline
null      null      null      1364      1207240663      1      Unit came online
1364      1207240720      58      null      null      null      Unit went offline
null      null      null      1366      1207240600      1      Unit came online
1366      1207240602      3      1366      1207240612      4      *
1366      1207240622      14      1366      1207240629      15      *
1366      1207240635      21      1366      1207240641      22      *
1366      1207240645      26      1366      1207240650      27      *
1366      1207240660      37      null      null      null      Unit went offline
null      null      null      1367      1207240600      1      Unit came online
1367      1207240660      58      null      null      null      Unit went offline
null      null      null      1368      1207240600      1      Unit came online
1368      1207240660      60      null      null      null      Unit went offline
null      null      null      1371      1207240603      1      Unit came online
1371      1207240637      35      1371      1207240641      36      *
1371      1207240660      53      null      null      null      Unit went offline
null      null      null      1375      1207240600      1      Unit came online
1375      1207240610      11      1375      1207240615      12      *
1375      1207240640      35      1375      1207240646      36      *
1375      1207240660      50      null      null      null      Unit went offline
null      null      null      1377      1207240600      1      Unit came online
1377      1207240610      11      1377      1207240614      12      *
1377      1207240660      55      null      null      null      Unit went offline
null      null      null      1378      1207240600      1      Unit came online
1378      1207240660      59      null      null      null      Unit went offline
null      null      null      1379      1207240600      1      Unit came online
1379      1207240610      11      1379      1207240614      12      *
1379      1207240660      58      null      null      null      Unit went offline
null      null      null      1380      1207240603      1      Unit came online
1380      1207240660      57      null      null      null      Unit went offline
null      null      null      1382      1207240600      1      Unit came online
1382      1207240608      9      1382      1207240614      10      *
1382      1207240660      54      null      null      null      Unit went offline
null      null      null      1383      1207240600      1      Unit came online
1383      1207240656      57      null      null      null      Unit went offline
null      null      null      1384      1207240600      1      Unit came online
1384      1207240660      59      null      null      null      Unit went offline
null      null      null      1385      1207240600      1      Unit came online
1385      1207240617      18      1385      1207240623      19      *
1385      1207240660      56      null      null      null      Unit went offline
null      null      null      1386      1207240600      1      Unit came online
1386      1207240605      6      1386      1207240611      7      *
1386      1207240660      55      null      null      null      Unit went offline
null      null      null      1387      1207240611      1      Unit came online
1387      1207240638      28      1387      1207240654      29      *
1387      1207240660      35      null      null      null      Unit went offline
null      null      null      1388      1207240601      1      Unit came online
1388      1207240638      35      1388      1207240643      36      *
1388      1207240660      53      null      null      null      Unit went offline
The hour is actually 10AM.  The CTE multiplied the hour by 60, not 100, just in case the table spanned the top of the hour.

Let's multiple by 100 to make this easier to read.  Oddly, it looks right so far...

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and mod(t1.time, 100) < 57)
   OR (t1.unit is NULL and mod(t0.time, 100) > 3)
   OR (t0.unit is not NULL and t1.unit is not NULL and t1.time - t0.time > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

I changed the interval to 10 minutes:

Let's multiple by 100 to make this easier to read.  Oddly, it looks right so far...

WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and mod(t1.time, 100) < 57)
   OR (t1.unit is NULL and mod(t0.time, 100) > 3)
   OR (t0.unit is not NULL and t1.unit is not NULL and t1.time - t0.time > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

226 rows:

UNIT      TIME      RN      UNIT      TIME      RN      7
null      null      null      1209      1207241000      1      Unit came online
1209      1207241059      60      1209      1207241100      61      *
null      null      null      1221      1207241000      1      Unit came online
1221      1207241059      57      1221      1207241100      58      *
null      null      null      1227      1207241000      1      Unit came online
1227      1207241059      51      1227      1207241100      52      *
null      null      null      1228      1207241000      1      Unit came online
1228      1207241059      57      null      null      null      Unit went offline
null      null      null      1230      1207241000      1      Unit came online
1230      1207241059      60      1230      1207241100      61      *
null      null      null      1231      1207241000      1      Unit came online
1231      1207241059      60      1231      1207241100      61      *
null      null      null      1233      1207241000      1      Unit came online
1233      1207241058      54      null      null      null      Unit went offline
null      null      null      1234      1207241000      1      Unit came online
1234      1207241059      55      1234      1207241100      56      *
null      null      null      1235      1207241000      1      Unit came online
1235      1207241059      60      1235      1207241100      61      *
null      null      null      1236      1207241002      1      Unit came online
1236      1207241059      56      1236      1207241100      57      *
null      null      null      1237      1207241000      1      Unit came online
1237      1207241059      60      1237      1207241100      61      *
null      null      null      1238      1207241000      1      Unit came online
1238      1207241059      51      1238      1207241100      52      *
null      null      null      1242      1207241000      1      Unit came online
1242      1207241059      59      1242      1207241100      60      *
null      null      null      1244      1207241000      1      Unit came online
1244      1207241059      59      1244      1207241100      60      *
null      null      null      1245      1207241000      1      Unit came online
1245      1207241059      60      1245      1207241100      61      *
null      null      null      1246      1207241000      1      Unit came online
1246      1207241059      60      1246      1207241100      61      *
null      null      null      1247      1207241000      1      Unit came online
null      null      null      1249      1207241000      1      Unit came online
1249      1207241046      47      null      null      null      Unit went offline
null      null      null      1251      1207241000      1      Unit came online
1251      1207241059      46      1251      1207241100      47      *
null      null      null      1252      1207241000      1      Unit came online
1252      1207241059      54      1252      1207241100      55      *
null      null      null      1254      1207241006      1      Unit came online
null      null      null      1255      1207241000      1      Unit came online
1255      1207241059      60      1255      1207241100      61      *
null      null      null      1257      1207241000      1      Unit came online
1257      1207241059      59      1257      1207241100      60      *
null      null      null      1258      1207241000      1      Unit came online
1258      1207241059      57      1258      1207241100      58      *
null      null      null      1260      1207241000      1      Unit came online
1260      1207241059      54      1260      1207241100      55      *
null      null      null      1261      1207241001      1      Unit came online
1261      1207241059      58      1261      1207241100      59      *
null      null      null      1262      1207241000      1      Unit came online
1262      1207241059      57      1262      1207241100      58      *
null      null      null      1264      1207241000      1      Unit came online
1264      1207241059      60      1264      1207241100      61      *
null      null      null      1265      1207241000      1      Unit came online
1265      1207241014      15      1265      1207241029      16      *
1265      1207241059      46      1265      1207241100      47      *
null      null      null      1267      1207241000      1      Unit came online
1267      1207241059      55      1267      1207241100      56      *
null      null      null      1268      1207241000      1      Unit came online
1268      1207241059      60      1268      1207241100      61      *
null      null      null      1269      1207241000      1      Unit came online
1269      1207241059      60      1269      1207241100      61      *
null      null      null      1270      1207241000      1      Unit came online
1270      1207241020      21      1270      1207241032      22      *
1270      1207241059      47      1270      1207241100      48      *
null      null      null      1271      1207241001      1      Unit came online
1271      1207241059      59      1271      1207241100      60      *
null      null      null      1272      1207241000      1      Unit came online
1272      1207241059      56      1272      1207241100      57      *
null      null      null      1273      1207241001      1      Unit came online
1273      1207241059      57      1273      1207241100      58      *
null      null      null      1274      1207241000      1      Unit came online
1274      1207241014      15      1274      1207241026      16      *
1274      1207241053      39      null      null      null      Unit went offline
null      null      null      1276      1207241000      1      Unit came online
1276      1207241059      57      1276      1207241100      58      *
null      null      null      1277      1207241013      1      Unit came online
1277      1207241054      39      null      null      null      Unit went offline
null      null      null      1282      1207241000      1      Unit came online
1282      1207241059      50      1282      1207241100      51      *
null      null      null      1283      1207241000      1      Unit came online
1283      1207241059      58      1283      1207241100      59      *
1284      1207240959      1      1284      1207241000      2      *
1284      1207241058      59      null      null      null      Unit went offline
null      null      null      1285      1207241000      1      Unit came online
1285      1207241059      59      1285      1207241100      60      *
null      null      null      1286      1207241000      1      Unit came online
1286      1207241059      52      1286      1207241100      53      *
null      null      null      1287      1207241000      1      Unit came online
1287      1207241059      55      1287      1207241100      56      *
null      null      null      1289      1207241000      1      Unit came online
1289      1207241059      60      1289      1207241100      61      *
null      null      null      1291      1207241000      1      Unit came online
1291      1207241036      35      1291      1207241048      36      *
1291      1207241059      45      1291      1207241100      46      *
null      null      null      1292      1207241000      1      Unit came online
1292      1207241059      54      1292      1207241100      55      *
null      null      null      1293      1207241000      1      Unit came online
1293      1207241059      50      1293      1207241100      51      *
null      null      null      1294      1207241001      1      Unit came online
1294      1207241059      58      1294      1207241100      59      *
null      null      null      1296      1207241000      1      Unit came online
1296      1207241059      60      1296      1207241100      61      *
null      null      null      1297      1207241000      1      Unit came online
1297      1207241057      52      null      null      null      Unit went offline
null      null      null      1298      1207241001      1      Unit came online
1298      1207241059      58      1298      1207241100      59      *
null      null      null      1301      1207241000      1      Unit came online
1301      1207241059      55      null      null      null      Unit went offline
null      null      null      1303      1207241000      1      Unit came online
1303      1207241059      60      1303      1207241100      61      *
null      null      null      1305      1207241000      1      Unit came online
1305      1207241059      53      1305      1207241100      54      *
null      null      null      1306      1207241012      1      Unit came online
1306      1207241059      48      1306      1207241100      49      *
null      null      null      1307      1207241000      1      Unit came online
1307      1207241058      59      1307      1207241100      60      *
null      null      null      1308      1207241000      1      Unit came online
1308      1207241054      53      1308      1207241100      54      *
null      null      null      1309      1207241001      1      Unit came online
1309      1207241021      21      1309      1207241042      22      *
1309      1207241056      36      null      null      null      Unit went offline
null      null      null      1310      1207241000      1      Unit came online
1310      1207241059      60      1310      1207241100      61      *
null      null      null      1311      1207241000      1      Unit came online
1311      1207241056      44      null      null      null      Unit went offline
null      null      null      1313      1207241003      1      Unit came online
1313      1207241059      49      1313      1207241100      50      *
null      null      null      1315      1207241000      1      Unit came online
1315      1207241059      55      1315      1207241100      56      *
null      null      null      1318      1207241000      1      Unit came online
1318      1207241059      55      1318      1207241100      56      *
null      null      null      1319      1207241000      1      Unit came online
1319      1207241059      53      1319      1207241100      54      *
null      null      null      1320      1207241000      1      Unit came online
1320      1207241059      51      1320      1207241100      52      *
null      null      null      1321      1207241005      1      Unit came online
1321      1207241057      52      null      null      null      Unit went offline
null      null      null      1322      1207241000      1      Unit came online
1322      1207241059      59      1322      1207241100      60      *
null      null      null      1323      1207241000      1      Unit came online
1323      1207241059      54      null      null      null      Unit went offline
null      null      null      1324      1207241000      1      Unit came online
1324      1207241059      56      1324      1207241100      57      *
null      null      null      1325      1207241000      1      Unit came online
1325      1207241059      58      1325      1207241100      59      *
null      null      null      1326      1207241000      1      Unit came online
1326      1207241028      29      1326      1207241042      30      *
1326      1207241059      47      1326      1207241100      48      *
1328      1207241008      1      1328      1207241024      2      *
null      null      null      1328      1207241008      1      Unit came online
1328      1207241059      35      1328      1207241100      36      *
null      null      null      1329      1207241000      1      Unit came online
1329      1207241059      57      1329      1207241100      58      *
null      null      null      1331      1207241000      1      Unit came online
1331      1207241059      60      1331      1207241100      61      *
null      null      null      1332      1207241000      1      Unit came online
1332      1207241059      52      1332      1207241100      53      *
null      null      null      1333      1207241000      1      Unit came online
1333      1207241059      60      1333      1207241100      61      *
null      null      null      1334      1207241000      1      Unit came online
1334      1207241056      55      1334      1207241100      56      *
null      null      null      1335      1207241000      1      Unit came online
1335      1207241059      54      1335      1207241100      55      *
null      null      null      1337      1207241000      1      Unit came online
1337      1207241059      60      1337      1207241100      61      *
null      null      null      1339      1207241018      1      Unit came online
1339      1207241019      2      null      null      null      Unit went offline
null      null      null      1340      1207241000      1      Unit came online
1340      1207241059      60      1340      1207241100      61      *
null      null      null      1341      1207241000      1      Unit came online
1341      1207241059      60      1341      1207241100      61      *
null      null      null      1343      1207241000      1      Unit came online
1343      1207241059      59      1343      1207241100      60      *
null      null      null      1344      1207241000      1      Unit came online
1344      1207241059      60      1344      1207241100      61      *
null      null      null      1350      1207241000      1      Unit came online
1350      1207241059      60      1350      1207241100      61      *
null      null      null      1351      1207241000      1      Unit came online
1351      1207241059      59      1351      1207241100      60      *
null      null      null      1353      1207241000      1      Unit came online
1353      1207241059      60      1353      1207241100      61      *
null      null      null      1354      1207241000      1      Unit came online
1354      1207241059      57      1354      1207241100      58      *
null      null      null      1355      1207241000      1      Unit came online
1355      1207241059      59      1355      1207241100      60      *
null      null      null      1357      1207241000      1      Unit came online
1357      1207241059      58      1357      1207241100      59      *
null      null      null      1359      1207241000      1      Unit came online
1359      1207241059      58      1359      1207241100      59      *
null      null      null      1364      1207241103      1      Unit came online
1364      1207241159      57      1364      1207241200      58      *
null      null      null      1366      1207241000      1      Unit came online
1366      1207241059      36      1366      1207241100      37      *
null      null      null      1367      1207241000      1      Unit came online
1367      1207241059      57      1367      1207241100      58      *
null      null      null      1368      1207241000      1      Unit came online
1368      1207241059      59      1368      1207241100      60      *
null      null      null      1371      1207241003      1      Unit came online
1371      1207241059      52      1371      1207241100      53      *
null      null      null      1375      1207241000      1      Unit came online
1375      1207241059      49      1375      1207241100      50      *
null      null      null      1377      1207241000      1      Unit came online
1377      1207241058      54      1377      1207241100      55      *
null      null      null      1378      1207241000      1      Unit came online
1378      1207241059      58      1378      1207241100      59      *
null      null      null      1379      1207241000      1      Unit came online
1379      1207241059      57      1379      1207241100      58      *
null      null      null      1380      1207241003      1      Unit came online
1380      1207241059      56      1380      1207241100      57      *
null      null      null      1382      1207241000      1      Unit came online
1382      1207241059      53      1382      1207241100      54      *
null      null      null      1383      1207241000      1      Unit came online
1383      1207241056      57      null      null      null      Unit went offline
null      null      null      1384      1207241000      1      Unit came online
1384      1207241059      58      1384      1207241100      59      *
null      null      null      1385      1207241000      1      Unit came online
1385      1207241059      55      1385      1207241100      56      *
null      null      null      1386      1207241000      1      Unit came online
1386      1207241059      54      1386      1207241100      55      *
null      null      null      1387      1207241011      1      Unit came online
1387      1207241038      28      1387      1207241054      29      *
1387      1207241059      34      1387      1207241100      35      *
null      null      null      1388      1207241001      1      Unit came online
1388      1207241059      52      1388      1207241100      53      *
Another oops.  sigh...


WITH minute_data (unit, time, rn)
AS
(
  SELECT i_radio_id, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and mod(t1.time, 100) > 3)
   OR (t1.unit is NULL and mod(t0.time, 100) < 57)
   OR (t0.unit is not NULL and t1.unit is not NULL and t1.time - t0.time > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time)
Avatar of Mi-Jack

ASKER

with a 10-minute interval:

UNIT      TIME      RN      UNIT      TIME      RN      7
1209      1207241059      60      1209      1207241100      61      *
1209      1207241100      61      null      null      null      Unit went offline
1221      1207241059      57      1221      1207241100      58      *
1221      1207241100      58      null      null      null      Unit went offline
1227      1207241059      51      1227      1207241100      52      *
1227      1207241100      52      null      null      null      Unit went offline
1230      1207241059      60      1230      1207241100      61      *
1230      1207241100      61      null      null      null      Unit went offline
1231      1207241059      60      1231      1207241100      61      *
1231      1207241100      61      null      null      null      Unit went offline
1234      1207241059      55      1234      1207241100      56      *
1234      1207241100      56      null      null      null      Unit went offline
1235      1207241059      60      1235      1207241100      61      *
1235      1207241100      61      null      null      null      Unit went offline
1236      1207241059      56      1236      1207241100      57      *
1236      1207241100      57      null      null      null      Unit went offline
1237      1207241059      60      1237      1207241100      61      *
1237      1207241100      61      null      null      null      Unit went offline
1238      1207241059      51      1238      1207241100      52      *
1238      1207241100      52      null      null      null      Unit went offline
1242      1207241059      59      1242      1207241100      60      *
1242      1207241100      60      null      null      null      Unit went offline
1244      1207241059      59      1244      1207241100      60      *
1244      1207241100      60      null      null      null      Unit went offline
1245      1207241059      60      1245      1207241100      61      *
1245      1207241100      61      null      null      null      Unit went offline
1246      1207241059      60      1246      1207241100      61      *
1246      1207241100      61      null      null      null      Unit went offline
1247      1207241008      9      null      null      null      Unit went offline
1249      1207241046      47      null      null      null      Unit went offline
1251      1207241059      46      1251      1207241100      47      *
1251      1207241100      47      null      null      null      Unit went offline
1252      1207241059      54      1252      1207241100      55      *
1252      1207241100      55      null      null      null      Unit went offline
1254      1207241010      5      null      null      null      Unit went offline
1255      1207241059      60      1255      1207241100      61      *
1255      1207241100      61      null      null      null      Unit went offline
1257      1207241059      59      1257      1207241100      60      *
1257      1207241100      60      null      null      null      Unit went offline
1258      1207241059      57      1258      1207241100      58      *
1258      1207241100      58      null      null      null      Unit went offline
1260      1207241059      54      1260      1207241100      55      *
1260      1207241100      55      null      null      null      Unit went offline
1261      1207241059      58      1261      1207241100      59      *
1261      1207241100      59      null      null      null      Unit went offline
1262      1207241059      57      1262      1207241100      58      *
1262      1207241100      58      null      null      null      Unit went offline
1264      1207241059      60      1264      1207241100      61      *
1264      1207241100      61      null      null      null      Unit went offline
1265      1207241014      15      1265      1207241029      16      *
1265      1207241059      46      1265      1207241100      47      *
1265      1207241100      47      null      null      null      Unit went offline
1267      1207241059      55      1267      1207241100      56      *
1267      1207241100      56      null      null      null      Unit went offline
1268      1207241059      60      1268      1207241100      61      *
1268      1207241100      61      null      null      null      Unit went offline
1269      1207241059      60      1269      1207241100      61      *
1269      1207241100      61      null      null      null      Unit went offline
1270      1207241020      21      1270      1207241032      22      *
1270      1207241059      47      1270      1207241100      48      *
1270      1207241100      48      null      null      null      Unit went offline
1271      1207241059      59      1271      1207241100      60      *
1271      1207241101      61      null      null      null      Unit went offline
1272      1207241059      56      1272      1207241100      57      *
1272      1207241100      57      null      null      null      Unit went offline
1273      1207241059      57      1273      1207241100      58      *
1273      1207241100      58      null      null      null      Unit went offline
1274      1207241014      15      1274      1207241026      16      *
1276      1207241059      57      1276      1207241100      58      *
1276      1207241100      58      null      null      null      Unit went offline
null      null      null      1277      1207241013      1      Unit came online
1282      1207241059      50      1282      1207241100      51      *
1282      1207241100      51      null      null      null      Unit went offline
1283      1207241059      58      1283      1207241100      59      *
1283      1207241100      59      null      null      null      Unit went offline
1284      1207240959      1      1284      1207241000      2      *
null      null      null      1284      1207240959      1      Unit came online
1285      1207241059      59      1285      1207241100      60      *
1285      1207241100      60      null      null      null      Unit went offline
1286      1207241059      52      1286      1207241100      53      *
1286      1207241100      53      null      null      null      Unit went offline
1287      1207241059      55      1287      1207241100      56      *
1287      1207241100      56      null      null      null      Unit went offline
1289      1207241059      60      1289      1207241100      61      *
1289      1207241100      61      null      null      null      Unit went offline
1291      1207241036      35      1291      1207241048      36      *
1291      1207241059      45      1291      1207241100      46      *
1291      1207241100      46      null      null      null      Unit went offline
1292      1207241059      54      1292      1207241100      55      *
1292      1207241100      55      null      null      null      Unit went offline
1293      1207241059      50      1293      1207241100      51      *
1293      1207241100      51      null      null      null      Unit went offline
1294      1207241059      58      1294      1207241100      59      *
1294      1207241100      59      null      null      null      Unit went offline
1296      1207241059      60      1296      1207241100      61      *
1296      1207241100      61      null      null      null      Unit went offline
1298      1207241059      58      1298      1207241100      59      *
1298      1207241100      59      null      null      null      Unit went offline
1303      1207241059      60      1303      1207241100      61      *
1303      1207241100      61      null      null      null      Unit went offline
1305      1207241059      53      1305      1207241100      54      *
1305      1207241100      54      null      null      null      Unit went offline
null      null      null      1306      1207241012      1      Unit came online
1306      1207241059      48      1306      1207241100      49      *
1306      1207241100      49      null      null      null      Unit went offline
1307      1207241058      59      1307      1207241100      60      *
1307      1207241100      60      null      null      null      Unit went offline
1308      1207241054      53      1308      1207241100      54      *
1308      1207241100      54      null      null      null      Unit went offline
1309      1207241021      21      1309      1207241042      22      *
1310      1207241059      60      1310      1207241100      61      *
1310      1207241100      61      null      null      null      Unit went offline
1313      1207241059      49      1313      1207241100      50      *
1313      1207241100      50      null      null      null      Unit went offline
1315      1207241059      55      1315      1207241100      56      *
1315      1207241100      56      null      null      null      Unit went offline
1318      1207241059      55      1318      1207241100      56      *
1318      1207241100      56      null      null      null      Unit went offline
1319      1207241059      53      1319      1207241100      54      *
1319      1207241100      54      null      null      null      Unit went offline
1320      1207241059      51      1320      1207241100      52      *
1320      1207241100      52      null      null      null      Unit went offline
1322      1207241059      59      1322      1207241100      60      *
1322      1207241100      60      null      null      null      Unit went offline
1324      1207241059      56      1324      1207241100      57      *
1324      1207241100      57      null      null      null      Unit went offline
1325      1207241059      58      1325      1207241100      59      *
1325      1207241100      59      null      null      null      Unit went offline
1326      1207241028      29      1326      1207241042      30      *
1326      1207241059      47      1326      1207241100      48      *
1326      1207241100      48      null      null      null      Unit went offline
1328      1207241008      1      1328      1207241024      2      *
1328      1207241059      35      1328      1207241100      36      *
1328      1207241100      36      null      null      null      Unit went offline
1329      1207241059      57      1329      1207241100      58      *
1329      1207241100      58      null      null      null      Unit went offline
1331      1207241059      60      1331      1207241100      61      *
1331      1207241100      61      null      null      null      Unit went offline
1332      1207241059      52      1332      1207241100      53      *
1332      1207241100      53      null      null      null      Unit went offline
1333      1207241059      60      1333      1207241100      61      *
1333      1207241100      61      null      null      null      Unit went offline
1334      1207241056      55      1334      1207241100      56      *
1334      1207241100      56      null      null      null      Unit went offline
1335      1207241059      54      1335      1207241100      55      *
1335      1207241100      55      null      null      null      Unit went offline
1337      1207241059      60      1337      1207241100      61      *
1337      1207241100      61      null      null      null      Unit went offline
null      null      null      1339      1207241018      1      Unit came online
1339      1207241019      2      null      null      null      Unit went offline
1340      1207241059      60      1340      1207241100      61      *
1340      1207241100      61      null      null      null      Unit went offline
1341      1207241059      60      1341      1207241100      61      *
1341      1207241100      61      null      null      null      Unit went offline
1343      1207241059      59      1343      1207241100      60      *
1343      1207241100      60      null      null      null      Unit went offline
1344      1207241059      60      1344      1207241100      61      *
1344      1207241100      61      null      null      null      Unit went offline
1350      1207241059      60      1350      1207241100      61      *
1350      1207241100      61      null      null      null      Unit went offline
1351      1207241059      59      1351      1207241100      60      *
1351      1207241100      60      null      null      null      Unit went offline
1353      1207241059      60      1353      1207241100      61      *
1353      1207241100      61      null      null      null      Unit went offline
1354      1207241059      57      1354      1207241100      58      *
1354      1207241100      58      null      null      null      Unit went offline
1355      1207241059      59      1355      1207241100      60      *
1355      1207241100      60      null      null      null      Unit went offline
1357      1207241059      58      1357      1207241100      59      *
1357      1207241100      59      null      null      null      Unit went offline
1359      1207241059      58      1359      1207241100      59      *
1359      1207241100      59      null      null      null      Unit went offline
1364      1207241159      57      1364      1207241200      58      *
1364      1207241200      58      null      null      null      Unit went offline
1366      1207241059      36      1366      1207241100      37      *
1366      1207241100      37      null      null      null      Unit went offline
1367      1207241059      57      1367      1207241100      58      *
1367      1207241100      58      null      null      null      Unit went offline
1368      1207241059      59      1368      1207241100      60      *
1368      1207241100      60      null      null      null      Unit went offline
1371      1207241059      52      1371      1207241100      53      *
1371      1207241100      53      null      null      null      Unit went offline
1375      1207241059      49      1375      1207241100      50      *
1375      1207241100      50      null      null      null      Unit went offline
1377      1207241058      54      1377      1207241100      55      *
1377      1207241100      55      null      null      null      Unit went offline
1378      1207241059      58      1378      1207241100      59      *
1378      1207241100      59      null      null      null      Unit went offline
1379      1207241059      57      1379      1207241100      58      *
1379      1207241100      58      null      null      null      Unit went offline
1380      1207241059      56      1380      1207241100      57      *
1380      1207241100      57      null      null      null      Unit went offline
1382      1207241059      53      1382      1207241100      54      *
1382      1207241100      54      null      null      null      Unit went offline
1384      1207241059      58      1384      1207241100      59      *
1384      1207241100      59      null      null      null      Unit went offline
1385      1207241059      55      1385      1207241100      56      *
1385      1207241100      56      null      null      null      Unit went offline
1386      1207241059      54      1386      1207241100      55      *
1386      1207241100      55      null      null      null      Unit went offline
null      null      null      1387      1207241011      1      Unit came online
1387      1207241038      28      1387      1207241054      29      *
1387      1207241059      34      1387      1207241100      35      *
1387      1207241100      35      null      null      null      Unit went offline
1388      1207241059      52      1388      1207241100      53      *
1388      1207241100      53      null      null      null      Unit went offline
Avatar of Mi-Jack

ASKER

Leaving for the day.

Can you briefly explain each column to me, please?
UNIT      TIME      RN      UNIT      TIME      RN      7

What is RN exactly?
And the second UNIT,  TIME and  RN columns?

I don't want to guess - not enough gray matter.

I am keeping my fingers crossed that we see this to the end.
Thank you, talk to you tomorrow.
Ok.  It looks like we're going to have to find another way to do the date/time math.  It looks like the table starts before the hour (a lot of minute 59s in the first row for a unit) and extends for 62 minutes.  The original calculation that multiplied by 60 should have accounted for that, but it would certainly break down at midnight every day.

Let's convert the date and time to a timestamp and let the DB2 date/time manipulation functions do their jobs....

And let's see if I can type this correctly....

WITH minute_data (unit, ts, time, rn)
AS
(
  SELECT i_radio_id, time, ts, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      timestamp (digits(i_year) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.ts > (SELECT min(ts) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.ts < (SELECT max(ts) FROM minute_data) - 3 minutes)
   OR (t0.unit is not NULL and t1.unit is not NULL AND timestampdiff (4, char(t1.ts - t0.ts)) > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.ts, t1.ts)
We'll see this through.  :)

Can you briefly explain each column to me, please?
UNIT      TIME      RN      UNIT      TIME      RN      7


The syntax of this query (starting with the keyword WITH) is called a Common Table Expression (CTE).  In the most simple of terms, we create something similar to a temporary view, and can then query it repeatedly in the same SQL.  (We can also use this structure for INSERT and other SQL commands.)

The inner subquery (where we actually SELECT from your table) adds a column to the items read.  That column is the row number within each radio_id.  If you have 6 rows with radio id 1 and 4 more with id 2, the added column will contain 1-6 (sorted by time) for id 1 and 1-4 for id 2.  It also selects only 1 row per minute per unit.

We then select all of the rows from the CTE and join the rows with themselves, offset by one position.

If this is the data:
UNIT RN
100  1
100  2
100  3
100  4
200  1
200  2

the query joins row 1 with row 2.  Then row 2 with row 3, etc.  With a break every time the unit number changes.  The result is:

UNIT   RN UNIT   RN
NULL NULL 100     1
 100    1  100    2 
 100    2  100    3
 100    3  100    4
 100    4 NULL NULL
NULL NULL  200    1
 200    1  200    2
 200    2 NULL NULL

Open in new window


We're also selecting the computed time for each row.  (It's probably easier to see without it.)

Once we have the rows paired up that way, we can compare the time of the adjacent rows.
Avatar of Mi-Jack

ASKER

Wow.
Modified query (Added 2000 to the year):

WITH minute_data (unit, ts, time, rn)
AS
(
  SELECT i_radio_id, time, ts, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
     timestamp(right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.*, t1.*,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.ts > (SELECT min(ts) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.ts < (SELECT max(ts) FROM minute_data) - 3 minutes)
   OR (t0.unit is not NULL and t1.unit is not NULL AND timestampdiff (4, char(t1.ts - t0.ts)) > 3)
ORDER BY coalesce (t0.unit, t1.unit), coalesce (t0.ts, t1.ts)
------------------------------------------------------------------------------------------------------------

The
SELECT DISTINCT I_radio_id,
     timestamp(right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0

runs fine by itself, but the whole query produces this error:

ERROR: A datetime value or duration in an expression is invalid.

DB2
SQL Error: SQLCODE=-182, SQLSTATE=42816, SQLERRMC=null,
DRIVER=3.57.82
Error Code: -182
Avatar of Mi-Jack

ASKER

This also runs with no errors:

SELECT timestampdiff (4, char(
    timestamp(right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000')
    -
    timestamp(right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour-1),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000')
    )) td
    FROM DBO.PositionRecord2012072410
Let's test the inner sub-query

    SELECT DISTINCT I_radio_id,
     timestamp(right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0

by removing the recast to timestamp

    SELECT DISTINCT I_radio_id,
     right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000' ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
Avatar of Mi-Jack

ASKER

This runs with no problems

1209      2012-07-24-10.00.00.000000      1207241000
1209      2012-07-24-10.01.00.000000      1207241001
1209      2012-07-24-10.02.00.000000      1207241002
...
Rats.  We must be passing a NULL to timestampdiff.  Let's try this query:

WITH minute_data (unit, ts, time, rn)
AS
(
  SELECT i_radio_id, time, ts, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      timestamp (digits(i_year) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.unit, t0.ts starttime, t1.ts endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.ts > (SELECT min(ts) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.ts < (SELECT max(ts) FROM minute_data) - 3 minutes)

UNION ALL

SELECT t0.unit, t0.ts starttime, t1.ts endtime, t0.rn, t1.rn, '*'
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE t0.unit is not NULL
  AND t1.unit is not NULL
  AND timestampdiff (4, char(coalesce (t1.ts, current_timestamp) - coalesce (t0.ts, current_timestamp))) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

ERROR: A datetime value or duration in an expression is invalid.

DB2
SQL Error: SQLCODE=-182, SQLSTATE=42816, SQLERRMC=null,
DRIVER=3.57.82
Error Code: -182
Ok.  If THIS one fails, we've got a data issue and we'll need to find it and code around it.


WITH minute_data (unit, ts, time, rn)
AS
(
  SELECT i_radio_id, time, ts, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
      timestamp (digits(i_year) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
  ) t2
)
SELECT t0.unit, t0.ts starttime, t1.ts endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.ts > (SELECT min(ts) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.ts < (SELECT max(ts) FROM minute_data) - 3 minutes)

UNION ALL

SELECT *
FROM
(
      SELECT t0.unit, t0.ts starttime, t1.ts endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
            ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
            AND t1.unit is not NULL
) t0
WHERE timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

If I delete this line:

WHERE timestampdiff (4, char(endtime - starttime)) > 3

I still get the same error
Avatar of Mi-Jack

ASKER

To sum this last one up:

This (slightly modified) runs with no errors:

SELECT DISTINCT I_radio_id,
      timestamp (right('00'||digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM DBO.PositionRecord2012072410  where i_code=0
---------------------------------------------------------------------------------
The whole query produces this error:

ERROR: A datetime value or duration in an expression is invalid.

DB2
SQL Error: SQLCODE=-182, SQLSTATE=42816, SQLERRMC=null,
DRIVER=3.57.82
Error Code: -182

----------------------------------------------------------------------------
and if I delete this line:

WHERE timestampdiff (4, char(endtime - starttime)) > 3

the error is the same
"Slightly modified".  Is that just an extract from the larger query or did you have to change something?
Avatar of Mi-Jack

ASKER

I changed  
timestamp (digits(i_year)
to
timestamp (right('00'||digits(i_year+2000),4)
Ah.  Ok.  I'll note that.

My development system is being rebuilt right now.  Can you give me just a few minutes to finish that so I can test this query without just throwing code back and forth?


Kent
Avatar of Mi-Jack

ASKER

I am eternally grateful for you sticking with this, just whenever you can.
I apologize that I can't always be here to reply right away.

Gene
Hi Gene,

Ok.  Let's chalk this one up to "I'm gonna be really stupid today".  In the CTE, I had swapped the TIME and TS parameters.  In the query of the CTE, the computed date (an integer) was placed where the timestamp was expected and vice-versa.


This one looks pretty gook.  At least on my test system.

WITH minute_data (unit, ts, time, rn)
AS
(
  SELECT i_radio_id, ts, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
       timestamp(right (digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM t
--    where i_code=0
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.ts, t1.ts) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.ts > (SELECT min(ts) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.ts < (SELECT max(ts) FROM minute_data) - 3 minutes)

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.ts starttime, t1.ts endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
            ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
            AND t1.unit is not NULL
) t0
WHERE timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2; 

Open in new window

Avatar of Mi-Jack

ASKER

178 rows:

1      STARTTIME      ENDTIME      RN      RN      6
1209      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1221      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1227      2012-07-24 10:35:00.0      2012-07-24 10:41:00.0      33      34      *
1227      2012-07-24 11:00:00.0      null      52      null      Unit went offline
1228      2012-07-24 10:50:00.0      2012-07-24 10:54:00.0      51      52      *
1228      2012-07-24 10:59:00.0      null      57      null      Unit went offline
1230      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1231      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1233      2012-07-24 10:58:00.0      null      54      null      Unit went offline
1234      2012-07-24 10:23:00.0      2012-07-24 10:27:00.0      22      23      *
1234      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1235      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1236      2012-07-24 11:00:00.0      null      57      null      Unit went offline
1237      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1238      2012-07-24 10:31:00.0      2012-07-24 10:37:00.0      32      33      *
1238      2012-07-24 10:46:00.0      2012-07-24 10:50:00.0      42      43      *
1238      2012-07-24 11:00:00.0      null      52      null      Unit went offline
1242      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1244      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1245      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1246      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1247      2012-07-24 10:08:00.0      null      9      null      Unit went offline
1249      2012-07-24 10:46:00.0      null      47      null      Unit went offline
1251      2012-07-24 10:02:00.0      2012-07-24 10:06:00.0      3      4      *
1251      2012-07-24 10:26:00.0      2012-07-24 10:31:00.0      22      23      *
1251      2012-07-24 10:47:00.0      2012-07-24 10:52:00.0      39      40      *
1251      2012-07-24 11:00:00.0      null      47      null      Unit went offline
1252      2012-07-24 11:00:00.0      null      55      null      Unit went offline
1254      2012-07-24 10:06:00.0      null      null      1      Unit came online
1254      2012-07-24 10:10:00.0      null      5      null      Unit went offline
1255      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1257      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1258      2012-07-24 10:24:00.0      2012-07-24 10:28:00.0      25      26      *
1258      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1260      2012-07-24 10:36:00.0      2012-07-24 10:41:00.0      36      37      *
1260      2012-07-24 11:00:00.0      null      55      null      Unit went offline
1261      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1262      2012-07-24 10:45:00.0      2012-07-24 10:49:00.0      46      47      *
1262      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1264      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1265      2012-07-24 10:14:00.0      2012-07-24 10:29:00.0      15      16      *
1265      2012-07-24 11:00:00.0      null      47      null      Unit went offline
1267      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1268      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1269      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1270      2012-07-24 10:20:00.0      2012-07-24 10:32:00.0      21      22      *
1270      2012-07-24 11:00:00.0      null      48      null      Unit went offline
1271      2012-07-24 11:01:00.0      null      61      null      Unit went offline
1272      2012-07-24 10:17:00.0      2012-07-24 10:21:00.0      18      19      *
1272      2012-07-24 11:00:00.0      null      57      null      Unit went offline
1273      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1274      2012-07-24 10:14:00.0      2012-07-24 10:26:00.0      15      16      *
1274      2012-07-24 10:34:00.0      2012-07-24 10:38:00.0      24      25      *
1274      2012-07-24 10:53:00.0      null      39      null      Unit went offline
1276      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1277      2012-07-24 10:13:00.0      null      null      1      Unit came online
1277      2012-07-24 10:54:00.0      null      39      null      Unit went offline
1282      2012-07-24 10:44:00.0      2012-07-24 10:49:00.0      44      45      *
1282      2012-07-24 10:50:00.0      2012-07-24 10:55:00.0      46      47      *
1282      2012-07-24 11:00:00.0      null      51      null      Unit went offline
1283      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1284      2012-07-24 10:58:00.0      null      59      null      Unit went offline
1285      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1286      2012-07-24 10:54:00.0      2012-07-24 10:59:00.0      51      52      *
1286      2012-07-24 11:00:00.0      null      53      null      Unit went offline
1287      2012-07-24 10:36:00.0      2012-07-24 10:40:00.0      35      36      *
1287      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1289      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1291      2012-07-24 10:36:00.0      2012-07-24 10:48:00.0      35      36      *
1291      2012-07-24 11:00:00.0      null      46      null      Unit went offline
1292      2012-07-24 11:00:00.0      null      55      null      Unit went offline
1293      2012-07-24 10:27:00.0      2012-07-24 10:35:00.0      26      27      *
1293      2012-07-24 11:00:00.0      null      51      null      Unit went offline
1294      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1296      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1297      2012-07-24 10:26:00.0      2012-07-24 10:31:00.0      27      28      *
1297      2012-07-24 10:57:00.0      null      52      null      Unit went offline
1298      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1301      2012-07-24 10:11:00.0      2012-07-24 10:16:00.0      12      13      *
1301      2012-07-24 10:59:00.0      null      55      null      Unit went offline
1303      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1305      2012-07-24 10:03:00.0      2012-07-24 10:08:00.0      4      5      *
1305      2012-07-24 11:00:00.0      null      54      null      Unit went offline
1306      2012-07-24 10:12:00.0      null      null      1      Unit came online
1306      2012-07-24 11:00:00.0      null      49      null      Unit went offline
1307      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1308      2012-07-24 10:54:00.0      2012-07-24 11:00:00.0      53      54      *
1308      2012-07-24 11:00:00.0      null      54      null      Unit went offline
1309      2012-07-24 10:21:00.0      2012-07-24 10:42:00.0      21      22      *
1309      2012-07-24 10:56:00.0      null      36      null      Unit went offline
1310      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1311      2012-07-24 10:03:00.0      2012-07-24 10:09:00.0      4      5      *
1311      2012-07-24 10:42:00.0      2012-07-24 10:46:00.0      38      39      *
1311      2012-07-24 10:49:00.0      2012-07-24 10:55:00.0      42      43      *
1311      2012-07-24 10:56:00.0      null      44      null      Unit went offline
1313      2012-07-24 10:03:00.0      null      null      1      Unit came online
1313      2012-07-24 10:06:00.0      2012-07-24 10:12:00.0      4      5      *
1313      2012-07-24 10:20:00.0      2012-07-24 10:24:00.0      13      14      *
1313      2012-07-24 11:00:00.0      null      50      null      Unit went offline
1315      2012-07-24 10:07:00.0      2012-07-24 10:12:00.0      8      9      *
1315      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1318      2012-07-24 10:48:00.0      2012-07-24 10:53:00.0      48      49      *
1318      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1319      2012-07-24 10:16:00.0      2012-07-24 10:20:00.0      17      18      *
1319      2012-07-24 10:38:00.0      2012-07-24 10:43:00.0      36      37      *
1319      2012-07-24 11:00:00.0      null      54      null      Unit went offline
1320      2012-07-24 10:10:00.0      2012-07-24 10:17:00.0      11      12      *
1320      2012-07-24 10:18:00.0      2012-07-24 10:22:00.0      13      14      *
1320      2012-07-24 11:00:00.0      null      52      null      Unit went offline
1321      2012-07-24 10:05:00.0      null      null      1      Unit came online
1321      2012-07-24 10:57:00.0      null      52      null      Unit went offline
1322      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1323      2012-07-24 10:59:00.0      null      54      null      Unit went offline
1324      2012-07-24 10:12:00.0      2012-07-24 10:17:00.0      13      14      *
1324      2012-07-24 11:00:00.0      null      57      null      Unit went offline
1325      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1326      2012-07-24 10:28:00.0      2012-07-24 10:42:00.0      29      30      *
1326      2012-07-24 11:00:00.0      null      48      null      Unit went offline
1328      2012-07-24 10:08:00.0      2012-07-24 10:24:00.0      1      2      *
1328      2012-07-24 10:08:00.0      null      null      1      Unit came online
1328      2012-07-24 11:00:00.0      null      36      null      Unit went offline
1329      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1331      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1332      2012-07-24 10:39:00.0      2012-07-24 10:47:00.0      39      40      *
1332      2012-07-24 11:00:00.0      null      53      null      Unit went offline
1333      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1334      2012-07-24 10:56:00.0      2012-07-24 11:00:00.0      55      56      *
1334      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1335      2012-07-24 10:31:00.0      2012-07-24 10:36:00.0      32      33      *
1335      2012-07-24 11:00:00.0      null      55      null      Unit went offline
1337      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1339      2012-07-24 10:18:00.0      null      null      1      Unit came online
1339      2012-07-24 10:19:00.0      null      2      null      Unit went offline
1340      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1341      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1343      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1344      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1350      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1351      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1353      2012-07-24 11:00:00.0      null      61      null      Unit went offline
1354      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1355      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1357      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1359      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1364      2012-07-24 11:03:00.0      null      null      1      Unit came online
1366      2012-07-24 10:02:00.0      2012-07-24 10:12:00.0      3      4      *
1366      2012-07-24 10:22:00.0      2012-07-24 10:29:00.0      14      15      *
1366      2012-07-24 10:35:00.0      2012-07-24 10:41:00.0      21      22      *
1366      2012-07-24 10:45:00.0      2012-07-24 10:50:00.0      26      27      *
1366      2012-07-24 11:00:00.0      null      37      null      Unit went offline
1367      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1368      2012-07-24 11:00:00.0      null      60      null      Unit went offline
1371      2012-07-24 10:03:00.0      null      null      1      Unit came online
1371      2012-07-24 10:37:00.0      2012-07-24 10:41:00.0      35      36      *
1371      2012-07-24 11:00:00.0      null      53      null      Unit went offline
1375      2012-07-24 10:10:00.0      2012-07-24 10:15:00.0      11      12      *
1375      2012-07-24 10:40:00.0      2012-07-24 10:46:00.0      35      36      *
1375      2012-07-24 11:00:00.0      null      50      null      Unit went offline
1377      2012-07-24 10:10:00.0      2012-07-24 10:14:00.0      11      12      *
1377      2012-07-24 11:00:00.0      null      55      null      Unit went offline
1378      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1379      2012-07-24 10:10:00.0      2012-07-24 10:14:00.0      11      12      *
1379      2012-07-24 11:00:00.0      null      58      null      Unit went offline
1380      2012-07-24 10:03:00.0      null      null      1      Unit came online
1380      2012-07-24 11:00:00.0      null      57      null      Unit went offline
1382      2012-07-24 10:08:00.0      2012-07-24 10:14:00.0      9      10      *
1382      2012-07-24 11:00:00.0      null      54      null      Unit went offline
1383      2012-07-24 10:56:00.0      null      57      null      Unit went offline
1384      2012-07-24 11:00:00.0      null      59      null      Unit went offline
1385      2012-07-24 10:17:00.0      2012-07-24 10:23:00.0      18      19      *
1385      2012-07-24 11:00:00.0      null      56      null      Unit went offline
1386      2012-07-24 10:05:00.0      2012-07-24 10:11:00.0      6      7      *
1386      2012-07-24 11:00:00.0      null      55      null      Unit went offline
1387      2012-07-24 10:11:00.0      null      null      1      Unit came online
1387      2012-07-24 10:38:00.0      2012-07-24 10:54:00.0      28      29      *
1387      2012-07-24 11:00:00.0      null      35      null      Unit went offline
1388      2012-07-24 10:38:00.0      2012-07-24 10:43:00.0      35      36      *
1388      2012-07-24 11:00:00.0      null      53      null      Unit went offline
Hmm...  We need to change line 12 so that it's no longer a comment.

Let's find out the times in the table when recording starts/stops.  It looks like we may have to refine our rules.


WITH minute_data (unit, ts, time, rn)
AS
(
  SELECT i_radio_id, ts, time, row_number () over (partition by i_radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
       timestamp(right (digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') ts,
      i_year * 100000000 + i_month * 1000000 + i_day * 10000 + i_hour * 100 + i_minute time
    FROM t
    where i_code=0
  ) t2
)
SELECT min (ts), max (t2) FROM minute_data;
Avatar of Mi-Jack

ASKER

results:

1      2
2012-07-24 09:59:00.0      2012-07-24 12:00:00.0

I'm going to check the data...
Avatar of Mi-Jack

ASKER

Unbelievably enough, there's 2 dozen records from hour 12 from 1 unit - 1364.
There's 1 record at 9:59
The time is unit time, some units may be off by a minute or so - apparently, some may have incorrect DST. We should disregard this.
For the hour 10, the range will be from 9:59 to 11:01 no more than that.
That is interesting.  I hadn't considered that the timestamp could come from the unit instead of the database.

We could make the query times relative to the unit instead of the table, but if we do we have do way to determine that the first time is late enough to signal "came online" or that the last time is early enough to signal "went offline".
Avatar of Mi-Jack

ASKER

We should just accept times between <table hour - 1 minute> and
<table hour + 1 hour + 1 minute> and just disregard everything else
Avatar of Mi-Jack

ASKER

It's complicated enough as it is, and on top of that there's all these problems - time discrepancy between units themselves and also with the server, latency (some records have to be re-sent and arrive up to 30 seconds later), lost records (never made it to the database - to name just a few.
I didn't want to mention all this, but we're working with actual data, going pretty deep into it
It would seem that quite a few of the issues just "go away" if you merge the data from consecutive tables.  If you take the granularity down to the minute, you trim the number of rows a great deal so storage wouldn't be a problem.
Avatar of Mi-Jack

ASKER

I'm beginning to think that maybe query should span 2 tables - current hour and previous hour. That way a lot of those timestamp anomalies will be easier to handle...
The query doesn't know the "table hour".  That would have to be a parameter to a stored procedure, or edited into the query for each table.
Avatar of Mi-Jack

ASKER

What do you think, what's the best approach?
Avatar of Mi-Jack

ASKER

I agree, that was my plan all along - I wanted to call a SP from the application that creates the new hourly tables
Avatar of Mi-Jack

ASKER

It would be perfectly fine if I could do it in several steps instead of just one
How often do you need to take this report?  Can you assemble the data from 24 tables (a full day) into a single table and query it?  95% of the report (or more) would have an easy meaning then.
Avatar of Mi-Jack

ASKER

The data is bulky and dirty, impossible to analyze in the current form, so my idea was this:
Create a single table for per-processed data, then
run some logic every hour (after a new table is created, run it on the previous hour's table), or every day (perfectly acceptable, and if the current day's data is needed to be added, have some additional process) to populate that table.
Then actual reports will only have query that single table.
I'm open to any suggestions.
I can actually do this from an application instead through SQL only - that's what I meant by several steps, incrementally drilling the data.
I like the single table idea.  The sub-query of the CTE can be used to populate the single table with minute granularity status.  Once the table has several day's date (indexed by date and/or unit number) pulling statistics for any day or hour should be much cleaner.  If you "ignore" the delta time between the units and reality and accept their recorded time as correct, things get lots simpler.
Avatar of Mi-Jack

ASKER

Is there a way I can use the SQL you came up with so far to accomplish this?
Sure.  :)

The SQL that we're using will parse the one-hour snapshots, but since the field unit's times aren't necessarily in synch, it'll be very tough to tell if the first entry for a unit is "normal" or looks like the unit just "came online".  Similarly, identifying whether the last entry for a unit is normal or if the unit looks like it is going offline.

One thing that we can add to the query is to compare the first and last timestamp for each unit.  If they are more than (for example) 55 minutes apart, there would be no test for the start and end of the hour.  You might want to know the longest gap in the hour, since if there were only two entries that were 58 minutes apart the unit could have been offline for 50 minutes.  (Or maybe that's not an issue to you?)
Avatar of Mi-Jack

ASKER

here's the range of possibilities:

1. _________|'''''''''''''''''''''''''''''''''''''''''''''''''''
    unit was OFF at the beginning, then went ON line

2. ''''''''''''''''''''''''''''''''''''''''|____________
    from ON to OFF

3.____________|''''''''''''''''''''|_______
    from OFF to ON and back to OFF

4. '''''''''''''''''''''''''''''|_________|'''''''''''''''''''''''''''''''
    ON to OFF and back to ON

5. _____________________________
    OFF - not in this table at all

6. ''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''
    ON for the whole hour

If the query had 4 key parameters: table name tname, previous hour table name prevtname, hour h, previous hour prevh,
then
then by combining the 2 tables and looking for h only and for prevh only in that joint table,
that would effectively smooth out all unit time discrepancies.

Then the next hour tname becomes prevtname, h becomes prevh etc.

I think this will work for all 6 cases.

What do you think?
Is that possible?
Avatar of Mi-Jack

ASKER

If tables are analyzed in pairs - 1&2, 2&3, 3&4 etc with  the results written into a  single table, then all that needs to be done is to filter out duplicates via a primary key in that table. Right?
We can do something like that.

Do you just want the report or would you want to preserve the data (i.e. inserted into a summary table)?

And this approach will require that the report use the time reported by each unit for comparison, though it can be adjusted to fit in the table's time period.
Avatar of Mi-Jack

ASKER

Preserving the data sounds good to me.
The idea is to this single table with pre-processed data, so that even the mentally challenged like me could figure out how to get a report out of it ;-)
Then let's try this.  :)

CREATE TABLE summary_data AS 
(
 SELECT radio_id, reporttime, loadtime
  FROM
  (
    SELECT DISTINCT I_radio_id as radio_id,
       timestamp(right (digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') reporttime,
      current_timestamp as loadtime
    FROM DBO.PositionRecord2012072410
    WHERE i_code=0
  ) t2
) DEFINITION ONLY;

Open in new window


WITH sd AS
(
 SELECT radio_id, reporttime, loadtime
  FROM
  (
    SELECT DISTINCT I_radio_id as radio_id,
       timestamp(right (digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') reporttime,
      current_timestamp loadtime
    FROM DBO.PositionRecord2012072410
    WHERE i_code=0
  ) t2
) 
INSERT INTO summary_data
SELECT * FROM sd;

Open in new window


The first query will create us a summary table. The second one will summarize a table and load it.  Note that we're capturing the summary load time for each table.  That allows us to later know which rows were loaded as a group.

Kent
Avatar of Mi-Jack

ASKER

getting an error from the second one, can't understand it:

ERROR: A character, token, or clause is invalid or missing.

DB2
SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=INSERT;i_code=0

 ) t2
)
;<from>, DRIVER=3.57.82
Error Code: -104
My Bad....

WITH sd (radio_id, reporttime, loadtime) 
AS
(
 SELECT radio_id, reporttime, loadtime
  FROM
  (
    SELECT DISTINCT I_radio_id as radio_id,
       timestamp(right (digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
        right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') reporttime,
      current_timestamp loadtime
    FROM DBO.PositionRecord2012072410
    WHERE i_code=0
  ) t2
) 
INSERT INTO summary_data
SELECT * FROM sd;

Open in new window

I think the insert statement should look more like
insert into ...
with sd ...
select ...

Open in new window

Avatar of Mi-Jack

ASKER

I got the same error ;(
Keeping it simple....


INSERT INTO summary_data
SELECT DISTINCT I_radio_id as radio_id,
  timestamp(right (digits(i_year+2000),4) || '-' || right('00'||digits(i_month),2) || '-' || right('00'||digits(i_day),2) || '-' ||
  right('00'||digits(i_hour),2) || '.' || right('00'||digits(i_minute),2) || '.' || '00.000000') reporttime,
  current_timestamp loadtime
FROM DBO.PositionRecord2012072410
WHERE i_code=0;
Avatar of Mi-Jack

ASKER

That resulted in 5965 rows
Avatar of Mi-Jack

ASKER

Looks like one row per unit per second:

1209      2012-07-24 10:57:00.0      2012-07-27 11:17:55.492
1209      2012-07-24 10:58:00.0      2012-07-27 11:17:55.492
1209      2012-07-24 10:59:00.0      2012-07-27 11:17:55.492
1209      2012-07-24 11:00:00.0      2012-07-27 11:17:55.492
1221      2012-07-24 10:00:00.0      2012-07-27 11:17:55.492
1221      2012-07-24 10:01:00.0      2012-07-27 11:17:55.492
1221      2012-07-24 10:02:00.0      2012-07-27 11:17:55.492
...
One row per minute.  Exactly what we want....
Avatar of Mi-Jack

ASKER

right, minute, of course, not second.
So this is like a preliminary table from which to populate the pre-processed report table?
If you can populate that table from consecutive source tables, we can then run the original query (first one that almost worked anyway) and have meaningful "online" "offline" flags as the time nears the start and/or end of the hour.
Avatar of Mi-Jack

ASKER

OK, that table now has 17614 rows from tables DBO.PositionRecord2012072410, 11 and 12.

Which query do you want me to run?
Start with this one.  We'll adjust it a little bit to ignore the extremes on the end, but let's see if it's the right start first.


WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id,
    FROM summary_date
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes)

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
        ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
       AND t1.unit is not NULL
) t0
WHERE timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

I had to modify the query alittle:

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id
    FROM pos_summary_data
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes)

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
        ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
       AND t1.unit is not NULL
) t0
WHERE timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

But it still gives me this error:

ERROR: An undefined column or parameter name was detected.

DB2
SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=TIME, DRIVER=3.57.82
Error
Code: -206
Avatar of Mi-Jack

ASKER

Here's the DDL for the POS_SUMMARY_DATA table:

CREATE TABLE POS_SUMMARY_DATA (
      RADIO_ID INTEGER NOT NULL,
      REPORTTIME TIMESTAMP NOT NULL,
      LOADTIME TIMESTAMP NOT NULL
);
SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id
    FROM pos_summary_data
  ) t2

There is no TIME column in the "table" you're selecting from, T2.
WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id, reporttime as time
    FROM summary_date
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE (t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes)

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
        ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
       AND t1.unit is not NULL
) t0
WHERE timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

That gave me 336 results:

1      STARTTIME      ENDTIME      RN      RN      6
1209      2012-07-24 13:00:00.0      null      181      null      Unit went offline
1221      2012-07-24 11:27:00.0      2012-07-24 12:24:00.0      85      86      *
1221      2012-07-24 13:01:00.0      null      121      null      Unit went offline
1226      2012-07-24 12:32:00.0      null      null      1      Unit came online
1226      2012-07-24 13:01:00.0      null      30      null      Unit went offline
1227      2012-07-24 10:35:00.0      2012-07-24 10:41:00.0      33      34      *
1227      2012-07-24 11:52:00.0      2012-07-24 11:56:00.0      103      104      *
1227      2012-07-24 12:26:00.0      2012-07-24 12:33:00.0      132      133      *
1227      2012-07-24 12:45:00.0      2012-07-24 12:49:00.0      145      146      *
1227      2012-07-24 13:00:00.0      null      157      null      Unit went offline
1228      2012-07-24 10:50:00.0      2012-07-24 10:54:00.0      51      52      *
1228      2012-07-24 10:59:00.0      2012-07-24 11:32:00.0      57      58      *
1228      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      61      62      *
1228      2012-07-24 12:01:00.0      2012-07-24 12:05:00.0      84      85      *
1228      2012-07-24 12:16:00.0      2012-07-24 12:22:00.0      96      97      *
1228      2012-07-24 13:01:00.0      null      136      null      Unit went offline
1230      2012-07-24 12:46:00.0      null      167      null      Unit went offline
1231      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      94      95      *
1231      2012-07-24 11:40:00.0      2012-07-24 11:49:00.0      96      97      *
1231      2012-07-24 11:53:00.0      2012-07-24 12:02:00.0      101      102      *
1231      2012-07-24 12:07:00.0      2012-07-24 12:14:00.0      106      107      *
1231      2012-07-24 12:15:00.0      2012-07-24 12:26:00.0      108      109      *
1231      2012-07-24 13:01:00.0      null      134      null      Unit went offline
1233      2012-07-24 13:01:00.0      null      173      null      Unit went offline
1234      2012-07-24 10:23:00.0      2012-07-24 10:27:00.0      22      23      *
1234      2012-07-24 11:51:00.0      2012-07-24 11:56:00.0      105      106      *
1234      2012-07-24 13:01:00.0      null      168      null      Unit went offline
1235      2012-07-24 11:57:00.0      2012-07-24 12:04:00.0      118      119      *
1235      2012-07-24 12:58:00.0      null      173      null      Unit went offline
1236      2012-07-24 13:01:00.0      null      174      null      Unit went offline
1237      2012-07-24 13:01:00.0      null      181      null      Unit went offline
1238      2012-07-24 10:31:00.0      2012-07-24 10:37:00.0      32      33      *
1238      2012-07-24 10:46:00.0      2012-07-24 10:50:00.0      42      43      *
1238      2012-07-24 11:29:00.0      2012-07-24 12:21:00.0      81      82      *
1238      2012-07-24 13:00:00.0      null      119      null      Unit went offline
1239      2012-07-24 12:39:00.0      null      null      1      Unit came online
1239      2012-07-24 13:01:00.0      null      23      null      Unit went offline
1240      2012-07-24 12:22:00.0      null      null      1      Unit came online
1240      2012-07-24 13:01:00.0      null      39      null      Unit went offline
1241      2012-07-24 12:31:00.0      null      null      1      Unit came online
1241      2012-07-24 13:01:00.0      null      31      null      Unit went offline
1242      2012-07-24 11:46:00.0      2012-07-24 12:10:00.0      106      107      *
1242      2012-07-24 13:01:00.0      null      158      null      Unit went offline
1244      2012-07-24 11:06:00.0      2012-07-24 11:11:00.0      66      67      *
1244      2012-07-24 13:01:00.0      null      168      null      Unit went offline
1245      2012-07-24 13:01:00.0      null      182      null      Unit went offline
1246      2012-07-24 12:19:00.0      2012-07-24 12:24:00.0      138      139      *
1246      2012-07-24 12:48:00.0      null      160      null      Unit went offline
1247      2012-07-24 10:08:00.0      2012-07-24 12:31:00.0      9      10      *
1247      2012-07-24 13:01:00.0      null      40      null      Unit went offline
1248      2012-07-24 12:31:00.0      null      null      1      Unit came online
1248      2012-07-24 13:01:00.0      null      31      null      Unit went offline
1249      2012-07-24 10:46:00.0      2012-07-24 12:24:00.0      47      48      *
1249      2012-07-24 13:01:00.0      null      85      null      Unit went offline
1251      2012-07-24 10:02:00.0      2012-07-24 10:06:00.0      3      4      *
1251      2012-07-24 10:26:00.0      2012-07-24 10:31:00.0      22      23      *
1251      2012-07-24 10:47:00.0      2012-07-24 10:52:00.0      39      40      *
1251      2012-07-24 12:11:00.0      null      117      null      Unit went offline
1252      2012-07-24 12:22:00.0      2012-07-24 12:26:00.0      134      135      *
1252      2012-07-24 12:38:00.0      2012-07-24 12:48:00.0      147      148      *
1252      2012-07-24 13:01:00.0      null      161      null      Unit went offline
1253      2012-07-24 12:32:00.0      null      null      1      Unit came online
1253      2012-07-24 13:01:00.0      null      30      null      Unit went offline
1254      2012-07-24 10:06:00.0      null      null      1      Unit came online
1254      2012-07-24 10:10:00.0      2012-07-24 12:39:00.0      5      6      *
1254      2012-07-24 13:01:00.0      null      28      null      Unit went offline
1255      2012-07-24 13:01:00.0      null      182      null      Unit went offline
1257      2012-07-24 12:20:00.0      2012-07-24 12:24:00.0      138      139      *
1257      2012-07-24 13:01:00.0      null      176      null      Unit went offline
1258      2012-07-24 10:24:00.0      2012-07-24 10:28:00.0      25      26      *
1258      2012-07-24 13:00:00.0      null      171      null      Unit went offline
1260      2012-07-24 10:36:00.0      2012-07-24 10:41:00.0      36      37      *
1260      2012-07-24 12:10:00.0      2012-07-24 12:14:00.0      120      121      *
1260      2012-07-24 13:01:00.0      null      166      null      Unit went offline
1261      2012-07-24 11:37:00.0      2012-07-24 11:41:00.0      94      95      *
1261      2012-07-24 12:53:00.0      2012-07-24 12:57:00.0      167      168      *
1261      2012-07-24 13:01:00.0      null      172      null      Unit went offline
1262      2012-07-24 10:45:00.0      2012-07-24 10:49:00.0      46      47      *
1262      2012-07-24 13:01:00.0      null      179      null      Unit went offline
1264      2012-07-24 13:01:00.0      null      181      null      Unit went offline
1265      2012-07-24 10:14:00.0      2012-07-24 10:29:00.0      15      16      *
1265      2012-07-24 13:01:00.0      null      160      null      Unit went offline
1267      2012-07-24 11:28:00.0      2012-07-24 12:12:00.0      84      85      *
1267      2012-07-24 12:13:00.0      null      86      null      Unit went offline
1268      2012-07-24 13:01:00.0      null      181      null      Unit went offline
1269      2012-07-24 13:01:00.0      null      182      null      Unit went offline
1270      2012-07-24 10:20:00.0      2012-07-24 10:32:00.0      21      22      *
1270      2012-07-24 13:01:00.0      null      158      null      Unit went offline
1271      2012-07-24 12:06:00.0      null      126      null      Unit went offline
1272      2012-07-24 10:17:00.0      2012-07-24 10:21:00.0      18      19      *
1272      2012-07-24 11:22:00.0      2012-07-24 11:26:00.0      79      80      *
1272      2012-07-24 13:00:00.0      null      173      null      Unit went offline
1273      2012-07-24 11:56:00.0      2012-07-24 12:05:00.0      114      115      *
1273      2012-07-24 13:01:00.0      null      171      null      Unit went offline
1274      2012-07-24 10:14:00.0      2012-07-24 10:26:00.0      15      16      *
1274      2012-07-24 10:34:00.0      2012-07-24 10:38:00.0      24      25      *
1274      2012-07-24 10:53:00.0      2012-07-24 11:03:00.0      39      40      *
1274      2012-07-24 11:14:00.0      2012-07-24 11:19:00.0      51      52      *
1274      2012-07-24 11:37:00.0      2012-07-24 11:43:00.0      69      70      *
1274      2012-07-24 12:26:00.0      2012-07-24 12:30:00.0      113      114      *
1274      2012-07-24 13:01:00.0      null      145      null      Unit went offline
1276      2012-07-24 11:24:00.0      2012-07-24 11:31:00.0      81      82      *
1276      2012-07-24 13:00:00.0      null      169      null      Unit went offline
1277      2012-07-24 10:13:00.0      null      null      1      Unit came online
1277      2012-07-24 10:54:00.0      2012-07-24 11:02:00.0      39      40      *
1277      2012-07-24 11:33:00.0      2012-07-24 11:55:00.0      70      71      *
1277      2012-07-24 13:01:00.0      null      133      null      Unit went offline
1280      2012-07-24 12:31:00.0      null      null      1      Unit came online
1280      2012-07-24 13:01:00.0      null      31      null      Unit went offline
1282      2012-07-24 10:44:00.0      2012-07-24 10:49:00.0      44      45      *
1282      2012-07-24 10:50:00.0      2012-07-24 10:55:00.0      46      47      *
1282      2012-07-24 13:01:00.0      null      167      null      Unit went offline
1283      2012-07-24 11:05:00.0      2012-07-24 11:10:00.0      64      65      *
1283      2012-07-24 13:01:00.0      null      167      null      Unit went offline
1284      2012-07-24 10:58:00.0      2012-07-24 11:02:00.0      59      60      *
1284      2012-07-24 11:34:00.0      2012-07-24 11:39:00.0      92      93      *
1284      2012-07-24 11:39:00.0      2012-07-24 11:45:00.0      93      94      *
1284      2012-07-24 12:36:00.0      2012-07-24 12:40:00.0      143      144      *
1284      2012-07-24 12:50:00.0      2012-07-24 12:56:00.0      154      155      *
1284      2012-07-24 13:01:00.0      null      159      null      Unit went offline
1285      2012-07-24 11:37:00.0      2012-07-24 11:49:00.0      96      97      *
1285      2012-07-24 12:51:00.0      2012-07-24 12:58:00.0      156      157      *
1285      2012-07-24 13:00:00.0      null      159      null      Unit went offline
1286      2012-07-24 10:54:00.0      2012-07-24 10:59:00.0      51      52      *
1286      2012-07-24 12:09:00.0      2012-07-24 12:13:00.0      120      121      *
1286      2012-07-24 13:01:00.0      null      169      null      Unit went offline
1287      2012-07-24 10:36:00.0      2012-07-24 10:40:00.0      35      36      *
1287      2012-07-24 13:01:00.0      null      175      null      Unit went offline
1289      2012-07-24 13:01:00.0      null      178      null      Unit went offline
1291      2012-07-24 10:36:00.0      2012-07-24 10:48:00.0      35      36      *
1291      2012-07-24 13:01:00.0      null      166      null      Unit went offline
1292      2012-07-24 13:01:00.0      null      170      null      Unit went offline
1293      2012-07-24 10:27:00.0      2012-07-24 10:35:00.0      26      27      *
1293      2012-07-24 11:57:00.0      2012-07-24 12:02:00.0      107      108      *
1293      2012-07-24 13:00:00.0      null      163      null      Unit went offline
1294      2012-07-24 11:26:00.0      2012-07-24 11:52:00.0      83      84      *
1294      2012-07-24 12:20:00.0      2012-07-24 12:47:00.0      110      111      *
1294      2012-07-24 13:00:00.0      null      124      null      Unit went offline
1296      2012-07-24 11:09:00.0      2012-07-24 11:14:00.0      70      71      *
1296      2012-07-24 13:00:00.0      null      175      null      Unit went offline
1297      2012-07-24 10:26:00.0      2012-07-24 10:31:00.0      27      28      *
1297      2012-07-24 10:57:00.0      2012-07-24 11:01:00.0      52      53      *
1297      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      73      74      *
1297      2012-07-24 13:01:00.0      null      169      null      Unit went offline
1298      2012-07-24 11:12:00.0      2012-07-24 11:18:00.0      71      72      *
1298      2012-07-24 12:02:00.0      2012-07-24 12:06:00.0      116      117      *
1298      2012-07-24 12:31:00.0      2012-07-24 12:36:00.0      141      142      *
1298      2012-07-24 12:44:00.0      2012-07-24 12:53:00.0      150      151      *
1298      2012-07-24 12:55:00.0      2012-07-24 13:00:00.0      153      154      *
1298      2012-07-24 13:01:00.0      null      155      null      Unit went offline
1299      2012-07-24 11:12:00.0      null      null      1      Unit came online
1299      2012-07-24 11:23:00.0      2012-07-24 11:27:00.0      12      13      *
1299      2012-07-24 13:00:00.0      null      102      null      Unit went offline
1301      2012-07-24 10:11:00.0      2012-07-24 10:16:00.0      12      13      *
1301      2012-07-24 13:01:00.0      null      170      null      Unit went offline
1303      2012-07-24 12:41:00.0      2012-07-24 12:50:00.0      159      160      *
1303      2012-07-24 13:01:00.0      null      171      null      Unit went offline
1305      2012-07-24 10:03:00.0      2012-07-24 10:08:00.0      4      5      *
1305      2012-07-24 11:18:00.0      2012-07-24 11:22:00.0      72      73      *
1305      2012-07-24 11:42:00.0      2012-07-24 11:46:00.0      93      94      *
1305      2012-07-24 12:05:00.0      2012-07-24 12:12:00.0      113      114      *
1305      2012-07-24 13:01:00.0      null      159      null      Unit went offline
1306      2012-07-24 10:12:00.0      null      null      1      Unit came online
1306      2012-07-24 13:01:00.0      null      168      null      Unit went offline
1307      2012-07-24 11:06:00.0      2012-07-24 11:15:00.0      66      67      *
1307      2012-07-24 13:01:00.0      null      173      null      Unit went offline
1308      2012-07-24 10:54:00.0      2012-07-24 11:00:00.0      53      54      *
1308      2012-07-24 12:29:00.0      2012-07-24 12:36:00.0      138      139      *
1308      2012-07-24 13:01:00.0      null      163      null      Unit went offline
1309      2012-07-24 10:21:00.0      2012-07-24 10:42:00.0      21      22      *
1309      2012-07-24 10:57:00.0      2012-07-24 11:05:00.0      37      38      *
1309      2012-07-24 12:21:00.0      null      114      null      Unit went offline
1310      2012-07-24 12:29:00.0      2012-07-24 12:37:00.0      148      149      *
1310      2012-07-24 12:42:00.0      2012-07-24 12:51:00.0      154      155      *
1310      2012-07-24 12:55:00.0      null      159      null      Unit went offline
1311      2012-07-24 10:03:00.0      2012-07-24 10:09:00.0      4      5      *
1311      2012-07-24 10:42:00.0      2012-07-24 10:46:00.0      38      39      *
1311      2012-07-24 10:49:00.0      2012-07-24 10:55:00.0      42      43      *
1311      2012-07-24 10:56:00.0      2012-07-24 11:12:00.0      44      45      *
1311      2012-07-24 12:43:00.0      2012-07-24 12:50:00.0      133      134      *
1311      2012-07-24 13:01:00.0      null      145      null      Unit went offline
1313      2012-07-24 10:03:00.0      null      null      1      Unit came online
1313      2012-07-24 10:06:00.0      2012-07-24 10:12:00.0      4      5      *
1313      2012-07-24 10:20:00.0      2012-07-24 10:24:00.0      13      14      *
1313      2012-07-24 12:43:00.0      null      149      null      Unit went offline
1315      2012-07-24 10:07:00.0      2012-07-24 10:12:00.0      8      9      *
1315      2012-07-24 11:49:00.0      2012-07-24 12:08:00.0      105      106      *
1315      2012-07-24 12:53:00.0      2012-07-24 13:00:00.0      149      150      *
1315      2012-07-24 13:01:00.0      null      151      null      Unit went offline
1318      2012-07-24 10:48:00.0      2012-07-24 10:53:00.0      48      49      *
1318      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      77      78      *
1318      2012-07-24 13:01:00.0      null      171      null      Unit went offline
1319      2012-07-24 10:16:00.0      2012-07-24 10:20:00.0      17      18      *
1319      2012-07-24 10:38:00.0      2012-07-24 10:43:00.0      36      37      *
1319      2012-07-24 12:35:00.0      2012-07-24 12:39:00.0      144      145      *
1319      2012-07-24 12:59:00.0      null      165      null      Unit went offline
1320      2012-07-24 10:10:00.0      2012-07-24 10:17:00.0      11      12      *
1320      2012-07-24 10:18:00.0      2012-07-24 10:22:00.0      13      14      *
1320      2012-07-24 11:07:00.0      2012-07-24 11:11:00.0      59      60      *
1320      2012-07-24 11:26:00.0      2012-07-24 11:33:00.0      74      75      *
1320      2012-07-24 13:01:00.0      null      161      null      Unit went offline
1321      2012-07-24 10:05:00.0      null      null      1      Unit came online
1321      2012-07-24 10:57:00.0      2012-07-24 11:05:00.0      52      53      *
1321      2012-07-24 11:20:00.0      2012-07-24 11:29:00.0      67      68      *
1321      2012-07-24 11:51:00.0      2012-07-24 12:09:00.0      90      91      *
1321      2012-07-24 12:21:00.0      2012-07-24 12:27:00.0      102      103      *
1321      2012-07-24 13:01:00.0      null      137      null      Unit went offline
1322      2012-07-24 13:00:00.0      null      175      null      Unit went offline
1323      2012-07-24 10:59:00.0      2012-07-24 11:03:00.0      54      55      *
1323      2012-07-24 12:58:00.0      null      166      null      Unit went offline
1324      2012-07-24 10:12:00.0      2012-07-24 10:17:00.0      13      14      *
1324      2012-07-24 12:11:00.0      2012-07-24 12:18:00.0      127      128      *
1324      2012-07-24 13:01:00.0      null      170      null      Unit went offline
1325      2012-07-24 11:05:00.0      2012-07-24 11:14:00.0      64      65      *
1325      2012-07-24 11:24:00.0      2012-07-24 11:29:00.0      74      75      *
1325      2012-07-24 11:41:00.0      2012-07-24 11:47:00.0      87      88      *
1325      2012-07-24 12:59:00.0      null      157      null      Unit went offline
1326      2012-07-24 10:28:00.0      2012-07-24 10:42:00.0      29      30      *
1326      2012-07-24 11:27:00.0      2012-07-24 12:22:00.0      75      76      *
1326      2012-07-24 13:01:00.0      null      115      null      Unit went offline
1328      2012-07-24 10:08:00.0      2012-07-24 10:24:00.0      1      2      *
1328      2012-07-24 10:08:00.0      null      null      1      Unit came online
1328      2012-07-24 11:54:00.0      2012-07-24 12:08:00.0      90      91      *
1328      2012-07-24 13:00:00.0      null      143      null      Unit went offline
1329      2012-07-24 13:01:00.0      null      175      null      Unit went offline
1330      2012-07-24 12:21:00.0      null      null      1      Unit came online
1330      2012-07-24 13:01:00.0      null      41      null      Unit went offline
1331      2012-07-24 11:26:00.0      2012-07-24 11:30:00.0      86      87      *
1331      2012-07-24 12:06:00.0      2012-07-24 12:17:00.0      121      122      *
1331      2012-07-24 12:26:00.0      2012-07-24 12:35:00.0      131      132      *
1331      2012-07-24 13:01:00.0      null      155      null      Unit went offline
1332      2012-07-24 10:39:00.0      2012-07-24 10:47:00.0      39      40      *
1332      2012-07-24 13:01:00.0      null      168      null      Unit went offline
1333      2012-07-24 13:01:00.0      null      178      null      Unit went offline
1334      2012-07-24 10:56:00.0      2012-07-24 11:00:00.0      55      56      *
1334      2012-07-24 11:52:00.0      2012-07-24 11:57:00.0      107      108      *
1334      2012-07-24 12:52:00.0      2012-07-24 12:56:00.0      163      164      *
1334      2012-07-24 13:01:00.0      null      169      null      Unit went offline
1335      2012-07-24 10:31:00.0      2012-07-24 10:36:00.0      32      33      *
1335      2012-07-24 11:26:00.0      2012-07-24 11:32:00.0      81      82      *
1335      2012-07-24 13:01:00.0      null      170      null      Unit went offline
1336      2012-07-24 11:03:00.0      null      null      1      Unit came online
1336      2012-07-24 11:06:00.0      null      4      null      Unit went offline
1337      2012-07-24 13:01:00.0      null      178      null      Unit went offline
1338      2012-07-24 13:31:00.0      null      null      1      Unit came online
1339      2012-07-24 10:18:00.0      null      null      1      Unit came online
1339      2012-07-24 10:19:00.0      2012-07-24 12:22:00.0      2      3      *
1339      2012-07-24 13:01:00.0      null      42      null      Unit went offline
1340      2012-07-24 11:11:00.0      2012-07-24 11:15:00.0      72      73      *
1340      2012-07-24 13:01:00.0      null      178      null      Unit went offline
1341      2012-07-24 13:01:00.0      null      180      null      Unit went offline
1342      2012-07-24 12:32:00.0      null      null      1      Unit came online
1342      2012-07-24 13:01:00.0      null      30      null      Unit went offline
1343      2012-07-24 11:55:00.0      2012-07-24 12:00:00.0      115      116      *
1343      2012-07-24 13:01:00.0      null      177      null      Unit went offline
1344      2012-07-24 11:30:00.0      2012-07-24 11:38:00.0      91      92      *
1344      2012-07-24 13:01:00.0      null      175      null      Unit went offline
1350      2012-07-24 13:01:00.0      null      180      null      Unit went offline
1351      2012-07-24 11:00:00.0      2012-07-24 11:04:00.0      60      61      *
1351      2012-07-24 11:16:00.0      2012-07-24 11:22:00.0      73      74      *
1351      2012-07-24 12:30:00.0      2012-07-24 12:43:00.0      141      142      *
1351      2012-07-24 13:01:00.0      null      160      null      Unit went offline
1352      2012-07-24 12:38:00.0      null      null      1      Unit came online
1352      2012-07-24 13:01:00.0      null      24      null      Unit went offline
1353      2012-07-24 13:01:00.0      null      182      null      Unit went offline
1354      2012-07-24 12:02:00.0      2012-07-24 12:07:00.0      118      119      *
1354      2012-07-24 12:59:00.0      null      168      null      Unit went offline
1355      2012-07-24 12:09:00.0      2012-07-24 12:13:00.0      128      129      *
1355      2012-07-24 12:24:00.0      2012-07-24 12:40:00.0      140      141      *
1355      2012-07-24 13:01:00.0      null      162      null      Unit went offline
1356      2012-07-24 12:31:00.0      null      null      1      Unit came online
1356      2012-07-24 13:01:00.0      null      31      null      Unit went offline
1357      2012-07-24 11:45:00.0      2012-07-24 11:54:00.0      104      105      *
1357      2012-07-24 12:03:00.0      2012-07-24 12:08:00.0      114      115      *
1357      2012-07-24 13:00:00.0      null      166      null      Unit went offline
1359      2012-07-24 13:01:00.0      null      173      null      Unit went offline
1360      2012-07-24 12:18:00.0      null      null      1      Unit came online
1360      2012-07-24 12:37:00.0      null      18      null      Unit went offline
1364      2012-07-24 11:03:00.0      null      null      1      Unit came online
1364      2012-07-24 12:21:00.0      null      79      null      Unit went offline
1366      2012-07-24 10:02:00.0      2012-07-24 10:12:00.0      3      4      *
1366      2012-07-24 10:22:00.0      2012-07-24 10:29:00.0      14      15      *
1366      2012-07-24 10:35:00.0      2012-07-24 10:41:00.0      21      22      *
1366      2012-07-24 10:45:00.0      2012-07-24 10:50:00.0      26      27      *
1366      2012-07-24 12:13:00.0      2012-07-24 12:31:00.0      108      109      *
1366      2012-07-24 13:01:00.0      null      139      null      Unit went offline
1367      2012-07-24 12:28:00.0      2012-07-24 12:40:00.0      141      142      *
1367      2012-07-24 12:48:00.0      2012-07-24 12:56:00.0      148      149      *
1367      2012-07-24 13:00:00.0      null      153      null      Unit went offline
1368      2012-07-24 13:01:00.0      null      174      null      Unit went offline
1371      2012-07-24 10:03:00.0      null      null      1      Unit came online
1371      2012-07-24 10:37:00.0      2012-07-24 10:41:00.0      35      36      *
1371      2012-07-24 11:14:00.0      2012-07-24 11:23:00.0      67      68      *
1371      2012-07-24 13:01:00.0      null      166      null      Unit went offline
1375      2012-07-24 10:10:00.0      2012-07-24 10:15:00.0      11      12      *
1375      2012-07-24 10:40:00.0      2012-07-24 10:46:00.0      35      36      *
1375      2012-07-24 11:17:00.0      2012-07-24 11:21:00.0      66      67      *
1375      2012-07-24 11:47:00.0      2012-07-24 11:53:00.0      92      93      *
1375      2012-07-24 12:40:00.0      2012-07-24 12:44:00.0      140      141      *
1375      2012-07-24 13:00:00.0      null      157      null      Unit went offline
1377      2012-07-24 10:10:00.0      2012-07-24 10:14:00.0      11      12      *
1377      2012-07-24 12:22:00.0      2012-07-24 12:26:00.0      135      136      *
1377      2012-07-24 12:56:00.0      null      166      null      Unit went offline
1378      2012-07-24 11:26:00.0      2012-07-24 11:31:00.0      83      84      *
1378      2012-07-24 12:11:00.0      2012-07-24 12:38:00.0      123      124      *
1378      2012-07-24 12:38:00.0      2012-07-24 12:43:00.0      124      125      *
1378      2012-07-24 12:44:00.0      2012-07-24 12:48:00.0      126      127      *
1378      2012-07-24 12:52:00.0      null      131      null      Unit went offline
1379      2012-07-24 10:10:00.0      2012-07-24 10:14:00.0      11      12      *
1379      2012-07-24 11:06:00.0      2012-07-24 11:14:00.0      64      65      *
1379      2012-07-24 12:02:00.0      2012-07-24 12:06:00.0      113      114      *
1379      2012-07-24 12:06:00.0      2012-07-24 12:19:00.0      114      115      *
1379      2012-07-24 12:43:00.0      2012-07-24 12:50:00.0      138      139      *
1379      2012-07-24 13:01:00.0      null      150      null      Unit went offline
1380      2012-07-24 10:03:00.0      null      null      1      Unit came online
1380      2012-07-24 13:01:00.0      null      173      null      Unit went offline
1382      2012-07-24 10:08:00.0      2012-07-24 10:14:00.0      9      10      *
1382      2012-07-24 12:37:00.0      2012-07-24 12:42:00.0      146      147      *
1382      2012-07-24 13:01:00.0      null      166      null      Unit went offline
1383      2012-07-24 10:56:00.0      null      57      null      Unit went offline
1384      2012-07-24 11:40:00.0      2012-07-24 11:50:00.0      98      99      *
1384      2012-07-24 12:30:00.0      2012-07-24 12:34:00.0      139      140      *
1384      2012-07-24 13:01:00.0      null      167      null      Unit went offline
1385      2012-07-24 10:17:00.0      2012-07-24 10:23:00.0      18      19      *
1385      2012-07-24 11:19:00.0      2012-07-24 11:45:00.0      75      76      *
1385      2012-07-24 13:00:00.0      null      151      null      Unit went offline
1386      2012-07-24 10:05:00.0      2012-07-24 10:11:00.0      6      7      *
1386      2012-07-24 12:11:00.0      2012-07-24 12:15:00.0      121      122      *
1386      2012-07-24 12:42:00.0      2012-07-24 12:50:00.0      149      150      *
1386      2012-07-24 13:01:00.0      null      160      null      Unit went offline
1387      2012-07-24 10:11:00.0      null      null      1      Unit came online
1387      2012-07-24 10:38:00.0      2012-07-24 10:54:00.0      28      29      *
1387      2012-07-24 11:23:00.0      null      58      null      Unit went offline
1388      2012-07-24 10:38:00.0      2012-07-24 10:43:00.0      35      36      *
1388      2012-07-24 11:20:00.0      2012-07-24 11:25:00.0      73      74      *
1388      2012-07-24 12:50:00.0      null      159      null      Unit went offline
Avatar of Mi-Jack

ASKER

Wow, I'm starting to think that it will actually happen, that this mountain of raw sh***y data can be parsed.
Thank you.
I have to go - have a good weekend.
Ok.  Let's try a really silly filter and just select the rows in the "middle" hour.  It looks like You've got time for the 10, 11, and 12 o'clock periods, so we'll select 11:00 - 12:00.

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id, reporttime as time
    FROM summary_date
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between '2012-07-24-11:00:00.000000' and '2012-07-24-12:00:00.000000'
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
        ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
       AND t1.unit is not NULL
) t0
WHERE starttime between '2012-07-24-11:00:00.000000' and '2012-07-24-12:00:00.000000'
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
>> Wow, I'm starting to think that it will actually happen, that this mountain of raw sh***y data can be parsed.

My background is applied mathematics.  Tell me what you want it to say and I'll come up with a formula that gets you that answer.  ;-)

Seriously, I've see a lot worse data!
Avatar of Mi-Jack

ASKER

I got really lucky, then (which I already knew). This is a great learning experience, too.
Avatar of Mi-Jack

ASKER

The query above runs with an error:

ERROR: An invalid datetime format was detected; that is, an invalid
string representation or value was specified.

DB2 SQL
Error: SQLCODE=-180, SQLSTATE=22007, SQLERRMC=null, DRIVER=3.57.82
Error
Code: -180
I'm thinking that DB2 isn't automatically recasting the string to a timestamp.

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT I_radio_id, reporttime as time
    FROM summary_date
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11:00:00.000000') and timestamp('2012-07-24-12:00:00.000000')
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
        ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
       AND t1.unit is not NULL
) t0
WHERE starttime between timestamp('2012-07-24-11:00:00.000000') and timestamp('2012-07-24-12:00:00.000000')
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

My version of Db2 doesn't accept this:
'2012-07-24-11:00:00.000000'
as a valid format, but this: '2012-07-24-11.00.00.000000'

Here's the modified query:

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
      SELECT coalesce (t0.unit, t1.unit), t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
      FROM minute_data t0
      FULL JOIN minute_data t1
        ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE t0.unit is not NULL
       AND t1.unit is not NULL
) t0
WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

65 rows returned:

1      STARTTIME      ENDTIME      RN      RN      6
1221      2012-07-24 11:27:00.0      2012-07-24 12:24:00.0      85      86      *
1227      2012-07-24 11:52:00.0      2012-07-24 11:56:00.0      103      104      *
1228      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      61      62      *
1231      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      94      95      *
1231      2012-07-24 11:40:00.0      2012-07-24 11:49:00.0      96      97      *
1231      2012-07-24 11:53:00.0      2012-07-24 12:02:00.0      101      102      *
1234      2012-07-24 11:51:00.0      2012-07-24 11:56:00.0      105      106      *
1235      2012-07-24 11:57:00.0      2012-07-24 12:04:00.0      118      119      *
1238      2012-07-24 11:29:00.0      2012-07-24 12:21:00.0      81      82      *
1242      2012-07-24 11:46:00.0      2012-07-24 12:10:00.0      106      107      *
1244      2012-07-24 11:06:00.0      2012-07-24 11:11:00.0      66      67      *
1261      2012-07-24 11:37:00.0      2012-07-24 11:41:00.0      94      95      *
1267      2012-07-24 11:28:00.0      2012-07-24 12:12:00.0      84      85      *
1272      2012-07-24 11:22:00.0      2012-07-24 11:26:00.0      79      80      *
1273      2012-07-24 11:56:00.0      2012-07-24 12:05:00.0      114      115      *
1274      2012-07-24 11:14:00.0      2012-07-24 11:19:00.0      51      52      *
1274      2012-07-24 11:37:00.0      2012-07-24 11:43:00.0      69      70      *
1276      2012-07-24 11:24:00.0      2012-07-24 11:31:00.0      81      82      *
1277      2012-07-24 11:33:00.0      2012-07-24 11:55:00.0      70      71      *
1283      2012-07-24 11:05:00.0      2012-07-24 11:10:00.0      64      65      *
1284      2012-07-24 11:34:00.0      2012-07-24 11:39:00.0      92      93      *
1284      2012-07-24 11:39:00.0      2012-07-24 11:45:00.0      93      94      *
1285      2012-07-24 11:37:00.0      2012-07-24 11:49:00.0      96      97      *
1293      2012-07-24 11:57:00.0      2012-07-24 12:02:00.0      107      108      *
1294      2012-07-24 11:26:00.0      2012-07-24 11:52:00.0      83      84      *
1296      2012-07-24 11:09:00.0      2012-07-24 11:14:00.0      70      71      *
1297      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      73      74      *
1298      2012-07-24 11:12:00.0      2012-07-24 11:18:00.0      71      72      *
1299      2012-07-24 11:12:00.0      null      null      1      Unit came online
1299      2012-07-24 11:23:00.0      2012-07-24 11:27:00.0      12      13      *
1305      2012-07-24 11:18:00.0      2012-07-24 11:22:00.0      72      73      *
1305      2012-07-24 11:42:00.0      2012-07-24 11:46:00.0      93      94      *
1307      2012-07-24 11:06:00.0      2012-07-24 11:15:00.0      66      67      *
1315      2012-07-24 11:49:00.0      2012-07-24 12:08:00.0      105      106      *
1318      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      77      78      *
1320      2012-07-24 11:07:00.0      2012-07-24 11:11:00.0      59      60      *
1320      2012-07-24 11:26:00.0      2012-07-24 11:33:00.0      74      75      *
1321      2012-07-24 11:20:00.0      2012-07-24 11:29:00.0      67      68      *
1321      2012-07-24 11:51:00.0      2012-07-24 12:09:00.0      90      91      *
1325      2012-07-24 11:05:00.0      2012-07-24 11:14:00.0      64      65      *
1325      2012-07-24 11:24:00.0      2012-07-24 11:29:00.0      74      75      *
1325      2012-07-24 11:41:00.0      2012-07-24 11:47:00.0      87      88      *
1326      2012-07-24 11:27:00.0      2012-07-24 12:22:00.0      75      76      *
1328      2012-07-24 11:54:00.0      2012-07-24 12:08:00.0      90      91      *
1331      2012-07-24 11:26:00.0      2012-07-24 11:30:00.0      86      87      *
1334      2012-07-24 11:52:00.0      2012-07-24 11:57:00.0      107      108      *
1335      2012-07-24 11:26:00.0      2012-07-24 11:32:00.0      81      82      *
1336      2012-07-24 11:03:00.0      null      null      1      Unit came online
1336      2012-07-24 11:06:00.0      null      4      null      Unit went offline
1340      2012-07-24 11:11:00.0      2012-07-24 11:15:00.0      72      73      *
1343      2012-07-24 11:55:00.0      2012-07-24 12:00:00.0      115      116      *
1344      2012-07-24 11:30:00.0      2012-07-24 11:38:00.0      91      92      *
1351      2012-07-24 11:00:00.0      2012-07-24 11:04:00.0      60      61      *
1351      2012-07-24 11:16:00.0      2012-07-24 11:22:00.0      73      74      *
1357      2012-07-24 11:45:00.0      2012-07-24 11:54:00.0      104      105      *
1364      2012-07-24 11:03:00.0      null      null      1      Unit came online
1371      2012-07-24 11:14:00.0      2012-07-24 11:23:00.0      67      68      *
1375      2012-07-24 11:17:00.0      2012-07-24 11:21:00.0      66      67      *
1375      2012-07-24 11:47:00.0      2012-07-24 11:53:00.0      92      93      *
1378      2012-07-24 11:26:00.0      2012-07-24 11:31:00.0      83      84      *
1379      2012-07-24 11:06:00.0      2012-07-24 11:14:00.0      64      65      *
1384      2012-07-24 11:40:00.0      2012-07-24 11:50:00.0      98      99      *
1385      2012-07-24 11:19:00.0      2012-07-24 11:45:00.0      75      76      *
1387      2012-07-24 11:23:00.0      null      58      null      Unit went offline
1388      2012-07-24 11:20:00.0      2012-07-24 11:25:00.0      73      74      *
Hey!  That's great progress.  When we can "window" our search from data that includes the adjacent hours, the results start to make sense.  Let's add a column to get the time delta that's producing all of the '*' results.  Since those are units that went more than 3 minutes between reporting in, it might be that changing the 3 minutes to 5 or 8 or 10 could give us 15 or 20 lines that we can actually use.

My version of DB2 doesn't accept the ':' character in a timestamp either.  But both of our systems SHOW a datetime with the character.  (grrrr......)  :)

Oh, and I suggest that the last change we make will be to put the start time into a variable in the CTE so you only have to edit it in one place.


WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
)
SELECT coalesce (t0.unit, t1.unit), '-' as Delta, 
  coalesce (t0.time, t1.time) starttime, NULL as endtime, t0.rn, t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
  SELECT coalesce (t0.unit, t1.unit), 
    cast (timestampdiff (4, char(endtime - starttime)) as varchar(4)) Delta,
    t0.time starttime, t1.time endtime, t0.rn, t1.rn, '*'
  FROM minute_data t0
  FULL JOIN minute_data t1
    ON t0.unit = t1.unit
   AND t0.rn = t1.rn - 1
  WHERE t0.unit is not NULL
    AND t1.unit is not NULL
) t0
WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2; 

Open in new window

Avatar of Mi-Jack

ASKER

Can't figure this one out, getting the error:

ERROR: An undefined column or parameter name was detected.

DB2
SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=ENDTIME,
DRIVER=3.57.82
Error Code: -206
Hmmm...

The only thing that we really did was insert another column.

Try this one:

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
)
SELECT 
  coalesce (t0.unit, t1.unit), 
  cast ('-' as varchar(4)) as Delta, 
  coalesce (t0.time, t1.time) starttime, NULL as endtime, 
  t0.rn, 
  t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
  SELECT 
    coalesce (t0.unit, t1.unit), 
    cast (timestampdiff (4, char(endtime - starttime)) as varchar(4)) as Delta,
    t0.time starttime, 
    t1.time endtime, 
    t0.rn, 
    t1.rn, 
    '*'
  FROM minute_data t0
  FULL JOIN minute_data t1
    ON t0.unit = t1.unit
   AND t0.rn = t1.rn - 1
  WHERE t0.unit is not NULL
    AND t1.unit is not NULL
) t0
WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2; 

Open in new window

                                           

Then this one:

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
)
SELECT 
  coalesce (t0.unit, t1.unit), 
  0 as Delta, 
  coalesce (t0.time, t1.time) starttime, NULL as endtime, 
  t0.rn, 
  t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
  SELECT 
    coalesce (t0.unit, t1.unit), 
    timestampdiff (4, char(endtime - starttime)) as Delta,
    t0.time starttime, 
    t1.time endtime, 
    t0.rn, 
    t1.rn, 
    '*'
  FROM minute_data t0
  FULL JOIN minute_data t1
    ON t0.unit = t1.unit
   AND t0.rn = t1.rn - 1
  WHERE t0.unit is not NULL
    AND t1.unit is not NULL
) t0
WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;

Open in new window

Avatar of Mi-Jack

ASKER

The first one gets the same error, and the second one times out after 10 minutes
Something's wrong.  The time difference calculation should generate a parse error as the column names aren't yet aliased.  The first one produces the correct error, the second one is a mystery.

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
)
SELECT
  coalesce (t0.unit, t1.unit),
  cast ('-' as varchar(4)) as Delta,
  coalesce (t0.time, t1.time) starttime, NULL as endtime,
  t0.rn,
  t1.rn,
  CASE WHEN t0.unit is NULL then 'Unit came online'
       WHEN t1.unit is NULL then 'Unit went offline'
       ELSE '*' END
FROM minute_data t0
FULL JOIN minute_data t1
  ON t0.unit = t1.unit
 AND t0.rn = t1.rn - 1
WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
   OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))

UNION ALL

SELECT *
FROM
(
  SELECT
    coalesce (t0.unit, t1.unit),
    cast (timestampdiff (4, char(t1.time - t0.time)) as varchar(4)) as Delta,
    t0.time starttime,
    t1.time endtime,
    t0.rn,
    t1.rn,
    '*'
  FROM minute_data t0
  FULL JOIN minute_data t1
    ON t0.unit = t1.unit
   AND t0.rn = t1.rn - 1
  WHERE t0.unit is not NULL
    AND t1.unit is not NULL
) t0
WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
  AND timestampdiff (4, char(endtime - starttime)) > 3
ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

This returns 65 rows.
(is this the same result as the one from post ID: 38233930?)

1      DELTA      STARTTIME      ENDTIME      RN      RN      7
1221      57      2012-07-24 11:27:00.0      2012-07-24 12:24:00.0      85      86      *
1227      4      2012-07-24 11:52:00.0      2012-07-24 11:56:00.0      103      104      *
1228      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      61      62      *
1231      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      94      95      *
1231      9      2012-07-24 11:40:00.0      2012-07-24 11:49:00.0      96      97      *
1231      9      2012-07-24 11:53:00.0      2012-07-24 12:02:00.0      101      102      *
1234      5      2012-07-24 11:51:00.0      2012-07-24 11:56:00.0      105      106      *
1235      7      2012-07-24 11:57:00.0      2012-07-24 12:04:00.0      118      119      *
1238      52      2012-07-24 11:29:00.0      2012-07-24 12:21:00.0      81      82      *
1242      24      2012-07-24 11:46:00.0      2012-07-24 12:10:00.0      106      107      *
1244      5      2012-07-24 11:06:00.0      2012-07-24 11:11:00.0      66      67      *
1261      4      2012-07-24 11:37:00.0      2012-07-24 11:41:00.0      94      95      *
1267      44      2012-07-24 11:28:00.0      2012-07-24 12:12:00.0      84      85      *
1272      4      2012-07-24 11:22:00.0      2012-07-24 11:26:00.0      79      80      *
1273      9      2012-07-24 11:56:00.0      2012-07-24 12:05:00.0      114      115      *
1274      5      2012-07-24 11:14:00.0      2012-07-24 11:19:00.0      51      52      *
1274      6      2012-07-24 11:37:00.0      2012-07-24 11:43:00.0      69      70      *
1276      7      2012-07-24 11:24:00.0      2012-07-24 11:31:00.0      81      82      *
1277      22      2012-07-24 11:33:00.0      2012-07-24 11:55:00.0      70      71      *
1283      5      2012-07-24 11:05:00.0      2012-07-24 11:10:00.0      64      65      *
1284      5      2012-07-24 11:34:00.0      2012-07-24 11:39:00.0      92      93      *
1284      6      2012-07-24 11:39:00.0      2012-07-24 11:45:00.0      93      94      *
1285      12      2012-07-24 11:37:00.0      2012-07-24 11:49:00.0      96      97      *
1293      5      2012-07-24 11:57:00.0      2012-07-24 12:02:00.0      107      108      *
1294      26      2012-07-24 11:26:00.0      2012-07-24 11:52:00.0      83      84      *
1296      5      2012-07-24 11:09:00.0      2012-07-24 11:14:00.0      70      71      *
1297      4      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      73      74      *
1298      6      2012-07-24 11:12:00.0      2012-07-24 11:18:00.0      71      72      *
1299      -      2012-07-24 11:12:00.0      null      null      1      Unit came online
1299      4      2012-07-24 11:23:00.0      2012-07-24 11:27:00.0      12      13      *
1305      4      2012-07-24 11:18:00.0      2012-07-24 11:22:00.0      72      73      *
1305      4      2012-07-24 11:42:00.0      2012-07-24 11:46:00.0      93      94      *
1307      9      2012-07-24 11:06:00.0      2012-07-24 11:15:00.0      66      67      *
1315      19      2012-07-24 11:49:00.0      2012-07-24 12:08:00.0      105      106      *
1318      4      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      77      78      *
1320      4      2012-07-24 11:07:00.0      2012-07-24 11:11:00.0      59      60      *
1320      7      2012-07-24 11:26:00.0      2012-07-24 11:33:00.0      74      75      *
1321      18      2012-07-24 11:51:00.0      2012-07-24 12:09:00.0      90      91      *
1321      9      2012-07-24 11:20:00.0      2012-07-24 11:29:00.0      67      68      *
1325      5      2012-07-24 11:24:00.0      2012-07-24 11:29:00.0      74      75      *
1325      6      2012-07-24 11:41:00.0      2012-07-24 11:47:00.0      87      88      *
1325      9      2012-07-24 11:05:00.0      2012-07-24 11:14:00.0      64      65      *
1326      55      2012-07-24 11:27:00.0      2012-07-24 12:22:00.0      75      76      *
1328      14      2012-07-24 11:54:00.0      2012-07-24 12:08:00.0      90      91      *
1331      4      2012-07-24 11:26:00.0      2012-07-24 11:30:00.0      86      87      *
1334      5      2012-07-24 11:52:00.0      2012-07-24 11:57:00.0      107      108      *
1335      6      2012-07-24 11:26:00.0      2012-07-24 11:32:00.0      81      82      *
1336      -      2012-07-24 11:06:00.0      null      4      null      Unit went offline
1336      -      2012-07-24 11:03:00.0      null      null      1      Unit came online
1340      4      2012-07-24 11:11:00.0      2012-07-24 11:15:00.0      72      73      *
1343      5      2012-07-24 11:55:00.0      2012-07-24 12:00:00.0      115      116      *
1344      8      2012-07-24 11:30:00.0      2012-07-24 11:38:00.0      91      92      *
1351      4      2012-07-24 11:00:00.0      2012-07-24 11:04:00.0      60      61      *
1351      6      2012-07-24 11:16:00.0      2012-07-24 11:22:00.0      73      74      *
1357      9      2012-07-24 11:45:00.0      2012-07-24 11:54:00.0      104      105      *
1364      -      2012-07-24 11:03:00.0      null      null      1      Unit came online
1371      9      2012-07-24 11:14:00.0      2012-07-24 11:23:00.0      67      68      *
1375      4      2012-07-24 11:17:00.0      2012-07-24 11:21:00.0      66      67      *
1375      6      2012-07-24 11:47:00.0      2012-07-24 11:53:00.0      92      93      *
1378      5      2012-07-24 11:26:00.0      2012-07-24 11:31:00.0      83      84      *
1379      8      2012-07-24 11:06:00.0      2012-07-24 11:14:00.0      64      65      *
1384      10      2012-07-24 11:40:00.0      2012-07-24 11:50:00.0      98      99      *
1385      26      2012-07-24 11:19:00.0      2012-07-24 11:45:00.0      75      76      *
1387      -      2012-07-24 11:23:00.0      null      58      null      Unit went offline
1388      5      2012-07-24 11:20:00.0      2012-07-24 11:25:00.0      73      74      *
Same rows as before, but column 2 suggests that more is at work than we may have thought.

That is the time (in minutes) between reports by that unit.  Most of these are less than 10 minutes.  4 are over 40.

My suspicion is that if we increase the search time to 10 minutes, we're not really losing anything.  Then we'll need to decide how to handle the rest of these.  Do you want them on the report or is a unit problematic if it goes 20 minutes between reports?
Avatar of Mi-Jack

ASKER

I'd like to have gaps in transmission recorded, but separately:
1. " Unit went online"
2. there may or may not be gaps, if there are
3. " Unit went offline"
In other words - exactly what this last query returned (if I understand it correctly)
Hi Gene,

Showing some of the output here:


UNIT   DELTA      STARTTIME      ENDTIME      RN      RN      7
1221      57     2012-07-24 11:27:00.0      2012-07-24 12:24:00.0      85      86      *
1227      4      2012-07-24 11:52:00.0      2012-07-24 11:56:00.0      103      104      *
1228      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      61      62      *
1231      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      94      95      *
1231      9      2012-07-24 11:40:00.0      2012-07-24 11:49:00.0      96      97      *
1231      9      2012-07-24 11:53:00.0      2012-07-24 12:02:00.0      101      102      *
1299      -      2012-07-24 11:12:00.0      null      null      1      Unit came online
1299      4      2012-07-24 11:23:00.0      2012-07-24 11:27:00.0      12      13      *

Open in new window


We can drop the two columns labelled RN from the report.  They've been included so that I can more easily see the first and last items for a unit.

Unit 1221 had a 57 minute gap in reporting during this period.  The gap started during this hour and the unit didn't respond until the next hour.

Units 1227 and 1228 had 4 minute gaps in their reporting.

Unit 1231 had a 4 minute gap and two 9 minute gaps.  Something erratic going on there.

Unit 1229 came online 72 minutes into our sampling.  This may mean that the unit was offline, or that the time reported by the unit is off more than an hour.  The unit later had a 4 minute gap.


You and/or your organization will need to decide what these things mean to you.  We've reported them, now someone needs to analyze them.

Kent
Avatar of Mi-Jack

ASKER

Hi Kent,
I think I spoke too soon.
The gaps, what do they mean, exactly?
Take unit 1221 - why do we think it's a 57 minute gap, and not that the unit came on line at 11:27 and off line at 12:24?
I don't want to guess, so I have to ask you to explain starttime and endtime to me, and what exactly the nulls mean.
Thank you,

Gene
1221 is a good example.  :)

1221      57     2012-07-24 11:27:00.0      2012-07-24 12:24:00.0      85      86      *

The 57 represents the number of minutes between 11:27 and 12:24.  There are no records of the unit reporting during this period.  Note that the row at 11:27 is the 85th row for this unit, meaning that it was reporting in about once a minute prior to 11:27.

With that unit being so regular prior to 11:27, it looks to me like the unit went offline at 11:27 and returned to service at 12:24 (57 minutes later).  This pattern occurs several other times in the data, but this unit is perhaps the most striking example.

And it raises the question, do you want to show that there is 57 minutes where the unit didn't report?  Or do you want to show this unit going offline and coming back online?  If the later, what are the parameters.  It looks like there's quite a bit of variance (and subjectivity) here and it's not my place to guess....
Avatar of Mi-Jack

ASKER

I think the unit went off line and then back on line, looks like this DElta just needs to be treated as nothing else but duration - it was off for 57 minutes, and as such I would like it to be recorded.

This will be typical pattern - on for a while, then off (a break, shift change etc - these are heavy machines with operators). No guessing, it's impossible - just a simple rule: no records for N minutes - unit  went off line, records present again - back on line.
The subtleties will wait, need to get on solid ground first.

I can't believe it, I think we're almost there!
Avatar of Mi-Jack

ASKER

How do I offend my lord?
Hi Gene,


Sorry.  Was looking to you for more guidance.  :)

Where do we go from here?
Avatar of Mi-Jack

ASKER

Hi Kent,

The last query does almost all of it, but instead of listing lack of records as GAPs, if we could treat that OFF line at the beginning of the gap and ON line at its end - that would be the end of this.

Duration is still valuable, so if we could keep it there - great, if not - it can be calculated.

Something like this:

UNIT - unit number, as before
DELTA - duration of either up- or down-time
STARTTIME  start of either up- or down-time
ENDTIME - end of either up- or down-time
      RN      RN      7 - same as before

Is this doable?
Ok.  A couple of quick questions.

-- What do we want to use for our limits?  The 3 minute limit seems to result in the selection of a lot of units that don't report for 4 to 9 minute.  Do you want to increase the 3 minute limit?  Maybe group comments for gaps of (for example) 4-6, 6-9, and 10+ minutes?

-- We can split rows (such as the one with the 57 minute gap) into two rows, show the online and offline comments, and the time offline (57).  No problem.  What duration is the limit where we should show "went offline" / "came online" messages instead of a single row that shows what could be SOP?
Avatar of Mi-Jack

ASKER

I am not sure that I understand correctly.
>>Maybe group comments for gaps of (for example) 4-6, 6-9, and 10+ minutes?
What would that look like?


>>We can split rows (such as the one with the 57 minute gap) into two rows
Why is that a single row in the first place? What is different here from the 3-minute rule?
I am afraid I am missing the point - I wouldn't want to muck things up now!

>> What duration is the limit where we should show "went offline" / "came online" >>messages instead of a single row that shows what could be SOP?
A unit can be OFF for a month - if this is what you mean by the limit

Gene
I am not sure that I understand correctly.
>>Maybe group comments for gaps of (for example) 4-6, 6-9, and 10+ minutes?
What would that look like?

UNIT   DELTA      STARTTIME                 ENDTIME                    RN      RN      COMMENT
1221      57     2012-07-24 11:27:00.0      2012-07-24 12:24:00.0      85      86      *
1227      4      2012-07-24 11:52:00.0      2012-07-24 11:56:00.0      103     104     Note:  Offline > 3 minutes
1228      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      61      62      Note:  Offline > 3 minutes
1231      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      94      95      Note:  Offline > 3 minutes
1231      9      2012-07-24 11:40:00.0      2012-07-24 11:49:00.0      96      97      WARN:  Offline > 6 minutes
1231      9      2012-07-24 11:53:00.0      2012-07-24 12:02:00.0      101     102     WARN:  Offline > 6 minutes
1299      -      2012-07-24 11:12:00.0      null                       null    1       Unit came online
1299      4      2012-07-24 11:23:00.0      2012-07-24 11:27:00.0      12      13      Note:  Offline > 3 minutes

Open in new window


                                           
>>We can split rows (such as the one with the 57 minute gap) into two rows
Why is that a single row in the first place? What is different here from the 3-minute rule?
I am afraid I am missing the point - I wouldn't want to muck things up now!

I'm not sure that you really want to split the 3-minute rows.  We certainly can, and it makes the SQL simpler to do that, but I'm not sure that it makes for the most useful report.  That's your call.  :)  

UNIT   DELTA      STARTTIME                 ENDTIME                    RN      RN      COMMENT
1221      57     2012-07-24 11:27:00.0      null                       85      86      Unit went offline
1221      57     2012-07-24 12:24:00.0      null                       85      86      Unit came online
1227      4      2012-07-24 11:52:00.0      null                       103    104      Unit went offline
1227      4      2012-07-24 11:56:00.0      null                       103    104      Unit came online
1228      4      2012-07-24 11:35:00.0      null                       61      62      Unit went offline
1228      4      2012-07-24 11:39:00.0      null                       61      62      Unit came online
1231      4      2012-07-24 11:35:00.0      null                       94      95      Unit went online
1231      4      2012-07-24 11:39:00.0      null                       94      95      Unit came online
1231      9      2012-07-24 11:40:00.0      null                       96      97      Unit went offline
1231      9      2012-07-24 11:49:00.0      null                       96      97      Unit came online
1231      9      2012-07-24 11:53:00.0      null                       101    102      Unit went offline
1231      9      2012-07-24 12:02:00.0      null                       101    102      Unit came online
1299      -      2012-07-24 11:12:00.0      null                       null    1       Unit came online
1299      4      2012-07-24 11:23:00.0      null                       12      13      Unit went offline
1299      4      2012-07-24 11:27:00.0      null                       12      13      Unit came online

Open in new window

               
If we're not going to show offline/online times on a single row like in the previous examples, we can drop the ENDTIME column from the report.  It will always be NULL.


>> What duration is the limit where we should show "went offline" / "came online" >>messages instead of a single row that shows what could be SOP?
A unit can be OFF for a month - if this is what you mean by the limit

I was wondering more if you wanted to adhere to a strict 3-minute limit.  Answers to the first two questions above should solve this one, too.
Avatar of Mi-Jack

ASKER

Comments for gaps looks great, that would be fantastic.
When you say "adhere to a strict 3-minute limit" -  do you mean that only 3-minute silence is considered? Any gap in transmission  that is longer than 3 minutes indicates an OFF line condition
Ok.  Just to be sure, do you want to use these parameters to base the comments?

<= 3 minutes -- OK.
4 - 6 minutes -- Note:  Offline > 3 minutes
7 - 9 minutes -- WARN:  Offline > 6 minutes
  > 9 minutes -- Show "went offline" and "came online" messages
Avatar of Mi-Jack

ASKER

Absolutely.
The above is fine, although the way I look at it, that is a 9-minute rule - stay dark for more than 9 minutes and you are considered OFF. The "Note" and "WARN" messages are a very nice touch, makes it much better.
9 minutes is even better, let's just do 9.
Ok.  Big changes.  :)

Let's see how badly I mucked this up....


WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
),
UnitStatusMissing (Unit, Delta, StartTime, EndTime, RN1, RN2, Comment)
AS
(
	SELECT
		coalesce (t0.unit, t1.unit),
		0 as Delta,
		coalesce (t0.time, t1.time) starttime, NULL as endtime,
		t0.rn,
		t1.rn,
		CASE WHEN t0.unit is NULL then 'Unit came online'
				 WHEN t1.unit is NULL then 'Unit went offline'
				 ELSE '*' END
	FROM minute_data t0
	FULL JOIN minute_data t1
		ON t0.unit = t1.unit
	 AND t0.rn = t1.rn - 1
	WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
		AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
		 OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))
),
UnitStatusChanges (Unit, Delta, StartTime, EndTime, RN1, RN2)
AS
(
	SELECT *
	FROM
	(
		SELECT
			coalesce (t0.unit, t1.unit),
			timestampdiff (4, char(t1.time - t0.time)) as Delta,
			t0.time starttime,
			t1.time endtime,
			t0.rn,
			t1.rn
		FROM minute_data t0
		FULL JOIN minute_data t1
			ON t0.unit = t1.unit
		 AND t0.rn = t1.rn - 1
		WHERE t0.unit is not NULL
			AND t1.unit is not NULL
	) t0
	WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
		AND timestampdiff (4, char(endtime - starttime)) > 3
)
SELECT * 
FROM UnitStatusMissing

UNION ALL

SELECT t0.*,
  CASE WHEN Delta <= 6 THEN "Note:  Offline > 3 minutes
       WHEN Delta <= 9 THEN "WARN:  Offline > 6 minutes
       ELSE '**'
  END
FROM UnitStatusChanges t0 
WHERE Delta < 10

UNION ALL 

SELECT unit, Delta, StartTime, NULL, RN, RN, 'Unit went offline'
FROM UnitStatusChanges
WHERE Delta >= 10

UNION ALL

SELECT unit, Delta, EndTime, NULL, RN, RN, 'Unit came online'
FROM UnitStatusChanges
WHERE Delta >= 10

ORDER BY 1, 2; 

Open in new window

I do despise it when the editor bounces between spaces and TABs.

yucko....
Avatar of Mi-Jack

ASKER

SModified (missing quotes):

WITH minute_data (unit, time, rn)
AS
(
  SELECT radio_id, time, row_number () over (partition by radio_id order by time) rn
  FROM
  (
    SELECT DISTINCT radio_id, reporttime as time
    FROM pos_summary_data
  ) t2
),
UnitStatusMissing (Unit, Delta, StartTime, EndTime, RN1, RN2, Comment)
AS
(
      SELECT
            coalesce (t0.unit, t1.unit),
            0 as Delta,
            coalesce (t0.time, t1.time) starttime, NULL as endtime,
            t0.rn,
            t1.rn,
            CASE WHEN t0.unit is NULL then 'Unit came online'
                         WHEN t1.unit is NULL then 'Unit went offline'
                         ELSE '*' END
      FROM minute_data t0
      FULL JOIN minute_data t1
            ON t0.unit = t1.unit
       AND t0.rn = t1.rn - 1
      WHERE coalesce (t0.time, t1.time) between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
            AND ((t0.unit is NULL and t1.time > (SELECT min(time) FROM minute_data) + 3 minutes)
             OR (t1.unit is NULL and t0.time < (SELECT max(time) FROM minute_data) - 3 minutes))
),
UnitStatusChanges (Unit, Delta, StartTime, EndTime, RN1, RN2)
AS
(
      SELECT *
      FROM
      (
            SELECT
                  coalesce (t0.unit, t1.unit),
                  timestampdiff (4, char(t1.time - t0.time)) as Delta,
                  t0.time starttime,
                  t1.time endtime,
                  t0.rn,
                  t1.rn
            FROM minute_data t0
            FULL JOIN minute_data t1
                  ON t0.unit = t1.unit
             AND t0.rn = t1.rn - 1
            WHERE t0.unit is not NULL
                  AND t1.unit is not NULL
      ) t0
      WHERE starttime between timestamp('2012-07-24-11.00.00.000000') and timestamp('2012-07-24-12.00.00.000000')
            AND timestampdiff (4, char(endtime - starttime)) > 3
)
SELECT *
FROM UnitStatusMissing

UNION ALL

SELECT t0.*,
  CASE WHEN Delta <= 6 THEN 'Note:  Offline > 3 minutes'
       WHEN Delta <= 9 THEN 'WARN:  Offline > 6 minutes'
       ELSE '**'
  END
FROM UnitStatusChanges t0
WHERE Delta < 10

UNION ALL

SELECT unit, Delta, StartTime, NULL, RN, RN, 'Unit went offline'
FROM UnitStatusChanges
WHERE Delta >= 10

UNION ALL

SELECT unit, Delta, EndTime, NULL, RN, RN, 'Unit came online'
FROM UnitStatusChanges
WHERE Delta >= 10

ORDER BY 1, 2;
Avatar of Mi-Jack

ASKER

Gives an error:

ERROR: An undefined column or parameter name was detected.

DB2
SQL Error: SQLCODE=-206, SQLSTATE=42703, SQLERRMC=RN, DRIVER=3.57.82
Error
Code: -206

I'm out for several hours.
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Mi-Jack

ASKER

That worked,

UNIT      DELTA      STARTTIME      ENDTIME      RN1      RN2      COMMENT
1221      57      2012-07-24 11:27:00.0      null      85      86      Unit went offline
1221      57      2012-07-24 12:24:00.0      null      85      86      Unit came online
1227      4      2012-07-24 11:52:00.0      2012-07-24 11:56:00.0      103      104      Note:  Offline > 3 minutes
1228      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      61      62      Note:  Offline > 3 minutes
1231      4      2012-07-24 11:35:00.0      2012-07-24 11:39:00.0      94      95      Note:  Offline > 3 minutes
1231      9      2012-07-24 11:40:00.0      2012-07-24 11:49:00.0      96      97      WARN:  Offline > 6 minutes
1231      9      2012-07-24 11:53:00.0      2012-07-24 12:02:00.0      101      102      WARN:  Offline > 6 minutes
1234      5      2012-07-24 11:51:00.0      2012-07-24 11:56:00.0      105      106      Note:  Offline > 3 minutes
1235      7      2012-07-24 11:57:00.0      2012-07-24 12:04:00.0      118      119      WARN:  Offline > 6 minutes
1238      52      2012-07-24 11:29:00.0      null      81      82      Unit went offline
1238      52      2012-07-24 12:21:00.0      null      81      82      Unit came online
1242      24      2012-07-24 11:46:00.0      null      106      107      Unit went offline
1242      24      2012-07-24 12:10:00.0      null      106      107      Unit came online
1244      5      2012-07-24 11:06:00.0      2012-07-24 11:11:00.0      66      67      Note:  Offline > 3 minutes
1261      4      2012-07-24 11:37:00.0      2012-07-24 11:41:00.0      94      95      Note:  Offline > 3 minutes
1267      44      2012-07-24 11:28:00.0      null      84      85      Unit went offline
1267      44      2012-07-24 12:12:00.0      null      84      85      Unit came online
1272      4      2012-07-24 11:22:00.0      2012-07-24 11:26:00.0      79      80      Note:  Offline > 3 minutes
1273      9      2012-07-24 11:56:00.0      2012-07-24 12:05:00.0      114      115      WARN:  Offline > 6 minutes
1274      5      2012-07-24 11:14:00.0      2012-07-24 11:19:00.0      51      52      Note:  Offline > 3 minutes
1274      6      2012-07-24 11:37:00.0      2012-07-24 11:43:00.0      69      70      Note:  Offline > 3 minutes
1276      7      2012-07-24 11:24:00.0      2012-07-24 11:31:00.0      81      82      WARN:  Offline > 6 minutes
1277      22      2012-07-24 11:33:00.0      null      70      71      Unit went offline
1277      22      2012-07-24 11:55:00.0      null      70      71      Unit came online
1283      5      2012-07-24 11:05:00.0      2012-07-24 11:10:00.0      64      65      Note:  Offline > 3 minutes
1284      5      2012-07-24 11:34:00.0      2012-07-24 11:39:00.0      92      93      Note:  Offline > 3 minutes
1284      6      2012-07-24 11:39:00.0      2012-07-24 11:45:00.0      93      94      Note:  Offline > 3 minutes
1285      12      2012-07-24 11:37:00.0      null      96      97      Unit went offline
1285      12      2012-07-24 11:49:00.0      null      96      97      Unit came online
1293      5      2012-07-24 11:57:00.0      2012-07-24 12:02:00.0      107      108      Note:  Offline > 3 minutes
1294      26      2012-07-24 11:26:00.0      null      83      84      Unit went offline
1294      26      2012-07-24 11:52:00.0      null      83      84      Unit came online
1296      5      2012-07-24 11:09:00.0      2012-07-24 11:14:00.0      70      71      Note:  Offline > 3 minutes
1297      4      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      73      74      Note:  Offline > 3 minutes
1298      6      2012-07-24 11:12:00.0      2012-07-24 11:18:00.0      71      72      Note:  Offline > 3 minutes
1299      0      2012-07-24 11:12:00.0      null      null      1      Unit came online
1299      4      2012-07-24 11:23:00.0      2012-07-24 11:27:00.0      12      13      Note:  Offline > 3 minutes
1305      4      2012-07-24 11:18:00.0      2012-07-24 11:22:00.0      72      73      Note:  Offline > 3 minutes
1305      4      2012-07-24 11:42:00.0      2012-07-24 11:46:00.0      93      94      Note:  Offline > 3 minutes
1307      9      2012-07-24 11:06:00.0      2012-07-24 11:15:00.0      66      67      WARN:  Offline > 6 minutes
1315      19      2012-07-24 11:49:00.0      null      105      106      Unit went offline
1315      19      2012-07-24 12:08:00.0      null      105      106      Unit came online
1318      4      2012-07-24 11:21:00.0      2012-07-24 11:25:00.0      77      78      Note:  Offline > 3 minutes
1320      4      2012-07-24 11:07:00.0      2012-07-24 11:11:00.0      59      60      Note:  Offline > 3 minutes
1320      7      2012-07-24 11:26:00.0      2012-07-24 11:33:00.0      74      75      WARN:  Offline > 6 minutes
1321      9      2012-07-24 11:20:00.0      2012-07-24 11:29:00.0      67      68      WARN:  Offline > 6 minutes
1321      18      2012-07-24 11:51:00.0      null      90      91      Unit went offline
1321      18      2012-07-24 12:09:00.0      null      90      91      Unit came online
1325      5      2012-07-24 11:24:00.0      2012-07-24 11:29:00.0      74      75      Note:  Offline > 3 minutes
1325      6      2012-07-24 11:41:00.0      2012-07-24 11:47:00.0      87      88      Note:  Offline > 3 minutes
1325      9      2012-07-24 11:05:00.0      2012-07-24 11:14:00.0      64      65      WARN:  Offline > 6 minutes
1326      55      2012-07-24 11:27:00.0      null      75      76      Unit went offline
1326      55      2012-07-24 12:22:00.0      null      75      76      Unit came online
1328      14      2012-07-24 11:54:00.0      null      90      91      Unit went offline
1328      14      2012-07-24 12:08:00.0      null      90      91      Unit came online
1331      4      2012-07-24 11:26:00.0      2012-07-24 11:30:00.0      86      87      Note:  Offline > 3 minutes
1334      5      2012-07-24 11:52:00.0      2012-07-24 11:57:00.0      107      108      Note:  Offline > 3 minutes
1335      6      2012-07-24 11:26:00.0      2012-07-24 11:32:00.0      81      82      Note:  Offline > 3 minutes
1336      0      2012-07-24 11:06:00.0      null      4      null      Unit went offline
1336      0      2012-07-24 11:03:00.0      null      null      1      Unit came online
1340      4      2012-07-24 11:11:00.0      2012-07-24 11:15:00.0      72      73      Note:  Offline > 3 minutes
1343      5      2012-07-24 11:55:00.0      2012-07-24 12:00:00.0      115      116      Note:  Offline > 3 minutes
1344      8      2012-07-24 11:30:00.0      2012-07-24 11:38:00.0      91      92      WARN:  Offline > 6 minutes
1351      4      2012-07-24 11:00:00.0      2012-07-24 11:04:00.0      60      61      Note:  Offline > 3 minutes
1351      6      2012-07-24 11:16:00.0      2012-07-24 11:22:00.0      73      74      Note:  Offline > 3 minutes
1357      9      2012-07-24 11:45:00.0      2012-07-24 11:54:00.0      104      105      WARN:  Offline > 6 minutes
1364      0      2012-07-24 11:03:00.0      null      null      1      Unit came online
1371      9      2012-07-24 11:14:00.0      2012-07-24 11:23:00.0      67      68      WARN:  Offline > 6 minutes
1375      4      2012-07-24 11:17:00.0      2012-07-24 11:21:00.0      66      67      Note:  Offline > 3 minutes
1375      6      2012-07-24 11:47:00.0      2012-07-24 11:53:00.0      92      93      Note:  Offline > 3 minutes
1378      5      2012-07-24 11:26:00.0      2012-07-24 11:31:00.0      83      84      Note:  Offline > 3 minutes
1379      8      2012-07-24 11:06:00.0      2012-07-24 11:14:00.0      64      65      WARN:  Offline > 6 minutes
1384      10      2012-07-24 11:40:00.0      null      98      99      Unit went offline
1384      10      2012-07-24 11:50:00.0      null      98      99      Unit came online
1385      26      2012-07-24 11:19:00.0      null      75      76      Unit went offline
1385      26      2012-07-24 11:45:00.0      null      75      76      Unit came online
1387      0      2012-07-24 11:23:00.0      null      58      null      Unit went offline
1388      5      2012-07-24 11:20:00.0      2012-07-24 11:25:00.0      73      74      Note:  Offline > 3 minutes
Avatar of Mi-Jack

ASKER

All I need to do now is to write 2 SPs: one for populating pos_summary_data with new data every hour, and another one wrapping this last query (substitute start/end times with variables), also executed every hour.
But boy, is this last one long!
Will take me a while to figure this all out
Avatar of Mi-Jack

ASKER

I can't thank you enough.
Accepting a solution for 500 points really looks pathetic.
Avatar of Mi-Jack

ASKER

I think we effectively doubled Expert Exchange database size :)
Want to see the database grow?  Get me talking about fishing, trucks, FSU athletics, or Jack Daniels.

  :)


Enjoyed it!

Kent