Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    67

    Unanswered: Can someone convert this query into Oracle

    This is the Requirement that i have

    I have a Table that has a Date Field.
    I need to have 6 Records displayed from this.

    Here are the conditions

    1) Always get the First Date
    2) Always get the Latest Date
    3) Always get the Latest Date 1 (it could be the same month or the previous month)
    4) Get the Dates for the Last 3 Month ends (Month End meaning 29 Feb, 31 March)
    5) If 4 does not satisfy my 6 records condition get the previous 3 records of the Latest Date 1
    -----------------------------------------------------------------------------------
    I have the Query in SQL SERVER.
    Can i get the equivalent of it in Oracle


    select TOP 6 dte from
    (
    select ID = '999999999', dte = min(dte) from dbo.tst
    union
    select ID = '8' + convert(char(8),dte,112), dte from (select TOP 2 dte from dbo.tst order by dte desc ) A
    where dte != ( select min(dte) from dbo.tst )
    union
    select ID = '7' + convert(char(8),dte,112), dte from dbo.tst
    where dte = ( select dateadd(dd,-1,left(convert(char(8),max(dte),112),6)+'01') from dbo.tst )
    and dte != ( select min(dte) from dbo.tst )
    and dte not in ( select TOP 2 dte from dbo.tst order by dte desc )
    union
    select ID = '6' + convert(char(8),dte,112), dte from dbo.tst
    where dte = ( select dateadd(dd,-1,left(convert(char(8),dateadd(mm,-1,max(dte)),112),6)+'01') from dbo.tst )
    and dte != ( select min(dte) from dbo.tst )
    and dte not in ( select TOP 2 dte from dbo.tst order by dte desc )
    union
    select ID = '5' + convert(char(8),dte,112), dte from dbo.tst
    where dte = ( select dateadd(dd,-1,left(convert(char(8),dateadd(mm,-2,max(dte)),112),6)+'01') from dbo.tst )
    and dte != ( select min(dte) from dbo.tst )
    and dte not in ( select TOP 2 dte from dbo.tst order by dte desc )
    union
    select ID = '4' + convert(char(8),dte,112), dte from ( select TOP 6 dte from dbo.tst order by dte desc ) A
    where dte != ( select min(dte) from dbo.tst )
    and dte not in ( select TOP 2 dte from dbo.tst order by dte desc )
    and dte != ( select dateadd(dd,-1,left(convert(char(8),max(dte),112),6)+'01') from dbo.tst )
    and dte != ( select dateadd(dd,-1,left(convert(char(8),dateadd(mm,-1,max(dte)),112),6)+'01') from dbo.tst )
    and dte != ( select dateadd(dd,-1,left(convert(char(8),dateadd(mm,-2,max(dte)),112),6)+'01') from dbo.tst )

    ) A order by A.ID desc

  2. #2
    Join Date
    Jul 2003
    Posts
    2,296
    i thought we already solved this in an earlier post?
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  3. #3
    Join Date
    Jan 2004
    Posts
    67
    the problem did not get solved.
    I was concentrating on something else

  4. #4
    Join Date
    Jan 2004
    Posts
    67
    Hey,

    Sorry, i was involved in some other work.
    Could not concentrate on this.

    If you had already given a solution for this, can you please send it again.

    I might have missed it.

    Sorry for the Trouble

    Thanks
    Shankar

Posting Permissions

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