Forum Stats

  • 3,734,036 Users
  • 2,246,863 Discussions
  • 7,857,005 Comments

Discussions

Howdy, Stranger!

It looks like you're new here. If you want to get involved, click one of these buttons!

Log In Register

Categories

Tricky Analytical/Aggregae Sql

dd_ram
dd_ram Member Posts: 224
edited September 2008 in SQL & PL/SQL
Hi,
I have data like below.
MRP_DATE SHORT_FLAG
8/25/2008 Y
8/26/2008 Y
8/27/2008 N
8/28/2008 Y
8/29/2008 Y
8/30/2008 Y
8/31/2008 N
9/1/2008 Y
9/2/2008 Y
9/3/2008 N
9/4/2008 N
I want to calculate Short_days. If the Short_flag changes from Y to N, it needs to be reset to 1. The output should look like below.
MRP_DATE SHORT_FLAG Shortage_days
8/25/2008 Y 1
8/26/2008 Y 2
8/27/2008 N 0
8/28/2008 Y 1
8/29/2008 Y 2
8/30/2008 Y 3
8/31/2008 N 0
9/1/2008 Y 1
9/2/2008 Y 2
9/3/2008 N 0
9/4/2008 N 0
Would you guys Please tell me how to achieve this??

Thanks,
Ram.
· Share on TwitterShare on Facebook

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Accepted Answer
    OK, you have a table with holiday dates, but do you store weekend/holiday rows in the main table? And if you do, what SHORT_FLAG values do they have? Can it be N? Also, why:
    9/6/2008 Y 2
    when 9/6/2008 is weekend (Saturday). I assume it is a typo and it should be:
    9/6/2008 Y 0
    Anyway, for now I will assume you do store weekend/holiday rows in the main table and they can have value N:
    with t as(
          select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
          select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/6/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual
         ),
    h as (
          select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
         )
select  mpr_date,grp,
        short_flag,
        case
          when weight = 0 then 0
          else sum(weight) over(partition by grp order by mpr_date)
        end shortage_days
  from  (
         select  mpr_date,
                 short_flag,
                 sum(
                     case
                       when short_flag = 'N' then 1
                       when mpr_date_gap > 1 then 1
                       else 0
                     end
                    ) over(order by mpr_date) as grp,
                 case
                   when to_char(mpr_date,'d','nls_date_language=american') in ('1','7') then 0
                   when 1 = (select 1 from h where h_date = mpr_date) then 0
                   when short_flag = 'N' then 0
                   else 1
                 end weight
           from (
                 select  mpr_date,
                         short_flag,
                         mpr_date - lag(mpr_date) over(order by mpr_date) mpr_date_gap
                   from  t
                )
        )
