locked
how to get only those data that will match the parameter date that was inputted RRS feed

  • Question

  • Hi to all,

    Please see below scenario (yyyy-MM-dd)

    table1

    ID  dateon                 dateoff

    1  2013-02-03         2013-05-02

    2  2013-01-05         2013-03-02

    3  2013-01-01         2013-02-01

    4  2013-02-01         2013-05-05

    5  2013-01-03         2013-02-02

    what i want is that, user will input

    from: '2013-02-02' to: '2013-05-02'

    so i only want to get ID that will match on the parameter that was given so for this example ID match are

    ID

    1

    2

    4

    as you can see i did not include ID 5 since he will be out on the datefrom that was inputted by the user

    I only get 3 ID, please help how to query that

    thanks in advance.

    Wednesday, April 3, 2013 5:29 AM

Answers

  • as per your example you should get only ID 1. try my code below you should understand...

    create table #temp
    (
    id int,
    dateon varchar(100),
    dateoff varchar(100)
    )
    
    insert into #temp select '1',  '2013-02-03',        '2013-05-02'
    insert into #temp select '2',  '2013-01-05',        '2013-03-02'
    insert into #temp select '3',  '2013-01-01',        '2013-02-01'
    insert into #temp select '4',  '2013-02-01',        '2013-05-05'
    insert into #temp select '5',  '2013-01-03',        '2013-02-02'
    
    select * from #temp where dateon >= '2013-02-02' and dateoff <=  '2013-05-02'
    
    drop table #temp
    

    Or if you want any of the matching parameter of date input, try the below query


    select * from #temp where dateon >= '2013-02-02' or dateoff <=  '2013-05-02'


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.


    • Proposed as answer by Kapil.Kumawat Wednesday, April 3, 2013 5:55 AM
    • Edited by Iam_Rakesh Wednesday, April 3, 2013 8:08 AM
    • Marked as answer by Allen Li - MSFT Thursday, April 11, 2013 3:22 AM
    Wednesday, April 3, 2013 5:43 AM
  • Try this suggestion

    create table #temp
    (
    id int,
    dateon varchar(100),
    dateoff varchar(100)
    )
    
    insert into #temp select '1',  '2013-02-03',        '2013-05-02'
    insert into #temp select '2',  '2013-01-05',        '2013-03-02'
    insert into #temp select '3',  '2013-01-01',        '2013-02-01'
    insert into #temp select '4',  '2013-02-01',        '2013-05-05'
    insert into #temp select '5',  '2013-01-03',        '2013-02-02'
    
    select * from #temp where dateoff > '2013-02-02' or dateon >=  '2013-05-02'
    
    drop table #temp
    You might have to play around with the operators and use <, > and or <=, >= if you want to include the userinput dates or not.

    Steen Schlüter Persson (DK)

    Wednesday, April 3, 2013 7:28 AM

All replies

  • use dynamic query like or procedure with input parameter as per your need.

    in dynamic query like,

    Declare @startdate datetime, @enddate datetime

    SET @startdate= enter your date

    SET @enddate= enter end date

    SELECT * from yourtabe where date between @startdate and @endate

    or in proc

    create proc proc_name

    @startdate datetime, @enddate datetime

    as

    SELECT * from yourtabe where date between @startdate and @endate

    you can execute proc like

    exec procname '2013-02-02' , '2013-05-02'

    • Edited by skc_chat Wednesday, April 3, 2013 5:44 AM
    • Proposed as answer by Kapil.Kumawat Wednesday, April 3, 2013 5:55 AM
    Wednesday, April 3, 2013 5:42 AM
  • as per your example you should get only ID 1. try my code below you should understand...

    create table #temp
    (
    id int,
    dateon varchar(100),
    dateoff varchar(100)
    )
    
    insert into #temp select '1',  '2013-02-03',        '2013-05-02'
    insert into #temp select '2',  '2013-01-05',        '2013-03-02'
    insert into #temp select '3',  '2013-01-01',        '2013-02-01'
    insert into #temp select '4',  '2013-02-01',        '2013-05-05'
    insert into #temp select '5',  '2013-01-03',        '2013-02-02'
    
    select * from #temp where dateon >= '2013-02-02' and dateoff <=  '2013-05-02'
    
    drop table #temp
    

    Or if you want any of the matching parameter of date input, try the below query


    select * from #temp where dateon >= '2013-02-02' or dateoff <=  '2013-05-02'


    Please mark as helpful and propose as answer if you find this as correct!!! Thanks, Rakesh.


    • Proposed as answer by Kapil.Kumawat Wednesday, April 3, 2013 5:55 AM
    • Edited by Iam_Rakesh Wednesday, April 3, 2013 8:08 AM
    • Marked as answer by Allen Li - MSFT Thursday, April 11, 2013 3:22 AM
    Wednesday, April 3, 2013 5:43 AM
  • Do you filter on dateon and dateoff or only on dateon? See Iam's example.


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Blog: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance

    Wednesday, April 3, 2013 5:47 AM
    Answerer
  • no in my example i should have get 3 ID's

    and i know that query but what i want is as you can see above, ID number 2 is still active from 2013-01-05    until  2013-03-02

    so the ID is counted i am counting active ID on the parameter that i have indicated.

    please help.

    thanks.

    Wednesday, April 3, 2013 6:12 AM
  • Hi skc_chat

    this is not correct i want to get all the active ID between the two dates specified by the user.

    so in above i will get 3 ID's

    please advise.

    thanks

    Wednesday, April 3, 2013 6:19 AM
  • hi uri,

    iam filtering between dateon and dateoff

    so i want to know who is active on that parameter so as per my example above you will notice that there is a dateoff so meaning the person on that ID is no longer active on that particular date.

    pls advise.

    Wednesday, April 3, 2013 6:26 AM
  • Try this suggestion

    create table #temp
    (
    id int,
    dateon varchar(100),
    dateoff varchar(100)
    )
    
    insert into #temp select '1',  '2013-02-03',        '2013-05-02'
    insert into #temp select '2',  '2013-01-05',        '2013-03-02'
    insert into #temp select '3',  '2013-01-01',        '2013-02-01'
    insert into #temp select '4',  '2013-02-01',        '2013-05-05'
    insert into #temp select '5',  '2013-01-03',        '2013-02-02'
    
    select * from #temp where dateoff > '2013-02-02' or dateon >=  '2013-05-02'
    
    drop table #temp
    You might have to play around with the operators and use <, > and or <=, >= if you want to include the userinput dates or not.

    Steen Schlüter Persson (DK)

    Wednesday, April 3, 2013 7:28 AM
  • Hi skc_chat

    this is not correct i want to get all the active ID between the two dates specified by the user.

    so in above i will get 3 ID's

    please advise.

    thanks

    yes it will wrk as per ur need and give 3 ids only, just add column name dateoff as you are querying on that

    Declare @startdate datetime, @enddate datetime

    SET @startdate= enter your date

    SET @enddate= enter end date

    SELECT ID from yourtabe where dateoff between @startdate and @endate

    or in proc

    create proc proc_name

    @startdate datetime, @enddate datetime

    as

    SELECT ID from yourtabe where dateoff between @startdate and @endate

    you can execute proc like

    exec procname '2013-02-02' , '2013-05-02'

    Wednesday, April 3, 2013 8:45 AM
  • It looks like the requirement is to show anyone who was active in that time frame.  The typical solution is to show records where the end date or dateoff is after the start of the date range and the start date or dateon is prior to the ending date of the range,

    The Where clause would be "where dateoff > '2013-02-02' and dateon <  '2013-05-02' "

    HTH

    Tim Mills-Groninger

    Wednesday, April 3, 2013 3:40 PM