Results 1 to 13 of 13
  1. #1
    Join Date
    Jan 2004
    Location
    Columbia, MD USA
    Posts
    8

    Unanswered: Daterange for a daterange

    It's late and I'm having a hard time figuring out how the heck to build my where clause.

    Here's a sample table with data:

    ID int
    Value int
    StartDate DateTime
    EndDate DateTime

    1 | 100 | 1/1/2004 | 1/23/2004
    2 | 200 | 1/23/2004 | NULL

    For all intents and purposes, the second record has a null end date because it's valid until a new value is entered. If I were to update the value again, the 3rd record would look like this.

    3 | 300 | 1/24/2004 | NULL

    And, since this was updated, I'd go back and update the 2nd record so that I know the End Date (the 3rd record's start date)

    2 | 200 | 1/23/2004 | 1/24/2004

    Ok, with that said, my application looks at each week in a year, and looks for a valid value for the given date. I need to say "for this week, give me the value." If 2 values fall within the given week, I want to grab the highest (MAX) value.

    Any ideas on how I'd structure the SQL statement for this? The where clause is where I'm having funny (hey, that's kinda funny -- where and where).

    Anyway, I appreciate any help that you all can give me on this one. It's getting late and my brain is burnt out for the day!

  2. #2
    Join Date
    Jan 2004
    Location
    Columbia, MD USA
    Posts
    8

    Question

    Anybody? Sorry to bump this, I just though I may have had a bite or two by now.

  3. #3
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    checking your clause...
    hope is on the way !

  4. #4
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    "for this week, give me the value."
    If 2 values fall within the given week,
    I want to grab the highest (MAX) value.



    what is the value that you're talkign about ?
    startdate ???

  5. #5
    Join Date
    Jan 2004
    Location
    Columbia, MD USA
    Posts
    8
    ID int
    Value int <----- [ this is what I'm getting ]
    StartDate DateTime [ used in where clause ]
    EndDate DateTime [ used in where clause ]

  6. #6
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    and when you say "this week"
    what kind of parameter are you sending

    date ?
    no of week in year ?

  7. #7
    Join Date
    Jan 2004
    Location
    Columbia, MD USA
    Posts
    8
    A date range.

    i.e. 2/8/2004 - 2/14/2004

  8. #8
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    is this what your looking for ?

    select max(value)
    from range
    where '2/8/2004' between startdate and enddate or
    '2/14/2004' between startdate and enddate

  9. #9
    Join Date
    Jan 2004
    Location
    Columbia, MD USA
    Posts
    8
    Hmm, that may work, BUT, there is still the situation where the most recent value does not have an enddate. I guess I'm going to need to OR that in. Any ideas on that?

  10. #10
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    maybe better ?

    select *
    from range
    where '2/8/2004' between startdate and isnull(enddate,'1/1/2999') or
    '2/14/2004' between startdate and isnull(enddate,'1/1/2999')

  11. #11
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    magical transformation of null to a far-far-far-date

  12. #12
    Join Date
    Jan 2004
    Location
    Columbia, MD USA
    Posts
    8
    I think that will work out just fine!

    Thanks a bunch, I'll work in implementation and check back if I forgot something. You have helped me past my brain fart, thanks!

  13. #13
    Join Date
    Nov 2003
    Location
    FRANCE
    Posts
    393
    on sql i'm good enough to help a bit

Posting Permissions

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