Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2003
    Posts
    3

    Unanswered: Comparing Dates in Subqueries

    Hi,

    I am trying to write a basic select query with a subquery.

    select x
    from y
    where DateTime > '27 January 2003'
    and DateTime < '02 February 2003'
    and a = 'ttt'
    and x IN (SELECT x
    from y
    WHERE a = 'vvv'
    and rcd.DateTime > '27 January 2003'
    and rcd.DateTime < '02 February 2003')

    I only want to see the data for the records where ttt happened before vvv.

    Any ideas?

  2. #2
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    select x
    from y
    where DateTime > '27 January 2003'
    and DateTime < '02 February 2003'
    and a = 'ttt'
    and exists
    (SELECT *
    from y y2
    WHERE y2.a = 'vvv'
    and y2.DateTime > '27 January 2003'
    and y2.DateTime < '02 February 2003'
    and y2,DateTime > y.DateTime
    )

  3. #3
    Join Date
    Dec 2002
    Location
    Czech Republic
    Posts
    249
    select y.x
    from y
    left join
    (
    select x,MinDateTime=min(DateTime)
    from y
    where a='vvv' and DateTime > '27 January 2003' and DateTime < '02 February 2003'
    group by x
    ) sy on sy.x=y.x and a='ttt' and ( (DateTime<MinDateTime) or MinDateTime is null )

Posting Permissions

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