Oracle - Count the same value used on consecutive days

Question

Date        jm       Text
--------    ----     ----
6/3/2015    ne       Good
6/4/2015    ne       Good
6/5/2015    ne       Same
6/8/2015    ne       Same

I want to count how often the "same" value occurs in a set of consecutive days. I dont want to count the value for the whole database. Now on the current date it is 2 (above example).

It is very important for me that "Same" never occurs...

The query has to ignore the weekend (6 and 7 june).

Date        jm       Text
--------    ----     ----
6/3/2015    ne       Same
6/4/2015    ne       Same
6/5/2015    ne       Good
6/8/2015    ne       Good

In this example the count is zero

Do you have exactly one record for each day (except the weekends), or can there be multiple or none for certain days? Also the obligatory "What have you tried"? — GolezTrol, Jun 07 '15 at 19:15
What if `6/9/2015` is 'Ye' again (or 'Ya') and `6/10/2015` is 'ne'. Could you add those to the list, and then specify the output that you would like to have? I hope I don't annoy you, but I'm really trying to get clear what you want exactly. — GolezTrol, Jun 07 '15 at 19:31

score 1 · Accepted Answer · edited May 23 '17 at 12:22

Okay, I'm starting to get the picture, although at first I thought you wanted to count by jm, and now it seems you want to count by Text = 'Same'. Anyway, that's what this query should do. It gets the row for the current date. Is connects all previous rows and counts them. Also, it shows whether the current text (and that of the connected rows).

So the query will return one row (if there is one for today), which will show the date, jm and Text of the current date, the number of consecutive days for which the Text has been the same (just in case you want to know how many days it is 'Good'), and the number of days (either 0 or the same as the other count) for which the Text has been 'Same'.

I hope this query is right, or at least it gives you an idea of how to solve the problem using CONNECT BY. I should mention I based the 'Friday-detection' on this question.

Also, I don't have Oracle at hand, so please forgive me for any minor syntax errors.

WITH
  VW_SAMESTATUSES AS
  ( SELECT      t.*  
    FROM        YourTable t
    START WITH  -- Start with the row for today
                t.Date = trunc(sysdate)
    CONNECT BY  -- Connect to previous row that have a lower date.
                -- Note that PRIOR refers to the prior record, which is
                -- actually the NEXT day. :)
                t.Date = PRIOR t.Date + 
                  CASE MOD(TO_CHAR(t.Date, 'J'), 7) + 1
                    WHEN 5 THEN 3 -- Friday, so add 3
                    ELSE 1 -- Other days, so add one
                  END
                -- And the Text also has to match to the one of the next day.
                AND t.Text = PRIOR t.Text)

SELECT    s.Date,
          s.jm,
          MAX(Text) AS CurrentText, -- Not really MAX, they are actually all the same
          COUNT(*) AS ConsecutiveDays,
          COUNT(CASE WHEN Text = 'Same' THEN 1 END) as SameCount
FROM      VW_SAMESTATUSES s
GROUP BY  s.Date,
          s.jm

score 0 · Answer 2 · answered Jun 07 '15 at 20:57

This recursive query (available from Oracle version 11g) might be useful:

with s(tcode, tdate) as (
  select tcode, tdate from test where tdate = date '2015-06-08'
  union all 
  select t.tcode, t.tdate from test t, s 
    where s.tcode = t.tcode 
      and t.tdate = s.tdate - decode(s.tdate-trunc(s.tdate, 'iw'), 0, 3, 1) )
select count(1) cnt from s

^SQLFiddle

I prepared sample data according to your original question, without further edits, you can see them in attached SQLFiddle. Additional conditions for column 'Text' are very simple, just add something like ... and Text ='Same' in where clauses.

In current version query counts number of previous days starting from given date (change it in line 2) where dates are consecutive (excluding weekend days) and values in column tcode is the same for all days.

Part: decode(s.tdate-trunc(s.tdate, 'iw'), 0, 3, 1) is for substracting days depending if it's Monday or other day, and should work independently from NLS settings.

Oracle - Count the same value used on consecutive days

2 Answers2