/
    SQL> with t as(
  2            select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3            select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4            select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5            select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6            select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7            select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8            select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  9            select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10            select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11            select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 12            select to_date('9/6/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual
 13           ),
 14      h as (
 15            select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
 16           )
 17  select  mpr_date,grp,
 18          short_flag,
 19          case
 20            when weight = 0 then 0
 21            else sum(weight) over(partition by grp order by mpr_date)
 22          end shortage_days
 23    from  (
 24           select  mpr_date,
 25                   short_flag,
 26                   sum(
 27                       case
 28                         when short_flag = 'N' then 1
 29                         when mpr_date_gap > 1 then 1
 30                         else 0
 31                       end
 32                      ) over(order by mpr_date) as grp,
 33                   case
 34                     when to_char(mpr_date,'d','nls_date_language=american') in ('1','7') then 0
 35                     when 1 = (select 1 from h where h_date = mpr_date) then 0
 36                     when short_flag = 'N' then 0
 37                     else 1
 38                   end weight
 39             from (
 40                   select  mpr_date,
 41                           short_flag,
 42                           mpr_date - lag(mpr_date) over(order by mpr_date) mpr_date_gap
 43                     from  t
 44                  )
 45          )
 46  /

MPR_DATE         GRP S SHORTAGE_DAYS
--------- ---------- - -------------
25-AUG-08          0 Y             1
26-AUG-08          0 Y             2
27-AUG-08          1 N             0
28-AUG-08          1 Y             1
29-AUG-08          1 Y             2
30-AUG-08          1 Y             0
31-AUG-08          1 Y             0
01-SEP-08          1 Y             0
02-SEP-08          1 Y             3
05-SEP-08          2 Y             1
06-SEP-08          2 Y             0

11 rows selected.
    SY.
    · Share on TwitterShare on Facebook
«1

Answers

  • Randolf Geist
    Randolf Geist Member Posts: 2,214 Silver Trophy
    There is probably a much simpler way to do it, but as a starting point:
    with data as (
select to_date('8/25/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('8/26/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('8/27/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual union all
select to_date('8/28/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('8/29/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('8/30/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('8/31/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual union all
select to_date('9/1/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('9/2/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
select to_date('9/3/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual union all
select to_date('9/4/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual
), 
data2 as (
select 
case 
when lag(short_flag, 1, 'N') over (order by mrp_date) = 'N' 
then mrp_date 
else null 
end as group_expr,
data.*
from data
), 
data3 as (
select last_value(group_expr ignore nulls) over (order by mrp_date) as group_expr2, 
data2.*
from data2
)
select case 
when short_flag = 'Y' 
then
count(*) over (partition by group_expr2 order by mrp_date) 
else 0
end as short_days,
data3.* from data3;
    gives this result in SQL*Plus:
    SQL>
SQL> with data as (
  2  select to_date('8/25/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
  3  select to_date('8/26/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
  4  select to_date('8/27/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual union all
  5  select to_date('8/28/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
  6  select to_date('8/29/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
  7  select to_date('8/30/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
  8  select to_date('8/31/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual union all
  9  select to_date('9/1/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
 10  select to_date('9/2/2008', 'MM/DD/YYYY') as mrp_date, 'Y' as short_flag from dual union all
 11  select to_date('9/3/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual union all
 12  select to_date('9/4/2008', 'MM/DD/YYYY') as mrp_date, 'N' as short_flag from dual
 13  ),
 14  data2 as (
 15  select
 16  data.*,
 17  case
 18  when lag(short_flag, 1, 'N') over (order by mrp_date) = 'N'
 19  then mrp_date
 20  else null
 21  end as group_expr
 22  from data
 23  ),
 24  data3 as (
 25  select
 26  data2.*,
 27  last_value(group_expr ignore nulls) over (order by mrp_date) as group_expr2
 28  from data2
 29  )
 30  select
 31  data3.*,
 32  case
 33  when short_flag = 'Y'
 34  then
 35  count(*) over (partition by group_expr2 order by mrp_date)
 36  else 0
 37  end as short_days
 38  from data3;

MRP_DATE S GROUP_EX GROUP_EX SHORT_DAYS
-------- - -------- -------- ----------
25.08.08 Y 25.08.08 25.08.08          1
26.08.08 Y          25.08.08          2
27.08.08 N          25.08.08          0
28.08.08 Y 28.08.08 28.08.08          1
29.08.08 Y          28.08.08          2
30.08.08 Y          28.08.08          3
31.08.08 N          28.08.08          0
01.09.08 Y 01.09.08 01.09.08          1
02.09.08 Y          01.09.08          2
03.09.08 N          01.09.08          0
04.09.08 N 04.09.08 04.09.08          0

11 rows selected.

SQL>
    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle:
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Your data sample is not clear. It has consecutive dates. I am assuming you need a cumulative number of days since last 'N'. Also, your first row has 'Y', so we do not know when the previous N was. I will assume 1 day before:
    SQL> with t as(
  2            select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
  3            select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
  4            select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union all
  5            select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
  6            select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
  7            select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
  8            select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union all
  9            select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
 10            select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union all
 11            select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union all
 12            select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual
 13           )
 14  select  mpr_date,
 15          short_flag,
 16          decode(grp,0,1,0) +
 17          mpr_date - min(mpr_date) over(partition by grp order by mpr_date) Shortage_days
 18    from  (
 19           select  mpr_date,
 20                   short_flag,
 21                   sum(decode(short_flag,'N',1,0)) over(order by mpr_date) grp
 22            from  t
 23          )
 24  /

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
25-AUG-08 Y             1
26-AUG-08 Y             2
27-AUG-08 N             0
28-AUG-08 Y             1
29-AUG-08 Y             2
30-AUG-08 Y             3
31-AUG-08 N             0
01-SEP-08 Y             1
02-SEP-08 Y             2
03-SEP-08 N             0
04-SEP-08 N             0

11 rows selected.

SQL>
    SY.
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge 
    with t as(
select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
select mpr_date,short_flag,SHORTAGE_DAYS
  from t
 model
 dimension by (mpr_date)
 measures(short_flag,0 as SHORTAGE_DAYS)
 rules(SHORTAGE_DAYS[any] order by mpr_date
     = case when short_flag[cv()] = 'Y' 
            then presentv(SHORTAGE_DAYS[cv()-1],SHORTAGE_DAYS[cv()-1]+1,1)
            else 0 end);

MPR_DATE  S  SHORTAGE_DAYS
--------  -  -------------
08-08-25  Y              1
08-08-26  Y              2
08-08-27  N              0
08-08-28  Y              1
08-08-29  Y              2
08-08-30  Y              3
08-08-31  N              0
08-09-01  Y              1
08-09-02  Y              2
08-09-03  N              0
08-09-04  N              0
    · Share on TwitterShare on Facebook
  • Satyaki_De
    Satyaki_De Member Posts: 7,081
    edited September 2008
    Great to see some 10g solution.

    Regards.

    Satyaki De

    Edited by: Satyaki_De on Sep 6, 2008 8:41 PM

    Typo... ;)
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    ??? MODEL is available in 10g.

    SY.
    · Share on TwitterShare on Facebook
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    Yes, the Model clause is available in 10g (r2)

    http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/expressions011.htm#sthref2682
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Alex Nuijten wrote:
    Yes, the Model clause is available in 10g (r2)
    Well originally Satyaki_De wrote
    Satyaki_De wrote:
    Great to see some *11g* solution.
    So my ??? was in response to 11g. Latter Satyaki_De edited his posting and changed 11g to 10g.

    SY.
    · Share on TwitterShare on Facebook
  • 21205
    21205 Member Posts: 6,168 Gold Trophy
    Ah.... that's what happens when you drop by late on a thread and people edit their posts... ;-)
    · Share on TwitterShare on Facebook
  • Aketi Jyuuzou
    Aketi Jyuuzou Member Posts: 1,072 Bronze Badge
    Other solution ;-) 
    with t as(
select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
select mpr_date,short_flag,
case short_flag
when 'Y' then Row_Number() over(partition by GID,short_flag order by mpr_date)
else 0 end as SHORTAGE_DAYS
from (select mpr_date,short_flag,
      count(decode(short_flag,'N',1)) over(order by mpr_date) as GID
      from t);
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    As I already stated, unless OP confirms there is a row for each date (no gaps), sample provided is "data magic".

    SY.
    · Share on TwitterShare on Facebook
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    edited September 2008
    even when gaps present replacing row_number() over (,,,) with mpr_date - first_value(mpr_date) over (...) will confirm Aketi's solution as an excellent example of sharp thinking.

    ... everyone can be a general after the battle (some old saying of ours on my behalf)

    Hats off, Aketi.

    Regards

    Etbin
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Etbin wrote:
    even when gaps present replacing row_number() over (,,,) with mpr_date - first_value(mpr_date) over (...) will confirm Aketi's solution as an excellent example of sharp thinking.

    ... everyone can be a general after the battle (some old saying of ours on my behalf)
    Well, Aketi's MODEL solution is nice, although I try to stay away from MODEL for one reason - Oracle does not provide (at least I am not aware of any) ways to cost it. Look:
    SQL> create table t as (
  2  select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  9  select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11  select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
 12  select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual
 13  );

Table created.

SQL> exec dbms_stats.gather_table_stats('SCOTT','T');

PL/SQL procedure successfully completed.

SQL> explain plan for
  2  select mpr_date,short_flag,SHORTAGE_DAYS
  3    from t
  4   model
  5   dimension by (mpr_date)
  6   measures(short_flag,0 as SHORTAGE_DAYS)
  7   rules(SHORTAGE_DAYS[any] order by mpr_date
  8       = case when short_flag[cv()] = 'Y' 
  9              then presentv(SHORTAGE_DAYS[cv()-1],SHORTAGE_DAYS[cv()-1]+1,1)
 10              else 0 end);

Explained.

SQL> set linesize 132
SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 4004708133

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    11 |   110 |     3   (0)| 00:00:01 |
|   1 |  SQL MODEL ORDERED |      |    11 |   110 |            |          |
|   2 |   TABLE ACCESS FULL| T    |    11 |   110 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------

9 rows selected.
    Compare it to:
    SQL> explain plan for
  2  select mpr_date,short_flag,
  3  case grp when 0 then 1 else 0 end +
  4  mpr_date - min(mpr_date) over(partition by grp order by mpr_date) shortage_days
  5  from (select mpr_date,short_flag,
  6        sum(decode(short_flag,'N',1)) over(order by mpr_date) as grp
  7        from t)
  8  /

Explained.

SQL> @?\rdbms\admin\utlxpls

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2825241469

-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    11 |   275 |     5  (40)| 00:00:01 |
|   1 |  WINDOW SORT         |      |    11 |   275 |     5  (40)| 00:00:01 |
|   2 |   VIEW               |      |    11 |   275 |     4  (25)| 00:00:01 |
|   3 |    WINDOW SORT       |      |    11 |   110 |     4  (25)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| T    |    11 |   110 |     3   (0)| 00:00:01 |
-----------------------------------------------------------------------------

11 rows selected.
    As you can see, SQL MODEL ORDERED does not give us any cost value. Again, I am not saying MODEL is slower/faster, just I'd like to see some costing.
    Now about replacing row_number() over (,,,) with mpr_date - first_value(mpr_date) over (...). Your suggestion will not work:
    SQL> with t as(
  2  select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  9  select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11  select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
 12  select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
 13  select mpr_date,short_flag,
 14  case short_flag
 15  when 'Y'
 16    then mpr_date - first_value(mpr_date) over(partition by GID,short_flag order by mpr_date)
 17  else 0 end as SHORTAGE_DAYS
 18  from (select mpr_date,short_flag,
 19        count(decode(short_flag,'N',1)) over(order by mpr_date) as GID
 20        from t)
 21  /

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
25-AUG-08 Y             0
26-AUG-08 Y             1
27-AUG-08 N             0
28-AUG-08 Y             0
29-AUG-08 Y             1
30-AUG-08 Y             2
31-AUG-08 N             0
01-SEP-08 Y             0
02-SEP-08 Y             1
03-SEP-08 N             0
04-SEP-08 N             0
    Reason is partitioning by both GID and short_flag. Now if we will remove it we will get correct results except within first group since there it has no N row:
    SQL> with t as(
  2  select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  9  select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11  select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
 12  select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
 13  select mpr_date,short_flag,
 14  case short_flag
 15  when 'Y'
 16    then mpr_date - first_value(mpr_date) over(partition by GID order by mpr_date)
 17  else 0 end as SHORTAGE_DAYS
 18  from (select mpr_date,short_flag,
 19        count(decode(short_flag,'N',1)) over(order by mpr_date) as GID
 20        from t)
 21  /

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
25-AUG-08 Y             0
26-AUG-08 Y             1
27-AUG-08 N             0
28-AUG-08 Y             1
29-AUG-08 Y             2
30-AUG-08 Y             3
31-AUG-08 N             0
01-SEP-08 Y             1
02-SEP-08 Y             2
03-SEP-08 N             0
04-SEP-08 N             0

11 rows selected.
    And case is absolutely not needed:
    SQL> with t as(
  2  select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  9  select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11  select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
 12  select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
 13  select mpr_date,short_flag,
 14  mpr_date - first_value(mpr_date) over(partition by GID order by mpr_date) SHORTAGE_DAYS
 15  from (select mpr_date,short_flag,
 16        count(decode(short_flag,'N',1)) over(order by mpr_date) as GID
 17        from t)
 18  /

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
25-AUG-08 Y             0
26-AUG-08 Y             1
27-AUG-08 N             0
28-AUG-08 Y             1
29-AUG-08 Y             2
30-AUG-08 Y             3
31-AUG-08 N             0
01-SEP-08 Y             1
02-SEP-08 Y             2
03-SEP-08 N             0
04-SEP-08 N             0

11 rows selected.
    And to fix first GID issue:
    SQL> with t as(
  2  select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  9  select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11  select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
 12  select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
 13  select mpr_date,short_flag,
 14  case gid when 0 then 1 else 0 end +
 15  mpr_date - first_value(mpr_date) over(partition by GID order by mpr_date) SHORTAGE_DAYS
 16  from (select mpr_date,short_flag,
 17        count(decode(short_flag,'N',1)) over(order by mpr_date) as GID
 18        from t)
 19  /

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
25-AUG-08 Y             1
26-AUG-08 Y             2
27-AUG-08 N             0
28-AUG-08 Y             1
29-AUG-08 Y             2
30-AUG-08 Y             3
31-AUG-08 N             0
01-SEP-08 Y             1
02-SEP-08 Y             2
03-SEP-08 N             0
04-SEP-08 N             0

11 rows selected.
    As you can see, we basically end up with my solution. COUNTing NOT NULLs and NULLs in inline view is same as SUMming 1s and 0s.

    SY.
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Just for fun, Aketi's model solution adjustment for gaps:
    with t as(
select to_date('8/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/6/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/7/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
select mpr_date,short_flag,mpr_date - grp_dt SHORTAGE_DAYS,grp_dt
  from t
 model
 dimension by (row_number() over(order by mpr_date) rn)
 measures(short_flag,mpr_date,mpr_date grp_dt)
 rules(
       grp_dt[any] order by rn = case
                                   when short_flag[cv()] = 'Y'
                                     then presentv(
                                                   grp_dt[cv() - 1],
                                                   grp_dt[cv() - 1],
                                                   grp_dt[cv()]
                                                  )
                                         else grp_dt[cv()]
                                       end
      )
/
    SQL> with t as(
  2  select to_date('8/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/6/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/7/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  9  select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11  select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
 12  select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual)
 13  select mpr_date,short_flag,mpr_date - grp_dt SHORTAGE_DAYS,grp_dt
 14    from t
 15   model
 16   dimension by (row_number() over(order by mpr_date) rn)
 17   measures(short_flag,mpr_date,mpr_date grp_dt)
 18   rules(
 19         grp_dt[any] order by rn = case
 20                                     when short_flag[cv()] = 'Y'
 21                                       then presentv(
 22                                                     grp_dt[cv() - 1],
 23                                                     grp_dt[cv() - 1],
 24                                                     grp_dt[cv()]
 25                                                    )
 26                                           else grp_dt[cv()]
 27                                         end
 28        )
 29  /

MPR_DATE  S SHORTAGE_DAYS GRP_DT
--------- - ------------- ---------
05-AUG-08 Y             0 05-AUG-08
06-AUG-08 Y             1 05-AUG-08
07-AUG-08 N             0 07-AUG-08
28-AUG-08 Y            21 07-AUG-08
29-AUG-08 Y            22 07-AUG-08
30-AUG-08 Y            23 07-AUG-08
31-AUG-08 N             0 31-AUG-08
01-SEP-08 Y             1 31-AUG-08
02-SEP-08 Y             2 31-AUG-08
03-SEP-08 N             0 03-SEP-08
04-SEP-08 N             0 04-SEP-08

11 rows selected.

SQL>
    SY.
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Thank you soooomuch guys!!!!! they are excellent solutions...
    The first date is the base date for the calculation...
    There are some other restrictions..
    1) if there is weekend or holiday, they need to be excluded from the shortage days count. we store holdays in a table.
    2) if there is a gap in the dates and it is non-holiday/non-weekend, we assumes that customer did not drop the file on to our site. So we consider it as not shortage (N). So the shortage count should reset to 1 from next row onwards.
    8/25/2008 Y 1
    8/26/2008 Y 2
    8/27/2008 N 0
    8/28/2008 Y 1
    8/29/2008 Y 2
    8/30/2008 -- 0 --weekend
    8/31/2008 -- 0 --weekend
    9/1/2008 -- 0 --holiday
    9/2/2008 Y 3
    ------------------------------ Gap in the dates(no matter howmany days gap), so the counter should reset to 1.
    9/5/2008 Y 1
    9/6/2008 Y 2

    I reallyappreciate your help!!!!!! Please help me how to implement this....
    · Share on TwitterShare on Facebook
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    if there is weekend or holiday
    If you are able to define easily what are the week-end days, how do you define holidays ? Any lookup table ?

    Nicolas.
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Thanks nicolus....
    Yes, we have a table which holds Holidays...
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Accepted Answer
    OK, you have a table with holiday dates, but do you store weekend/holiday rows in the main table? And if you do, what SHORT_FLAG values do they have? Can it be N? Also, why:
    9/6/2008 Y 2
    when 9/6/2008 is weekend (Saturday). I assume it is a typo and it should be:
    9/6/2008 Y 0
    Anyway, for now I will assume you do store weekend/holiday rows in the main table and they can have value N:
    with t as(
          select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
          select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
          select to_date('9/6/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual
         ),
    h as (
          select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
         )
select  mpr_date,grp,
        short_flag,
        case
          when weight = 0 then 0
          else sum(weight) over(partition by grp order by mpr_date)
        end shortage_days
  from  (
         select  mpr_date,
                 short_flag,
                 sum(
                     case
                       when short_flag = 'N' then 1
                       when mpr_date_gap > 1 then 1
                       else 0
                     end
                    ) over(order by mpr_date) as grp,
                 case
                   when to_char(mpr_date,'d','nls_date_language=american') in ('1','7') then 0
                   when 1 = (select 1 from h where h_date = mpr_date) then 0
                   when short_flag = 'N' then 0
                   else 1
                 end weight
           from (
                 select  mpr_date,
                         short_flag,
                         mpr_date - lag(mpr_date) over(order by mpr_date) mpr_date_gap
                   from  t
                )
        )
/
    SQL> with t as(
  2            select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3            select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4            select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5            select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6            select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7            select to_date('8/30/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8            select to_date('8/31/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  9            select to_date('9/1/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10            select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 11            select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 12            select to_date('9/6/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual
 13           ),
 14      h as (
 15            select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
 16           )
 17  select  mpr_date,grp,
 18          short_flag,
 19          case
 20            when weight = 0 then 0
 21            else sum(weight) over(partition by grp order by mpr_date)
 22          end shortage_days
 23    from  (
 24           select  mpr_date,
 25                   short_flag,
 26                   sum(
 27                       case
 28                         when short_flag = 'N' then 1
 29                         when mpr_date_gap > 1 then 1
 30                         else 0
 31                       end
 32                      ) over(order by mpr_date) as grp,
 33                   case
 34                     when to_char(mpr_date,'d','nls_date_language=american') in ('1','7') then 0
 35                     when 1 = (select 1 from h where h_date = mpr_date) then 0
 36                     when short_flag = 'N' then 0
 37                     else 1
 38                   end weight
 39             from (
 40                   select  mpr_date,
 41                           short_flag,
 42                           mpr_date - lag(mpr_date) over(order by mpr_date) mpr_date_gap
 43                     from  t
 44                  )
 45          )
 46  /

MPR_DATE         GRP S SHORTAGE_DAYS
--------- ---------- - -------------
25-AUG-08          0 Y             1
26-AUG-08          0 Y             2
27-AUG-08          1 N             0
28-AUG-08          1 Y             1
29-AUG-08          1 Y             2
30-AUG-08          1 Y             0
31-AUG-08          1 Y             0
01-SEP-08          1 Y             0
02-SEP-08          1 Y             3
05-SEP-08          2 Y             1
06-SEP-08          2 Y             0

11 rows selected.
    SY.
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Sorry Soloman....the main table will not have any row for holidays and weekends
    it was my mistake...I should not mention those rows in the sample data above.
    · Share on TwitterShare on Facebook
  • Etbin
    Etbin Member Posts: 8,968 Gold Crown
    Thanks for your detailed elaboration it's just that with ... in over clauses I meant to say "appropriate" not "same as Aketi's".
    I don't have database access on weekends so I cannot do any tests and I had to leave (owing something to the family too) and although being sure the same partitioning won't work I didn't dare to write down the clauses without testing, the same for providing a complete solution (I didn't have the time too).
    I also try not to use the model clause unless absolutely necessary (I tried some ten comparisons between model/non model versions - mainly attempts to get used to the model clause and all model versions were slower than the non model versions). But sometimes (at least by my judgement) there is only an iterative solution available and then I prefer the model clause to the procedural (loop ... end loop) approach.

    Regards

    Etbin
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    Sorry Solomon :( Here is the revised correct sample data
    with t as(
    select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
    select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('9/9/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
    select to_date('9/10/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual
    ),
    h as (
    select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
    )

    THANK YOU VERY MUCH!!!!!!
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    dd_ram wrote:
    Here is the revised correct sample data
    OK. And what output that sample should produce?

    SY.
    · Share on TwitterShare on Facebook
  • Nicolas Gasparotto
    Nicolas Gasparotto Member Posts: 25,514 Silver Crown
    edited September 2008
    Ok, here below a try : 
    SQL> with t as(
  2           select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3           select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4           select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5           select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6           select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7           select to_date('9/3/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8           select to_date('9/8/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  9           select to_date('9/4/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10           select to_date('9/9/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual
 11          ),
 12     h as (
 13           select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
 14          )
 15  select mpr_date, decode(orig,1,short_flag) short_flag, flag
 16  from   (select mpr_date, short_flag,orig,
 17                 row_number() over (order by mpr_date)-nvl(last_value(c3 ignore nulls) over (order by mpr_date),0) flag
 18          from   (select mpr_date, short_flag,
 19                         case when to_char(mpr_date,'fmday') in ('saturday','sunday')
 20                              or   short_flag = 'N'
 21                              then row_number() over (order by mpr_date) end c3,
 22                         orig
 23                  from (select mpr_date, short_flag, 1 orig from t
 24                        union
 25                        select h_date, 'N', 2 orig from h
 26                        union
 27                        select mpr_date-1, 'N', 0 orig from t where mpr_date-1 not in (select mpr_date from t))))
 28  where orig!=0
 29  order by mpr_date;

MPR_DATE  S       FLAG
--------- - ----------
25-AUG-08 Y          1
26-AUG-08 Y          2
27-AUG-08 N          0
28-AUG-08 Y          1
29-AUG-08 Y          2
01-SEP-08            0
03-SEP-08 Y          1
04-SEP-08 Y          2
08-SEP-08 Y          1
09-SEP-08 Y          2

10 rows selected.
    I changed the input data sample for testing purpose, but here same query with your data : 
    SQL> with t as(
  2  select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3  select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4  select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5  select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6  select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7  select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8  select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  9  select to_date('9/9/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10  select to_date('9/10/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual
 11  ),
 12  h as (
 13  select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
 14  )
 15  select mpr_date, decode(orig,1,short_flag) short_flag,flag
 16  from   (select mpr_date, short_flag,orig,
 17                 row_number() over (order by mpr_date)-nvl(last_value(c3 ignore nulls) over (order by mpr_date),0) flag
 18          from   (select mpr_date, short_flag,
 19                         case when to_char(mpr_date,'fmday') in ('saturday','sunday')
 20                              or   short_flag = 'N'
 21                              then row_number() over (order by mpr_date) end c3,
 22                         orig
 23                  from (select mpr_date, short_flag, 1 orig from t
 24                        union
 25                        select h_date, 'N', 2 orig from h
 26                        union
 27                        select mpr_date-1, 'N', 0 orig from t where mpr_date-1 not in (select mpr_date from t))))
 28  where orig!=0
 29  order by mpr_date;

MPR_DATE  S       FLAG
--------- - ----------
25-AUG-08 Y          1
26-AUG-08 Y          2
27-AUG-08 N          0
28-AUG-08 Y          1
29-AUG-08 Y          2
01-SEP-08            0
02-SEP-08 Y          1
05-SEP-08 Y          1
09-SEP-08 Y          1
10-SEP-08 N          0

10 rows selected.

SQL>
    Nicolas.
    Nicolas.

    small changes to make null the short_flag when holiday
    Edited by: N. Gasparotto on Sep 7, 2008 10:21 PM
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    Is this what you expect:
    SQL> with t as(
  2            select to_date('8/25/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  3            select to_date('8/26/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  4            select to_date('8/27/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual union
  5            select to_date('8/28/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  6            select to_date('8/29/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  7            select to_date('9/2/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  8            select to_date('9/5/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
  9            select to_date('9/9/2008','mm/dd/yyyy') mpr_date,'Y' short_flag from dual union
 10            select to_date('9/10/2008','mm/dd/yyyy') mpr_date,'N' short_flag from dual
 11           ),
 12      w as (
 13            select  mpr_date,
 14                    to_char(null) short_flag,
 15                    0 weight
 16              from  (
 17                     select  min_mpr_date + level - 1 mpr_date,
 18                             to_char(null) short_flag
 19                       from  (
 20                              select  min(mpr_date) min_mpr_date,
 21                                      max(mpr_date) max_mpr_date
 22                                from  t
 23                             )
 24                       connect by level <= max_mpr_date - min_mpr_date + 1
 25                    )
 26              where to_char(mpr_date,'d','nls_date_language=american') in ('1','7')
 27            ),
 28      h as (
 29            select to_date('9/1/2008','mm/dd/yyyy') h_date from dual
 30           ),
 31      e as (
 32             select  mpr_date,
 33                     short_flag,
 34                     case
 35                       when short_flag = 'N' then 0
 36                       else 1
 37                     end weight
 38               from  t
 39            union all
 40             (
 41               select  *
 42                 from  w
 43              union
 44               select  h_date,
 45                       to_char(null) short_flag,
 46                       0 weight
 47                 from  h
 48             )
 49           )
 50  select  mpr_date,
 51          short_flag,
 52          case
 53            when weight = 0 then 0
 54            else sum(weight) over(partition by grp order by mpr_date)
 55          end shortage_days
 56    from  (
 57           select  mpr_date,
 58                   short_flag,
 59                   sum(
 60                       case
 61                         when short_flag = 'N' then 1
 62                         when mpr_date_gap > 1 then 1
 63                         else 0
 64                       end
 65                      ) over(order by mpr_date) as grp,
 66                   weight
 67             from (
 68                   select  mpr_date,
 69                           short_flag,
 70                           weight,
 71                           mpr_date - lag(mpr_date) over(order by mpr_date) mpr_date_gap
 72                     from  e
 73                  )
 74          )
 75  / 

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
25-AUG-08 Y             1
26-AUG-08 Y             2
27-AUG-08 N             0
28-AUG-08 Y             1
29-AUG-08 Y             2
30-AUG-08               0
31-AUG-08               0
01-SEP-08               0
02-SEP-08 Y             3
05-SEP-08 Y             1
06-SEP-08               0

MPR_DATE  S SHORTAGE_DAYS
--------- - -------------
07-SEP-08               0
09-SEP-08 Y             1
10-SEP-08 N             0

14 rows selected.
    SY.
    · Share on TwitterShare on Facebook
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    N. Gasparotto wrote:
    Ok, here below a try :
    02-SEP-08 Y 1
    Nicolas,
    if I understand OP correctly, 02-SEP-08 should return
    02-SEP-08 Y          3
    The nearest short_flag = 'N' is '8/27/2008'. Both '8/28/2008' and '8/29/2008' have short_flag = 'Y'. '8/30/2008' and '8/31/2008' are weekend '9/1/2008' is a holiday, so no gap in non-weekend, non-holiday dates. Therefore on '9/2/2008' shortage days is not reset but keeps accumulating. Actually, it is shown in one of OP's posts:
    dd_ram wrote:
    Thank you soooomuch guys!!!!! they are excellent solutions...
    The first date is the base date for the calculation...
    There are some other restrictions..
    1) if there is weekend or holiday, they need to be excluded from the shortage days count. we store holdays in a table.
    2) if there is a gap in the dates and it is non-holiday/non-weekend, we assumes that customer did not drop the file on to our site. So we consider it as not shortage (N). So the shortage count should reset to 1 from next row onwards.
    8/25/2008 Y 1
    8/26/2008 Y 2
    8/27/2008 N 0
    8/28/2008 Y 1
    8/29/2008 Y 2
    8/30/2008 -- 0 --weekend
    8/31/2008 -- 0 --weekend
    9/1/2008 -- 0 --holiday
    9/2/2008 Y 3
    ------------------------------ Gap in the dates(no matter howmany days gap), so the counter should reset to 1.
    9/5/2008 Y 1
    9/6/2008 Y 2
    SY.
    · Share on TwitterShare on Facebook
  • dd_ram
    dd_ram Member Posts: 224
    You are right Solomon. 09/02/08 should be 3. Also I don't need to display weekends and holidays in the output.
    Output data consists of same number of rows of original table T. I think the query should be more simple now.

    Thanks Soomuch Solomon and N. Gasparotto...Really appreciate your help...
    · Share on TwitterShare on Facebook
This discussion has been closed.