locked
How can I get the last previous date? RRS feed

  • Question

  • I want to make a SQL query that gets todays date and the most previous date from a date column. So if I have three records in my database that have the following dates:

    March 8, 2012
    March 2,  2012
    December 8, 2011

    I want the SQL querty to return all records for March 8, 2012 and March 2, 2012 (most previous date). How can I do this?

    Thank You

    Thursday, March 8, 2012 8:12 PM

Answers

  • SELECT TOP 2 Date FROM @Table ORDER BY Date DESC

    should give you:
    March 8, 2012
    March 2, 2012

    But asumes that those are dateimte values, not Strings.

    Edit: (now with compelte example)

    DECLARE @Table TABLE(Date DATE);
    INSERT INTO @Table Values ('08-03-2012');
    INSERT INTO @Table Values ('02-03-2012');
    INSERT INTO @Table Values ('08-12-2011');
    
    SELECT * FROM @Table;
    
    SELECT TOP 2 Date FROM @Table ORDER BY Date DESC

    Results in:
    2012-03-08
    2012-03-02
    2011-12-08

    and

    2012-03-08
    2012-03-02

    • Proposed as answer by Vijaya Reddy PR Thursday, March 8, 2012 8:26 PM
    • Edited by Christopher84 Thursday, March 8, 2012 8:27 PM
    • Marked as answer by n3xtgen Thursday, March 8, 2012 9:17 PM
    Thursday, March 8, 2012 8:22 PM

All replies

  • SELECT TOP 2 Date FROM @Table ORDER BY Date DESC

    should give you:
    March 8, 2012
    March 2, 2012

    But asumes that those are dateimte values, not Strings.

    Edit: (now with compelte example)

    DECLARE @Table TABLE(Date DATE);
    INSERT INTO @Table Values ('08-03-2012');
    INSERT INTO @Table Values ('02-03-2012');
    INSERT INTO @Table Values ('08-12-2011');
    
    SELECT * FROM @Table;
    
    SELECT TOP 2 Date FROM @Table ORDER BY Date DESC

    Results in:
    2012-03-08
    2012-03-02
    2011-12-08

    and

    2012-03-08
    2012-03-02

    • Proposed as answer by Vijaya Reddy PR Thursday, March 8, 2012 8:26 PM
    • Edited by Christopher84 Thursday, March 8, 2012 8:27 PM
    • Marked as answer by n3xtgen Thursday, March 8, 2012 9:17 PM
    Thursday, March 8, 2012 8:22 PM
  • Hi Christopher,

    The TOP 2 works only if the table has only a single row for a date. If the table has multiple rows in a date then use the following query:

    DECLARE @tblDates TABLE (ColID INT, ColDate DATETIME)
    INSERT INTO @tblDates VALUES (1, '20120308')
    INSERT INTO @tblDates VALUES (2, '20120302')
    INSERT INTO @tblDates VALUES (3, '20111208')
    INSERT INTO @tblDates VALUES (4, '20120302')
    ;WITH DateCTE
    AS
    (
    SELECT *, DENSE_RANK() OVER(ORDER BY ColDate DESC) AS DateRank FROM @tblDates 
    ) SELECT * FROM DateCTE WHERE DateRank IN (1, 2)

    The DENSE_RANK function assign a rank number for each date and this number is used to retireve the desired date rows.

    - Krishnakumar S

    • Proposed as answer by sqlrockz Thursday, March 8, 2012 8:41 PM
    Thursday, March 8, 2012 8:33 PM
  • Hi Christopher,

    The TOP 2 works only if the table has only a single row for a date. If the table has multiple rows in a date then use the following query:

    Sorry, but it works perfectly even then:

    DECLARE @tblDates TABLE (ColID INT, ColDate DATE)
    INSERT INTO @tblDates VALUES (1, '20120308')
    INSERT INTO @tblDates VALUES (2, '20120302')
    INSERT INTO @tblDates VALUES (3, '20111208')
    INSERT INTO @tblDates VALUES (4, '20120302')
    
    SELECT * FROM @tblDates;
    
    SELECT TOP 2 ColDate FROM @tblDates ORDER BY ColDate DESC

    gives:
    1    2012-03-08
    2    2012-03-02
    3    2011-12-08
    4    2012-03-02

    and

    2012-03-08
    2012-03-02

    Thursday, March 8, 2012 8:36 PM
  • I think you could have meant this scenario (highest Date is twice in the table):
    DECLARE @tblDates TABLE (ColID INT, ColDate DATE)
    INSERT INTO @tblDates VALUES (1, '20120308')
    INSERT INTO @tblDates VALUES (2, '20120308')
    INSERT INTO @tblDates VALUES (3, '20111208')
    INSERT INTO @tblDates VALUES (4, '20120302')
    Wich I solved by adding DISTINCT:
    SELECT * FROM @tblDates;
    
    SELECT DISTINCT TOP 2 ColDate FROM @tblDates ORDER BY ColDate DESC

    Thursday, March 8, 2012 8:42 PM
  • Hi Christopher,

    Yes

    SELECT TOP 2 ColDate FROM @tblDates ORDER BY ColDate DESC

    gives you the two recent dates. However the OP needs all the rows for these two dates from the table. To return these detail you have to use either a subquery based solution using your TOP query or the method using ranking function similar to the one I've provided.

    - Krishnakumar S

    Thursday, March 8, 2012 8:46 PM

  • SELECT TOP 2 ColDate FROM @tblDates ORDER BY ColDate DESC

    gives you the two recent dates. However the OP needs all the rows for these two dates from the table. To return these detail you have to use either a subquery based solution using your TOP query or the method using ranking function similar to the one I've provided.

    - Krishnakumar S

    Oh right, the detail as in the last sentence. Compared your's wth this:

    SELECT * FROM @tblDates WHERE ColDate IN(
    	SELECT DISTINCT TOP 2 ColDate FROM @tblDates ORDER BY ColDate DESC);
    And according to the execution plan your needs only 4/5 of the time.
    Thursday, March 8, 2012 8:55 PM
  • You got the point. In most situations solutions using the window functions outperform other conventional methods.

    - Krishnakumar S

    Thursday, March 8, 2012 8:59 PM
  • Thanks everyone. I just have one more question. I have created two views:

    create view with top dates
    CREATE VIEW topDates AS
    select DISTINCT TOP 3 replace(CONVERT(VARCHAR(20),date,111),'-','/') AS dates from CSAResults.dbo.Details


    create view dateTwo
    select *
    from (select ROW_NUMBER() over (order by dates desc) as srNo, dates
    		from topDates)
    		AS employee
    		WHERE srNo=2

    And now I want to select * from my DB where a column is equal to the 'dates' column from the view 'dateTwo'

    select buildNumber
    from CSAResults.dbo.Details
    where buildNumber LIKE '%Main '+ (SELECT dates FROM dateTwo) + '%'


    But this returns nothing.

    Thanks

    Edit: Fixed. Working Now :)

    • Edited by n3xtgen Friday, March 9, 2012 5:17 PM
    Thursday, March 8, 2012 9:20 PM