dear all..i want to make some report which get data for one semester. this semester started at april and finished at september in each year. But for the second semester it will get data from october until march(next year). how do i do to make this grouping or aggregation? i'm just now the basic query like:
select * from dt_tb where `dt` >= DATE_SUB(CURDATE(), INTERVAL 6 MONTH)
Something like:
SELECT *
FROM dt_tb
WHERE (dt >= '2010-10-01') AND (dt < '2011-05-01')
(all data for the period starting on October 1st and ending BEFORE May 1st, which is April 30th).
Using DATE_SUB() and CURDATE() is fine if you want a sliding window, but if you're requiring fixed start and end points in time, as a semester would be, it's best to use actual dates.
As well, your method would pick up any data from futures semesters which have not occured yet, as your WHERE clause basically says "give me everything in the database that's newer than 6 months ago". If you store the next two years' semesters, you'll get those as well. By specifying start/end dates, you get only the semester you actually want.