How to get start/end date from single date column -oracle

Question

I've seen some brilliant answers on here and I am in need of a fix. I do not want to create table or ETL just yet and I was hoping to create a simple database view users could access to test first.

A table has an item#, item_ticket_color#, maint_date, and other columns tick_col is updated sometimes but other columns in the table are as well. If some other column is updated or changed tick_col iterates. I need to filter through this data to get the below expected outcome and need some help trying to figure out how I might do this. I am looking for straight SQL if possible

ITEM......................TICK_COL................. MAINT_DATE

21524804....................RIBG...................1/1/0001  
21524804....................RIBG...................6/15/2008 6:52:57 AM
21524804....................RIBG...................6/25/2008 11:31:03 AM
21524804....................RIBG...................6/28/2008 4:12:21 AM
21524804....................RIWH...................9/20/2008 6:36:24 AM
21524804....................RIGR...................9/23/2008 6:36:44 AM
21524804....................RIGR...................9/30/2008 6:37:42 AM
21524804....................RIWH...................10/31/2008 6:37:27 AM
21524804....................RIWH...................11/1/2008 6:36:41 AM
21524804....................RIGR...................3/11/2009 6:01:43 PM
21524804....................RIGR...................7/28/2009 6:37:11 AM
21524804....................RIGR...................10/8/2009 6:37:00 AM
21524804....................RIBS...................11/20/2009 6:37:58 AM
21524804....................RIBS...................5/18/2010 6:37:07 AM
21524804....................RIBS...................9/16/2010 6:38:11 AM
21524804....................RIBS...................8/13/2012 10:39:44 AM
21524804....................RIBS...................3/12/2013 6:46:08 AM
21524804....................RIBS...................3/17/2013 9:25:31 AM
21524804....................RIBS...................3/27/2013 6:52:57 AM
21524804....................RIBS...................7/25/2013 6:41:51 AM

I am Expecting to see this below, where it shows the start and end date for each of the scenarios where the ticket color changed.:

21524804.....RIBG.....10101........20080919

21524804.....RIWH.....20080920.....20080922

21524804.....RIGR.....20080923.....20081030

21524804.....RIWH.....20081031.....20090310

21524804.....RIGR.....20090311.....20091119

21524804.....RIBS.....20091120.....20130725

SQLFiddle here.

here is the new code, based on Jasti's contribution, I made some additional changes but this is exactly what I wanted

 SELECT item,
       tick_col,
       from_dt,
       CASE
          WHEN LEAD (from_dt) OVER (PARTITION BY item ORDER BY from_dt) - 1
                  IS NULL
          THEN
             SYSDATE
          ELSE
             LEAD (from_dt) OVER (PARTITION BY item ORDER BY from_dt) - 1
       END
          TO_DATE
  FROM (  SELECT ITEM,
                 TICK_COL,
                 MIN (MAINT_DATE) AS from_dt,
                 MAX (MAINT_DATE) AS to_dt
            FROM (SELECT SUM (start_of_group) OVER (ORDER BY maint_date) AS sm,
                         ITEM,
                         TICK_COL,
                         maint_date
                    FROM (SELECT ITEM,
                                 TICK_COL,
                                 maint_date,
                                 CASE
                                    WHEN LAG (TICK_COL, 1, TICK_COL)
                                            OVER (ORDER BY maint_date) =
                                            TICK_COL
                                    THEN
                                       0
                                    ELSE
                                       1
                                 END
                                    start_of_group
                            FROM mytable))
        GROUP BY ITEM, TICK_COL, sm
        ORDER BY sm)

