MaxDate and Second to max date

  • Hi,

    I have written a query to get the Max Date for each row. Now I need to find the second to max date from the table, but I am unable to get that...

    I just want last second maxdate from a table as i have to use it in my where clause..Is there any way to get that... using query

    Thanks in advance

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Use row_number().

    ;with TopDates as

    (select row_number() over (order by DateColumn desc) as Row, *

    from dbo.MyTable)

    select *

    from TopDates

    where Row<=2;

    Or use Top (2).

    select top 2 *

    from dbo.MyTable

    order by DateColumn desc;

    Depends on what you want to do with the data.

    - Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
    Property of The Thread

    "Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

  • Thanks, but I have many records for each date, I cant use this.

    For ex :

    10/23/09 -i have 1000 records

    10/22/09 - i have 3000 records

    I need max date -1 , but want to exclude holidays and weekends.

    Thanks

    [font="Comic Sans MS"]
    ---------------------------------------------------

    Thanks [/font]

  • Do you have a calendar table of some sort that designates holidays and/or weekends?

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • How about using a group by and row_number in a join?

    CREATE TABLE #DateTable(Date1 datetime)

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-26')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-26')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-25')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-25')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-25')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-24')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-23')

    INSERT INTO #DateTable ( Date1 ) VALUES ( '2009-10-22')

    SELECT *

    FROM #DateTable dt

    JOIN (SELECT

    Date1,

    ROW_NUMBER() OVER (ORDER BY date1 DESC) AS RowNumber

    FROM #DateTable dt1

    GROUP BY Date1

    ) dt2

    ON dt.Date1 = dt2.Date1

    AND dt2.RowNumber = 2

    DROP TABLE #DateTable

  • Is this what you're looking for?

    -- the ctes get you the date

    ;with cte1 (DateColumn) as (select distinct dateadd(day,datediff(day,0,DateColumn,0) from dbo.MyTable)

    ,cte2 (DateColumn,rowID) as (select DateColumn,row_number() over(order by DateColumn) from cte1

    -- WHERE (omit holidays and weekends))

    ,cte3 (DateColumn1,DateColumn2) as (select DateColumn, dateadd(day,1,DateColumn) from cte2 where rowID = 2)

    -- this query gets you all rows for that date

    select *

    from dbo.MyTable m

    cross join cte3 c

    where M.dateColumn >= c.DateColumn1 and m.dateColumn < c.DateColumn2

    Warning: Above code was typed freehand and untested.

    __________________________________________________

    Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
    Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

  • If you put a partition by on your the row_number function it will return the same row_number for the same dates, assuming you don't store the time portion of the date.

    Can you post some sample data and desired output from the sample data?

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply