Top In Consecutive Days Query

[ApocY85]

I have a rather large query I've been building for a report. It appears all is working well, except for one section. It shouldn't be this hard, but for some reason I'm not getting that section to work. Perhaps I've simply been working on the report for too long, but hopefully someone out there can help.

For that section, I have a table called valid_alerts. It has data much like the following (in this example, I made 'hostname' values easier to read and remember):

===============
alert_date hostname
===============
11/13/09 A
11/12/09 A
11/11/09 A
11/10/09 A
---------------------------
11/06/09 A
11/05/09 A
---------------------------
11/06/09 B
11/04/09 B
===============

I am trying to get records where alert_date has no consecutive dates above it. For instance, the result from the example above would be as follows:

===============
alert_date hostname
===============
11/13/09 A
---------------------------
11/06/09 A
---------------------------
11/06/09 B
===============

Obviously, there is much more data than given in this example.

By the way, 'alert_date' and 'hostname' together will uniquely identify a record. I tried the following SQL, thinking it would work. However, it appears as though it is not working at all:

SELECT alert_date, hostname
FROM valid_alerts v_a
WHERE (alert_date || ' ' || hostname) NOT IN (SELECT alert_date || ' ' || hostname AS combined
FROM valid_alerts
WHERE alert_date = v_a.alert_date - 1)

If anyone could help me correct my SQL or think of a better version, I would greatly appreciate your help!!

 

[Thargy]

Apoc,

your question is invalid. Since oracle's default is heap organised tables, the concept of one record being "above" another is meaningless.

If you can post the create table statement for the relevant table, and insert statements with sample data (anonymised if necessary) I can tackle this one easily (I think), using analytics.

Can you please post the relevant scripts.

Regards

T

 

[ApocY85]

Unfortunately, I cannot obtain the create table and insert statements.

I believe the word "above" may have taken too literally. What I mean is that I would like records where there is not another record who's alert_date value is greater by one. So, if there is a record for '11/10/09' and hostname 'A', and another record for '11/11/09' and hostname 'A', then the '11/10/09' record would not return in the results because there existed another record with a alert_date of one day ahead ('11/10/09').

I hope this clears things up for you.

Again, I greatly appreciate anyone's assistance

 

[carp]

Apoc -
The following will should get you all hostname/alert_date pairs that do not have a successor on the next day:

SELECT hostname, alert_date 
  FROM myTable o
 WHERE NOT EXISTS (SELECT 'x'
                     FROM myTable i
                    WHERE i.hostname = o.hostname
                      AND TRUNC(i.alert_date) = TRUNC(o.alert_date + 1));

Then, to get the maximum date for each hostname, you could use

SELECT hostname, max(alert_date)
  FROM (SELECT hostname, alert_date 
          FROM myTable o
         WHERE NOT EXISTS (SELECT 'x'
                             FROM myTable i
                            WHERE i.hostname = o.hostname
                              AND TRUNC(i.alert_date) = TRUNC(o.alert_date + 1))
       )
GROUP BY hostname;

I haven't tested this, and there's probably a more efficient way to do this, but this should at least get you close.

 

[lecorr]

In fact, carp got it nearly.

You just need:

SELECT hostname, max(alert_date) alert_date
  FROM myTable
GROUP BY hostname;

Because you want the last date for each hostname.

Note: Take care that a DATE in Oracle includes hours... If the code that inserts in your table doesn't do a truncate(sysdate), so the date you see may be in the range from 0 to 12pm(minus 1 second).

I hope that helps,

Christian

 

[carp]

Christian -
Of course! The most recent date with no consecutive dates beyond it would always be the most recent date. An excellent point that blew by me completely.

 

[Dagon]

I'm a bit confused by the "consecutive days" business. Why is 11/04/09 B not included ? There is no record for 11/05/09 B, so if consecutive means there exists a date which is one day higher than the current one, it ought to be included.

I find it hard to believe the problem boils down to just finding the maximum date within hostname, but in the absence of a clearer definition, perhaps Lecorr is right.

This would be my suggestion but it also returns the 11/04/09 B record on the grounds that there is no record which is exactly day higher:

select alert_date, hostname
from
(SELECT alert_date, hostname, lead(alert_date) over (partition by hostname order by alert_date) as next_alert from alerts)
where next_alert is null or (next_alert <> alert_date + 1)

 

[Dagon]

Looking at your original query, the reason it doesn't work is because you are joining valid_alerts on alert_date = v_a.alert_date - 1, so your manufactured key (alert_date || ' ' || hostname) will have alert_date - 1 in it for v_b, which will never equal the alert_date from v_a.

I think what you really meant was:

SELECT alert_date, hostname
FROM   valid_alerts v_a
WHERE NOT EXISTS
(SELECT 1   
FROM  valid_alerts v_b 
WHERE v_b.alert_date = v_a.alert_date - 1
AND   v_b.hostname   = v_a.hostname)

However, this also gives 11/04/09 B.

 

[ApocY85]

Yes Dagon, you are correct. The '11/4/2009' entry should also have been a result in the example I had provided.

Dagon and carp's solutions were exactly what I was looking for. Like I had anticipated, I had gotten so wrapped up in the large query, that I was overlooking such a small, ridiculous error, and that was to link the inner and outer tables by their hostnames. I originally used the "NOT EXISTS" method, too, but was changing things around while trying to find the solution. *sigh*

It's interesting to see that carp knew where I was going with the query. I needed the most recent occurrence (but not the one that occurred the day before), but I left that requirement out for simplicity. So, even though lecorr's version of carp's solution was logically correct , it would not work in my case.

Thank you to everyone who participated in finding a solution for me!!!