I have added solution and updated SQL Fiddle link. Comment if that doesn't work for you. — Jasti, Jul 25 '13 at 22:49
thanks Jasti, absolutely brilliant, i had to add something to it to get what I wanted but it works — C M, Jul 25 '13 at 23:49
Post the solution as an answer and mark it that way. Don't update the Question with the solution since it is supposed to be a **Question** — Shaihi, Jul 26 '13 at 08:28
@CM Your solution returns `July, 26` for last date instead of `July, 25` as in example dataset from question. [(SQLFiddle)](http://www.sqlfiddle.com/#!4/f87ef/168) — ThinkJet, Jul 26 '13 at 11:07
my solution, might not be the best solution, I only made small adjustments and it actually did not end up suiting my needs because it wouldn't work in the context of a database view. — C M, Jul 26 '13 at 13:52

Jasti · Answer 1 · 2013-07-25T22:43:50.347

1

I have added the query for your first part too. Following query should solve both the requirements. This query groups the items till the TICK_COL changes and calculate max and min dates for that particular group

select ITEM, TICK_COL, MIN(MAINT_DATE) AS from_dt, MAX(MAINT_DATE) AS to_dt from
(
  select sum(start_of_group) over (order by maint_date) as sm, ITEM, TICK_COL, maint_date from 
  (
    select  ITEM, TICK_COL,maint_date,
      case
        when 
          lag(TICK_COL,1,TICK_COL) over (order by maint_date)  = TICK_COL then 0
          else 1
         end start_of_group
    from  mytable 
  )
) group by ITEM, TICK_COL,sm order by sm

Updated SQL Fiddle here

edited Jul 25 '13 at 22:43

answered Jul 25 '13 at 21:30

Jasti

927
5
14

I did SQL Fiddle on my data, and I used your query, the start and end dates are off, please look at my expected results.... – C M Jul 25 '13 at 23:27
1

@CM - if this helped you, then at least upvote it. If this is the answer mark it as such. – Shaihi Jul 26 '13 at 08:27

Rajesh Chamarthi · Answer 2 · 2013-07-26T01:37:44.157

Try and see if this query works.

   select item,
           tick_col,
           MAINT_DATE maint_start_date,
           (case when tick_col <> next_tick_col then next_maint_date end) maint_end_date
    from (
      select item,
             tick_col,
             lead(MAINT_DATE) over (partition by item 
                                    order by MAINT_DATE asc) next_maint_date,
             lead(tick_col) over (partition by item 
                                  order by MAINT_DATE asc) next_tick_col,
      from   my_table
) where tick_col <> next_tick_col;

score 0 · Answer 3 · answered Jul 25 '13 at 20:52

You can use min and max to get the second part. I am not sure what you are looking for in the first part other than a standard select.

SELECT ITEM, TICK_COL, MIN(MAINT_DATE) AS startDate, MAX(MAINT_DATE) AS endDate
FROM yourTableName
GROUP BY ITEM, TICK_COL

score 0 · Answer 4 · edited May 23 '17 at 11:57

Variant based on detecting start and end of periods and then calculate desired dates. (Nearly same solution can be found here.)

SQLFiddle

with marked_set as (
  -- mark start and end of each maintenance period
  select
    item,
    tick_col,
    maint_date,
    decode( nvl(prev_tick, tick_col||'x'), tick_col, 0, 1 ) is_start,
    decode( nvl(next_tick, tick_col||'x'), tick_col, 0, 1 ) is_end
  from (
    select
      item,
      tick_col,
      maint_date,
      lag(tick_col)  over (partition by item order by maint_date) prev_tick,
      lead(tick_col) over (partition by item order by maint_date) next_tick
    from
      mytable
  )
),
boundary_set as (
  -- Leave only start and end of each period
  -- and get dates from previous and next lines
  select
    item,
    tick_col,
    maint_date,
    is_start,
    is_end,
    ( lag(maint_date) over (
        partition by item order by maint_date)
    )                                          prev_maint_date,
    ( lead(maint_date,1,maint_date) over (
        partition by item order by maint_date
      )
    )                                          next_maint_date
  from
    marked_set
  where
    1 in (is_start, is_end)
),
maintenance_sequence as (
  -- Calculate start and end dates for each maintenance period
  select distinct
    item,
    tick_col,
    decode( is_start,
      1, maint_date,
      prev_maint_date
    )                     start_date,
    decode( is_end,
      1, next_maint_date,
      ( lead(next_maint_date,1,next_maint_date)
         over (partition by item order by maint_date)
      )
    )                    end_date
  from
    boundary_set
)
select
  -- Final formatting and ordering.
  -- For end date of last period get exact date, for others get previous date
  -- because it's date of start for maintenance of next item.
  item,
  tick_col,
  to_char(start_date, 'yyyymmdd') maint_start,
  decode( (lead(end_date) over (partition by item order by start_date)),
    null, to_char(end_date, 'yyyymmdd'),
    to_char(end_date - 1, 'yyyymmdd')
  )
   maint_end
from
  maintenance_sequence
order by
  maint_start

How to get start/end date from single date column -oracle

4 Answers4