Forum Moderators: open
Hi all,
Looking for some help in constructing this query, and appreciate all help.
I am about to create a new table, this table is going to have a few columns, but the main ones are going to be date_from and date_to.
First question what is the best type for this column i.e DATE or DATETIME.
Now my main queries on this table are going to be firstly checking todays date and getting all results from table that are >= (after and equal to). I then want to also GROUP BY months so that the results will show all columns in current month then move on to next month and so on, also these results should be in order of nearest to query date. i.e.
CURRENT MONTH
1 - Result from todays date ending 25th August
2 - " " " " 19th August 2005 ending 26th August
3 - " " " " 26th August 2005 ending 1st September
SEPTEMBER
4 - Result from 1st Sept ending 7th Sept
and so on
Note: in the first GROUP we have a column with an end date in September but the beginning date belongs in the first month, so this would mean that the GROUP BY used the date_from column when GROUPING.
My question is what would my SQL statement look like to get the results in a format like this?
SELECT * FROM mytable WHERE (some date functions?) = (CURRDATE or something?) GROUP BY (column name?) ORDER BY (date_from?)
TIA,
-George
It looks like you might need two queries to do what you want to.
The date functions are pretty good, and you can group by MONTH(datefld) and put that in your select clause.
The reason I think two queries is that you want to separate current month from other months, and you might have teo specify that in the WHERE, unless you have some type of row checking after the query is executed.
What I do for these complex situations is hardcode as much of the query as possible to get a fixed dataset. Then, I go back and put variables in one at a time until it works for all query possibilities.
Give that a try here and see if it works.
Can you explain a little more on this:
>>unless you have some type of row checking after the query is executed
TIA,
-George
SELECT
MONTH(date_to) as month,
DATE_FORMAT(date_from, '%D %M %Y') AS date_from,
DATE_FORMAT(date_to, '%D %M %Y') AS date_to
FROM times
ORDER BY date_to
;
Like you said I shall keep the separation of months and years to the application logic by splitting it up into month and year arrays.
Many thanks for the query...
-George
