I am a total newb, and I couldn't find a good way to do this anywhere. I have a database table that contains statistics that are recorded at various time throughout the week. The reporting week starts on Thursday. The table contains a datestamp column (date) that stores when the data was recorded.
I need to pull the data for a given week (reporting weeks start on Thurs). I have written the following query:
SELECT *
FROM `table`
WHERE 1 = CASE
WHEN WEEKDAY(NOW()) = 0 THEN DATEDIFF(NOW(),`date`) BETWEEN -2 AND 4
WHEN WEEKDAY(NOW()) = 1 THEN DATEDIFF(NOW(),`date`) BETWEEN -1 AND 5
WHEN WEEKDAY(NOW()) = 2 THEN DATEDIFF(NOW(),`date`) BETWEEN -0 AND 6
WHEN WEEKDAY(NOW()) = 3 THEN DATEDIFF(NOW(),`date`) BETWEEN -6 AND 0
WHEN WEEKDAY(NOW()) = 4 THEN DATEDIFF(NOW(),`date`) BETWEEN -5 AND 1
WHEN WEEKDAY(NOW()) = 5 THEN DATEDIFF(NOW(),`date`) BETWEEN -4 AND 2
WHEN WEEKDAY(NOW()) = 6 THEN DATEDIFF(NOW(),`date`) BETWEEN -3 AND 3
END
This appears to work on initial testing. But I am not sure it is the best way to go about it. I don't know much about MySQL performance, but there will be over a hundred thousand records to filter. Will this query be real slow due to the number of conditions checked?
The NOW() function is used when pulling the most current report- however, it some cases I will need to do reports for other weeks- so I would substitute another date into the place.
Also, doing it this way requires re-writing the query if the reporting week changes- say the starting day changes to Wednesday.
I can't use the WEEK() function because you can only start a week on Sun or Mon with it.
Any ideas to improve this query are much appreciated!
Other Notes: Currently using MariaDB 5.3.
Best Answer
Here is a query I wrote up to give you the most recent thursday and the ending wednesday
Here is an example for today, 2011-09-21
Just replace the NOW() function calls with whatever datetime you like and you will have the week starting Thursday all the time for the give datetime you choose.
Here is another example using the specific date '2011-01-01'
Your query of
table
referencing today would resemble something like this:Give it a Try !!!
UPDATE 2011-09-22 16:27 EDT
This was my proposed query for marking Thu-Wed.
How about other weeks ???
(SELECT SUBSTR('6012345',wkndx,1)
does the week starting Mon ending Sun(SELECT SUBSTR('5601234',wkndx,1)
does the week starting Tue ending Mon(SELECT SUBSTR('4560123',wkndx,1)
does the week starting Wed ending Tue(SELECT SUBSTR('3456012',wkndx,1)
does the week starting Thu ending Wed(SELECT SUBSTR('2345601',wkndx,1)
does the week starting Fri ending Thu(SELECT SUBSTR('1234560',wkndx,1)
does the week starting Sat ending Fri(SELECT SUBSTR('0123456',wkndx,1)
does the week starting Sun ending Sat