SQL - How to select when a date is a Monday OR the first date in the table?
I have a table of sales reports (eod_cache). I'm trying to get a list of each week of sales, beginning with Monday, OR if the first date of sales is not a Monday, then adding that to the list as well. For example, if there are daily sales starting on 7/20/2011 through today, the list would be:
7/25/2011
7/20/2011
I am currently getting just Mondays, and wondering how I can add the minimum/smallest date to my list of returned dates. Here is my statement:
SELECT DISTINCT date
FROM eod_cache
WHERE DAYOFWEEK(date) = 2
ORDER BY date DES开发者_运维知识库C
You can add an OR
condition to your DAYOFWEEK
criteria that also allows in the smallest date.
SELECT DISTINCT date
FROM eod_cache
JOIN user_store_permissions
ON user_store_permissions.store_id = eod_cache.store_id
WHERE user_store_permissions.user_id = 32 AND
(DAYOFWEEK(date) = 2 or date = (select min(date) from eod_cache))
ORDER BY date DESC
In this example, I'm just using the smallest date in the table itself, though it looks like you might want to modify that query to use your permissions infrastructure, but that modification should be self-explanatory.
精彩评论