?
Solved

MYSQL Date Range between clause issue.

Posted on 2011-10-06
8
Medium Priority
?
399 Views
Last Modified: 2012-05-12
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

0
Comment
Question by:matthewdacruz
  • 5
  • 3
8 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36926010
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36926085
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
 

Author Comment

by:matthewdacruz
ID: 36926600
@ 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
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36926872
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
 
LVL 60

Expert Comment

by:Kevin Cross
ID: 36926874
Please show the full SELECT of the VIEW.
0
 

Author Comment

by:matthewdacruz
ID: 36927142
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
 
LVL 60

Accepted Solution

by:
Kevin Cross earned 2000 total points
ID: 36927414
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
 

Author Closing Comment

by:matthewdacruz
ID: 36929143
Thanks mwvisa1,
I learnt a lot from what you have told me.
I appreciate the help
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, I’ll talk about multi-threaded slave statistics printed in MySQL error log file.
Creating a Cordova application which allow user to save to/load from his Dropbox account the application database.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question