Help in SQL Query - easy

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • USI Newsgroups
    #1

    Help in SQL Query - easy

    First, let me apologize for how easy this probably is:
    DESCR TYPE SELL StartDate EndDate
    65048 04 Price A 4/21/2004 4/26/2004
    65048 06 Price C 4/20/2004 4/27/2004
    65048 08 Price B 4/22/2004 4/28/2004
    65049 04 Price A 4/19/2004 4/24/2004
    65049 06 Price B 4/22/2004 4/25/2004
    65049 09 Price C 4/20/2004 4/29/2004
    65050 07 Price A 4/21/2004 4/25/2004
    65050 06 Price B 4/18/2004 4/28/2004
    65050 05 Price C 4/17/2004 4/29/2004

    Descr, Type, Sell are CHAR
    StartDate and EndDate are SmallDatetime

    I need a simple query that would display the records with:
    Highest TYPE for each DESCR with:
    "Date I Enter" >= Startdate
    "Date I Enter" <= Enddate

    Results for ("Date I Enter" = 4/23/2004) should be:
    65048 08 Price B 4/22/2004 4/28/2004
    65049 09 Price C 4/20/2004 4/29/2004
    65050 07 Price A 4/21/2004 4/25/2004

    I would give you what I have done but it is such a mess I am better off
    starting over.

    Thanks!!




    -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
    http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
    -----== Over 100,000 Newsgroups - 19 Different Servers! =-----
    Tags: None
    • David Portas
      #2
      Re: Help in SQL Query - easy

      Try this:

      SELECT descr, type, sell, startdate, enddate
      FROM SomeTable AS T
      WHERE @date_entered BETWEEN startdate AND enddate
      AND type =
      (SELECT MAX(type)
      FROM SomeTable
      WHERE @date_entered
      BETWEEN startdate AND enddate
      AND descr = T.descr);

      --
      David Portas
      SQL Server MVP
      --


        Comment

        • David Portas
          #3
          Re: Help in SQL Query - easy

          Try this:

          SELECT descr, type, sell, startdate, enddate
          FROM SomeTable AS T
          WHERE @date_entered BETWEEN startdate AND enddate
          AND type =
          (SELECT MAX(type)
          FROM SomeTable
          WHERE @date_entered
          BETWEEN startdate AND enddate
          AND descr = T.descr);

          --
          David Portas
          SQL Server MVP
          --


            Comment

            • USI Newsgroups
              #4
              Re: Help in SQL Query - easy

              That worked great! Thanks!


              "David Portas" <REMOVE_BEFORE_ REPLYING_dporta s@acm.org> wrote in message
              news:TfWdnbucXa kB4BjdRVn-vA@giganews.com ...[color=blue]
              > Try this:
              >
              > SELECT descr, type, sell, startdate, enddate
              > FROM SomeTable AS T
              > WHERE @date_entered BETWEEN startdate AND enddate
              > AND type =
              > (SELECT MAX(type)
              > FROM SomeTable
              > WHERE @date_entered
              > BETWEEN startdate AND enddate
              > AND descr = T.descr);
              >
              > --
              > David Portas
              > SQL Server MVP
              > --
              >
              >[/color]




              -----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
              http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
              -----== Over 100,000 Newsgroups - 19 Different Servers! =-----

                Comment

                Previous template Next
                Working...