Results 1 to 9 of 9

Thread: sql query issue

  1. #1
    Join Date
    Oct 2012
    Posts
    6

    Unanswered: sql query issue

    Hi all,
    I am finding it difficult to build one mysql query.

    I have a table messages_stats_analysis_ota, with 4 columns: [brand_code, date_time_from, date_time_to, success_count].

    I want the value inside 'success_count' against individual 'brand_code', which lie within the last 7 days from the current date.

    u2, 01.10.2012, 07.10.2012, 100
    u2, 02.10.2012, 08.10.2012, 100
    u2, 03.10.2012, 07.10.2012, 100
    u2, 08.10.2012, 14.10.2012, 50
    fr, 01.10.2012, 07.10.2012, 200


    Now lets assume, the current date is 08.10.2012.

    I want only the follwoing 2 rows to be retrieved:

    u2, 01.10.2012, 07.10.2012, 100
    fr, 01.10.2012, 07.10.2012, 200

    I tried to build this query:

    SELECT * FROM messages_stats_analysis_ota WHERE date_time_from >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

    but it gives me, the follwoing, which i dont want:

    u2, 01.10.2012, 07.10.2012, 100
    u2, 02.10.2012, 08.10.2012, 100
    u2, 03.10.2012, 07.10.2012, 100
    fr, 01.10.2012, 07.10.2012, 200

    Assumtpion is current date is: 08.10.2012.

    Coudl someone please help me out?

    Thanks.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the format of your dates clearly implies you are using VARCHAR to store them

    bad move, and you will continue to have problems until you convert them to DATE or DATETIME datatype
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Oct 2012
    Posts
    6
    Quote Originally Posted by r937 View Post
    the format of your dates clearly implies you are using VARCHAR to store them

    bad move, and you will continue to have problems until you convert them to DATE or DATETIME datatype
    i have the column type as 'datetime'. The format i wrote earlier is german.

  4. #4
    Join Date
    Oct 2012
    Posts
    6
    the query i specified earlier works fine. it's just that it gives me the result that i dont need.

  5. #5
    Join Date
    Oct 2012
    Posts
    6
    02.10.2012 17:10:30,
    10.10.2012 17:10:33,
    etc

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by sbutt View Post
    i have the column type as 'datetime'. The format i wrote earlier is german.
    okay, my apologies

    now, let's look at your logic ...

    WHERE date_time_from >= DATE_SUB(CURDATE(), INTERVAL 7 DAY);

    if CURDATE() is october 8, then this evaluates as ...

    WHERE date_time_from >= october 1st

    consequently, all the following ~are~ correctly returned --

    u2, 01.10.2012
    u2, 02.10.2012
    u2, 03.10.2012
    fr, 01.10.2012

    so what you need to do next is re-specify what you want -- "which lie within the last 7 days from the current date"

    perhaps you should be testing date_time_to as well as date_time_from ??
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Oct 2012
    Posts
    6
    i exactly want the row(s) where the 'date_time_from' is the 1st day and date_time_to is the 7th day from the current date.

    For example if the current date is 08.10.2012, then i want record(s) with date_time_from: 01.10.2012 and date_time_to: 07.10.2012.

    If i run the same query on 02.10.2012 (current date), then i should get record(s): date_time_from: 02.10.2012 and date_time_to: 08.10.2012 and consequently.

    I dont want a range of records returned, but only those where exact column from and to column matches.

    The table could have number of dates, but important factors are current date and then -7th day and current day -1 day.

    Thanks

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    the thing that makes this tricky is that there can be quite a number of date_time_from values that all fall on a certain date

    therefore you need a range test, and since you want to apply this to both date_time_from and date_time_to, you need two range tests
    Code:
    WHERE date_time_from >= CURRENT_DATE - INTERVAL 7 DAY -- 1st range test low end
      AND date_time_from  < CURRENT_DATE - INTERVAL 6 DAY -- 1st range test high end
      AND date_time_to >= CURRENT_DATE - INTERVAL 1 DAY  -- 2nd range test low end 
      AND date_time_to  < CURRENT_DATE                   -- 2nd range test high end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Oct 2012
    Posts
    6
    thanks it works!

Posting Permissions

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