Results 1 to 2 of 2
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: Query problem : select the record from last 7days

    I've have these following table

    tbllocation
    Main_ID | Date_Taken | Time |Hit
    -----------------------------------------
    206 | 5/9/2008 | 100 | 2
    206 | 5/9/2008 | 200 | 3
    206 | 5/6/2008 | 300 | 6
    201 | 5/1/2008 | 400 | 5
    201 | 5/4/2008 | 500 | 9
    201 | 5/7/2008 | 600 | 2
    204 | 5/2/2008 | 700 | 2
    204 | 5/3/2008 | 800 | 4
    204 | 5/6/2008 | 900 | 2
    203 | 5/7/2008 | 100 | 2
    203 | 5/8/2008 | 200 | 3
    203 | 5/9/2008 | 300 | 6
    202 | 5/4/2008 | 400 | 5
    202 | 5/3/2008 | 500 | 9
    202 | 5/8/2008 | 200 | 3
    205 | 5/2/2008 | 300 | 6
    205 | 5/1/2008 | 400 | 5
    205 | 5/9/2008 | 500 | 9

    tblSetValue
    Main_ID | Hit2
    ---------------
    206 | 3
    201 | 5
    204 | 3
    203 | 1
    202 | 8
    205 | 7
    *Main_ID is a primary key

    Condition
    1. Let's say, the current date is 5/9/2008
    2. Result only display the last 7 days data. From above data. it's mean only pickup from 5/3/2008 to 5/9/2008
    3. Every Main_ID only pickup the MAX Hit
    4. Diff (column on the fly) = Hit - Hit2

    The expected result shown as follow
    tblResult
    Main_ID | Date_Taken | Time | Hit | Hit2 | Diff
    -----------------------------------------------
    206 | 5/6/2008 | 300 | 6 | 3 | 3
    201 | 5/4/2008 | 500 | 9 | 5 | 4
    204 | 5/3/2008 | 800 | 4 | 3 | 1
    203 | 5/9/2008 | 300 | 6 | 1 | 5
    ....
    ....
    ....

    Anyone can help me to show the query?

  2. #2
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    where Date_Taken>=dateadd(dd, datediff(dd,'20000101',getdate())-6, '20000101')

Posting Permissions

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