Results 1 to 6 of 6
  1. #1
    Join Date
    Sep 2011
    Posts
    220

    Answered: minimum date function

    Select case when in_dt < current date

    then 'a'
    when in_dt > current date
    then 'b'
    else 'c'
    end in_type
    ,in_dt
    ,in_misc
    from inv_table

    Code:
    input table
           in_dt           in_misc
          2012-12-31      pending
          2015-08-07      ready
          2015-08-21      progress
          2015-09-01      progress 
          2015-11-13      progress
    Code:
    expected result set
    
    in_type          in_dt        in_misc
      c            2012-12-31      pending
      a            2015-08-07      ready
      b            2015-08-21      progress
      b            2015-09-01      progress 
      b            2015-11-13      progress

  2. Best Answer
    Posted by mark.b

    "Try this:
    Code:
    select in_dt, case when in_dt > current date then 'b' when rn=1 then 'a' else 'c' end in_type 
    from (
    select 
      in_dt
    , rownumber() over (order by case when in_dt < current date then in_dt end desc nulls last) rn
    from inv_table
    )
    order by in_dt
    "


  3. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Assuming that "current date" is 2015-08-07 for your expected result set, then your first clause in the case statement should be "in_dt = current date".

    Andy

  4. #3
    Join Date
    Sep 2011
    Posts
    220
    Thanks for the reply.
    If more lesser IN_DT records exists less then current date means the closest record will identify as 'A'
    remaining all lesser records will identify as 'C'
    Ex:
    current date is 2015-08-12.In our input records lesser dates records are

    IN_DT IN_MISC
    2012-12-31 PENDING
    2015-08-07 READY

    in those records the closest to the current date record 2015-08-07 will show In_TYPE as 'A'
    next record 2012-12-31 will show as 'C'

  5. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    If that is the case then the first CASE clause should be "in_dt = (select max(in_dt) from MyTable where ind_dt < current date)"

    Andy

  6. #5
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Try this:
    Code:
    select in_dt, case when in_dt > current date then 'b' when rn=1 then 'a' else 'c' end in_type 
    from (
    select 
      in_dt
    , rownumber() over (order by case when in_dt < current date then in_dt end desc nulls last) rn
    from inv_table
    )
    order by in_dt
    Regards,
    Mark.

  7. #6
    Join Date
    Sep 2011
    Posts
    220
    Thanks for all

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •