Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2014
    Posts
    2

    Exclamation Unanswered: Query dates: < ,between, and >

    Hello,

    I have the following tables:
    Options(ID, Date)
    Location(ID, Effdt, Throughdt, Location)

    I am trying to build a query that retrieves Location.Location depending on the Options.Date. I need to have a location associated with each date. My issue is that some of the Options.Date data doesn't fall between Location.Effdt and Location.Throughdt.

    Here is the query I have now:
    Code:
    SELECT Options.ID, Options.Date1, Location.Location AS Loc1
    FROM Options LEFT JOIN Location ON Options.ID=Location.ID
    AND (Options.Date BETWEEN Location.Effdt AND Location.Throughdt)
    OR (Options.Date BETWEEN>=Location.Effdt AND Location.Throughdt Is NULL)
    ORDER BY [Emp Num];
    I am able to retrieve dates that are after Location.Effdt; but not before and then I get errors or I have more records in my query than what needs to be returned.

    Attached is a zip file containing a spreadsheet of sample tables that I am talking about. Please help if possible. I also have additional dates in Options table that need to associated with a location but this phase 2. I am just trying to get all of the locations aligned with one date for now.

    Thanks in advance!
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd leave the join as between the ID coloumns and use a where clause for the dates


    Code:
    SELECT Options.ID, Options.Date1, Location.Location AS Loc1
    
    FROM Options 
    
    LEFT JOIN Location ON Options.ID=Location.ID
    
    WHERE
    
    (Options.Date BETWEEN Location.Effdt AND Location.Throughdt)
    
    OR (Options.Date >= Location.Effdt AND IS null(Location.Throughdt))
    
    ORDER BY [Emp Num];
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Jul 2014
    Posts
    2
    Quote Originally Posted by healdem View Post
    I'd leave the join as between the ID coloumns and use a where clause for the dates


    Code:
    SELECT Options.ID, Options.Date1, Location.Location AS Loc1
    
    FROM Options 
    
    LEFT JOIN Location ON Options.ID=Location.ID
    
    WHERE
    
    (Options.Date BETWEEN Location.Effdt AND Location.Throughdt)
    
    OR (Options.Date >= Location.Effdt AND IS null(Location.Throughdt))
    
    ORDER BY [Emp Num];
    healdem,

    The issue with the WHERE clause is that I need all of the records from the "Options" table to show in the query. However, this would work if I could get the query to return dates for an ID that are before Location.Effdt. Sorry, I didn't list this in the sample tables I provided of my data.

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..sorry I don't understand your data

    try:-
    SELECT Options.ID, Options.oDate, Location.Location AS Loc1, effdt, Throughdt
    FROM Options LEFT JOIN Location ON Options.ID = Location.ID
    WHERE (IsNull(effdt) AND IsNull(throughdt))
    OR (oDate Between Effdt And Throughdt)
    OR (oDate >= Effdt AND IsNull(Throughdt));
    [/code]

    which returns:-
    Code:
    ID	oDate	Loc1	effdt	Throughdt
    1	14/02/2014	01127	01/06/2011	
    1	14/02/2014	01127	01/06/2011	
    1	15/02/2013	01127	01/06/2011	
    1	10/02/2012	01127	01/06/2011	
    2	10/02/2012	00454	01/07/2004	
    2	10/02/2012	00454	01/07/2004	
    2	15/02/2013	00454	01/07/2004	
    2	15/02/2013	00454	01/07/2004	
    3	10/02/2012	00672	01/08/2003	30/09/2013
    3	15/02/2013	00672	01/08/2003	30/09/2013
    3	14/02/2014	00454	01/10/2013	
    3	14/02/2014	00454	01/10/2013	
    4	10/02/2012	00428	01/12/2011	
    4	15/02/2013	00428	01/12/2011	
    4	10/02/2012	00428	01/12/2011	
    4	15/02/2013	00428	01/12/2011	
    4	14/02/2014	00428	01/12/2011	
    4	14/02/2014	00428	01/12/2011
    as you have duplicate data in options you will get what may look like duplicated rows in the resultset
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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