Show Records From Previous Week 1

[likelylad]

I have the following query that I am trying to get to work properly. I want to show all the records of the previous week.

select * From my_table Where week(Date_Entered,3)=week(SUBDATE(now(),Interval 7 day),3)

What it is doing is showing only those records of the previous week that are after 7 days from now.
eg this week is week 49
last week is week 48
now is 04/12/02
It will only show records with week 48 after and including the 27/11/02 (7 days previous)

I hope this is clear????

Thanks in advance for any help received.

 

[sleipnir214]

Do you mean for your weeks to start on a Wednesday?

Have you tried:

select * From my_table Where week(Date_Entered,3) = week(now(),3) - 1 ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[sleipnir214]

Oops. My query won't work if you run it the first week of any year.

What programming language are you using with this code? You may have to calculate beginning and end dates in your language, then query for those records that fall between them, inclusive. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!

 

[likelylad]

Hi sleipnir

your query produced the same result as I was getting.

I want to see all records from the previous week (Monday to Sunday).
The query shown will only show records from Wednesday of last week to Sunday of last week.
However tomorrow when I run the query I suspect it will only show records from last Thursday to last Sunday.
On Friday it will only show records from last Friday to Sunday.
It appears to be taking the curdate() as the starting point of that week.
Again I hope I am clear

 

[likelylad]

Hi sleipnir

At the moment I am just running the code directly against the database

 

[sleipnir214]

I'm getting the results I expect. Here's my test environment:

A table date_data, which contains:

+------+------------+
| ID   | thedate    |
+------+------------+
| A    | 2002-11-15 |
| B    | 2002-11-16 |
| C    | 2002-11-17 |
| D    | 2002-11-18 |
| E    | 2002-11-19 |
| F    | 2002-11-20 |
| G    | 2002-11-21 |
| H    | 2002-11-22 |
| I    | 2002-11-23 |
| J    | 2002-11-24 |
| K    | 2002-11-25 |
| L    | 2002-11-26 |
| M    | 2002-11-27 |
| N    | 2002-11-28 |
| O    | 2002-11-29 |
| P    | 2002-11-30 |
| Q    | 2002-12-01 |
| R    | 2002-12-02 |
| S    | 2002-12-03 |
| T    | 2002-12-04 |
| U    | 2002-12-05 |
| V    | 2002-12-06 |
+------+------------+

If I perform:

select ID, thedate, dayname(thedate) as day, week(thedate,3) as week from date_data where week(thedate, 3) = (week(now(),3) - 1)

I get:

+------+------------+-----------+------+
| ID   | thedate    | day       | week |
+------+------------+-----------+------+
| K    | 2002-11-25 | Monday    |   48 |
| L    | 2002-11-26 | Tuesday   |   48 |
| M    | 2002-11-27 | Wednesday |   48 |
| N    | 2002-11-28 | Thursday  |   48 |
| O    | 2002-11-29 | Friday    |   48 |
| P    | 2002-11-30 | Saturday  |   48 |
| Q    | 2002-12-01 | Sunday    |   48 |
+------+------------+-----------+------+

Which is what I expect. ______________________________________________________________________

http://www.tuxedo.org/~esr/faqs/smart-questions.html

TANSTAAFL!