locked
Select previous small date from Column_Date where Inputdate is not available RRS feed

  • Question

  • User-1068576637 posted
    Column_Date
    12/2/2014
    12/3/2014
    12/4/2014
    12/5/2014
    12/8/2014
    12/9/2014

    Input Date  : 12/6/2014

    Result :  12/5/2014

    Thursday, December 7, 2017 8:45 AM

Answers

  • User753101303 posted

    Hi,

    Try :

    SELECT TOP 1 * FROM MyTable WHERE Colum_Date<=@Value ORDER BY Column_Date DESC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 7, 2017 9:12 AM

All replies

  • User-1716253493 posted

    Your question is not clear, please elaborate.

    Maybe something like IsNull(@input, select min(Column_Date) from yourtable)

    Thursday, December 7, 2017 8:54 AM
  • User-1068576637 posted

    I have 6 records in a mytable .

    Id Column_Date
    1 12/2/2014
    2 12/3/2014
    3 12/4/2014
    4 12/5/2014
    5 12/8/2014
    6 12/9/2014

    Select * from my table where Column_Date ='12/07/2014'

    since Column_Date '12/07/2014'  is not available it should select the previous min date of  records .

    Id Column_Date
    4 12/5/2014

    hope it is clear.

    Thursday, December 7, 2017 9:08 AM
  • User753101303 posted

    Hi,

    Try :

    SELECT TOP 1 * FROM MyTable WHERE Colum_Date<=@Value ORDER BY Column_Date DESC

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Thursday, December 7, 2017 9:12 AM
  • User-1068576637 posted

    It is working fine , any other method is available. i want both previous date and next date .

    Example

    I have 6 records in a mytable .

    Id Column_Date
    1 12/2/2014
    2 12/3/2014
    3 12/4/2014
    4 12/5/2014
    5 12/8/2014
    6 12/9/2014

    Select * from my table where Column_Date ='12/07/2014'

    since Column_Date '12/07/2014'  is not available it should select the previous min date of  records .

    Id Column_Date
    4 12/5/2014

    Second requirement

    Select * from my table where Column_Date ='12/07/2014'

     select the next max date of  records .

    Id Column_Date
    5 12/8/2014
    Thursday, December 7, 2017 9:41 AM
  • User753101303 posted

    And if you have an exact match you still want just that row ? Are you using stored procedures or do you prefer to have a single SQL query which might be slightly more complex ?

    Thursday, December 7, 2017 10:14 AM
  • User-1068576637 posted

    If i have exact match then same row is fine, where no exact match then it should find.

    I am using stored procedure.

    Thursday, December 7, 2017 11:52 AM
  • User77042963 posted

    It is working fine , any other method is available. i want both previous date and next date .

    Example

    I have 6 records in a mytable .

    Id Column_Date
    1 12/2/2014
    2 12/3/2014
    3 12/4/2014
    4 12/5/2014
    5 12/8/2014
    6 12/9/2014

    Select * from my table where Column_Date ='12/07/2014'

    since Column_Date '12/07/2014'  is not available it should select the previous min date of  records .

    Id Column_Date
    4 12/5/2014

    Second requirement

    Select * from my table where Column_Date ='12/07/2014'

     select the next max date of  records .

    Id Column_Date
    5 12/8/2014

    CREATE TABLE mytable(
       Id          INTEGER  NOT NULL PRIMARY KEY 
      ,Column_Date DATE  NOT NULL
    );
    INSERT INTO mytable(Id,Column_Date) VALUES (1,'12/2/2014');
    INSERT INTO mytable(Id,Column_Date) VALUES (2,'12/3/2014');
    INSERT INTO mytable(Id,Column_Date) VALUES (3,'12/4/2014');
    INSERT INTO mytable(Id,Column_Date) VALUES (4,'12/5/2014');
    INSERT INTO mytable(Id,Column_Date) VALUES (5,'12/8/2014');
    INSERT INTO mytable(Id,Column_Date) VALUES (6,'12/9/2014');
    
    
    --declare @minDate as date
    --declare @maxDate as date
    
    declare @dt as date  ='12/07/2014'
    
    ;with mycte1 as (
    select Id,Column_Date, row_number() Over(Order by  Column_Date  DESC) rnMax
    from mytable
    WHERE Column_Date<=@dt
    )
    Select Id,Column_Date from mycte1 
    WHERE  rnMax=1
    
    ;with mycte2 as (
    select Id,Column_Date, row_number() Over(Order by  Column_Date  ASC) rnMin
    from mytable
    WHERE Column_Date>=@dt
    )
    
    Select Id,Column_Date from mycte2
    WHERE  rnMin=1
    
    
    
    
    drop table mytable
    Thursday, December 7, 2017 8:10 PM