Forum Stats

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

Discussions

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.

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.
«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/
  • 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.
  • 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
  • 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... ;)
  • Solomon Yakobson
    Solomon Yakobson Member Posts: 18,393 Black Diamond
    ??? MODEL is available in 10g.

    SY.
  • 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
  • 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.
  • 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... ;-)
  • 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);
  • 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.
  • 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
  • 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.
  • 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.
  • 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....
  • 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.
  • dd_ram
    dd_ram Member Posts: 224
    Thanks nicolus....
    Yes, we have a table which holds Holidays...
  • 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.
  • 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.
  • 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
  • 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!!!!!!
  • 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.
  • 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
  • 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.
  • 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.
  • 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...
This discussion has been closed.