MYSQL Date Range between clause issue.

Hi Experts
I am trying to retrieve data using a between clause in the where statement.
The statement works but it only shows the data defined by the interval.

I want all data from now up to the defined interval.
Is this where clause correct.
As it stands it gives me the 2014 and not 2011 up to 2014

Any help will be appreciated

Sample data
===================
131      April        8552      128280.00      2014-04-26
131      May      8379      125685.00      2014-05-31
131      June        8402      126030.00      2014-06-28
131      July      7743      116145.00      2014-07-26
131      August      8093      121395.00      2014-08-30
131      September      7810      117150.00      2014-09-27
131      October      10629      159435.00      2014-10-25
131      November      11001      165015.00      2014-11-29
131      December      11640      174600.00      2014-12-27
131      January      9468      142020.00      2015-01-31
131      February      8726      130890.00      2015-02-28
131      March      9807      147105.00      2015-03-14

WHERE
	`theme_keyword_promotiontracker2`.`TK_profileid` = `theme_keyword_promotiontracker2`.`TK_profileid` AND `theme_keyword_promotiontracker2`.`number` > 0
AND YEAR(theme_keyword_promotiontracker2.PP_dateDue)
BETWEEN YEAR(NOW()) AND YEAR(DATE_ADD(NOW(), INTERVAL 6 YEAR))
GROUP BY
	MONTH(theme_keyword_promotiontracker2.PP_dateDue)
ORDER BY
	`theme_keyword_promotiontracker2`.`weekNumber`,
  MONTH(theme_keyword_promotiontracker2.PP_dateDue)

Open in new window

matthewdacruzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:
matthewdacruz,

YEAR(DATE_ADD(NOW(), INTERVAL 6 YEAR)) yields 2017; therefore, 2014 does not even make sense. As such, I would check that the data you are missing is not due to some other filter.

Other notes, why do you have this bit that appears to just be checking that the id equals itself?
`theme_keyword_promotiontracker2`.`TK_profileid` = `theme_keyword_promotiontracker2`.`TK_profileid`

You are grouping by the month of PP_DateDue, so how are you seeing years are missing?
GROUP BY
      MONTH(theme_keyword_promotiontracker2.PP_dateDue)
0
Kevin CrossChief Technology OfficerCommented:
By the way, it is generally better to construct beginning and ending dates and use those rather than wrap your column with a function.

e.g., using http://dev.mysql.com/doc/refman/5.0/en/date-calculations.html
WHERE theme_keyword_promotiontracker2.PP_dateDue > = MAKEDATE(YEAR(NOW()), 1)
AND theme_keyword_promotiontracker2.PP_dateDue < MAKEDATE(YEAR(NOW())+7, 1)

MAKEDATE(YEAR(NOW()), 1) ==> 2011-01-01 (first day of current year)
MAKEDATE(YEAR(NOW())+7, 1) ==> 2018-01-01 (first day of year 7 years from now)

The end date uses 7 years, so that everything from now through 6th year is included regardless of day or timestamp on 12/31/{sixth year}.

Hopefully that makes sense.
0
matthewdacruzAuthor Commented:
@ mwvisa1:
Sorry the code was from a view.
The 2014 has been confusing me as well.
Basically what I want is the date now up to 6 years in the futures data.
I have a column that gives me a full date: see last column in sample. It gives 2014, and 3 months of 2015.

Thanks for the feedback, I will try your suggestion.

WHERE
	`theme_keyword_promotiontracker2`.`TK_profileid` = 100 AND `theme_keyword_promotiontracker2`.`number` > 0
AND YEAR(theme_keyword_promotiontracker2.PP_dateDue)
BETWEEN YEAR(NOW()) AND YEAR(DATE_ADD(NOW(), INTERVAL 6 YEAR))
GROUP BY
	MONTH(theme_keyword_promotiontracker2.PP_dateDue)
ORDER BY
	`theme_keyword_promotiontracker2`.`weekNumber`,
  MONTH(theme_keyword_promotiontracker2.PP_dateDue)

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Kevin CrossChief Technology OfficerCommented:
How are you getting a full date with GROUP BY? If there is a GROUP BY, the other columns are either aggregated or taking advantage of MySQL's tolerance for columns in SELECT that are not part of GROUP BY or the subject of an aggregate. It is tolerant because what it appears to do under the covers is apply an aggregate (or choose a row at random, but often appears to be similar to FIRST() or LAST() in some databases) for you; therefore, what you may be seeing is some random date that happens to be within the month specified ...
0
Kevin CrossChief Technology OfficerCommented:
Please show the full SELECT of the VIEW.
0
matthewdacruzAuthor Commented:
HI
here is the query for the view.
When I call the view I define the parameter for the `theme_keyword_promotiontracker2`.`TK_profileid`.
Any advice on the best way to do this would be appreciated.

Thanks for all the help so far.

SELECT
	`theme_keyword_promotiontracker2`.`TK_profileid` AS `TK_profileid`,
	monthname(`theme_keyword_promotiontracker2`.`PP_dateDue`)AS `Month_Tasks_Due`,
	sum(`theme_keyword_promotiontracker2`.`number`)AS `New_Re_Purposed_Content`,
	round((sum(`theme_keyword_promotiontracker2`.`number`) * `projectprofile`.`ContentTeamLabourCostph`), 2)AS `Estimated_budget_UC_dev`,
	`theme_keyword_promotiontracker2`.`PP_dateDue`
FROM
	(
		(
			`theme_keyword_promotiontracker2`
			LEFT JOIN `theme_keywords` ON((`theme_keywords`.`theme_keywordsid` = `theme_keyword_promotiontracker2`.`TK_theme_keywordsid`))
		)
		LEFT JOIN `projectprofile` ON((`projectprofile`.`profileid` = `theme_keyword_promotiontracker2`.`TK_profileid`))
	)
WHERE
	`theme_keyword_promotiontracker2`.`TK_profileid` = `theme_keyword_promotiontracker2`.`TK_profileid`
		AND `theme_keyword_promotiontracker2`.`number` > 0
		AND YEAR(theme_keyword_promotiontracker2.PP_dateDue)
		BETWEEN YEAR(NOW())
		AND YEAR(DATE_ADD(NOW(), INTERVAL 6 YEAR))
GROUP BY
	MONTH(theme_keyword_promotiontracker2.PP_dateDue)
ORDER BY
	`theme_keyword_promotiontracker2`.`weekNumber`,
	MONTH(theme_keyword_promotiontracker2.PP_dateDue)

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
Okay, so it is what I thought. You are doing a GROUP BY month, therefore, year is not a concern. If you mean to GROUP BY YEAR, MONTH, then that would make more sense. My recommendation would be to have the view be all the detailed rows and then you do the GROUP BY after the fact unless you are always getting the data grouped in this fashion. If that is the case, then you will need to add columns to help you see the dates in that range like MIN(PP_DateDate) and MAX(PP_DateDue). With group by year and month combination, this should represent the first and last actual days for each month of each year in your data. Your other aggregates will be over this time frame.

Second bit of advice -- although MySQL allows otherwise, I would have every column not being aggregated in GROUP BY and every column you want a SUM(), etc on clearly defined as such. Otherwise, the results will be random. Just makes it easier to maintain and port code if you ever change databases or behavior changes to more standard later as most every other database system will give you an error for current design.

Kevin
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
matthewdacruzAuthor Commented:
Thanks mwvisa1,
I learnt a lot from what you have told me.
I appreciate the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.