Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Count number of Mon, Tue, etc... between a date range

Posted on 2004-04-23
5
Medium Priority
?
921 Views
Last Modified: 2012-06-21
I am trying to pull out the number of Mondays, Tuesdays, ..., Sundays inbetween two date ranges in a mysql database (ex. 3/10/04 and 4/10/04).

Each day count would need to be a seperate select statement.

As in all my questions, I would love to keep it in a MySQL statement if possible :)

Thanks for the help

0
Comment
Question by:keeper3
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 7

Expert Comment

by:madwax
ID: 10902812
I think that the following could work, I can't test it until later tonight....

//madwax

SELECT
SUM(IF(DATEFORMAT(myDateField,"%a")="Mon",1,0)) AS nbrOfMondays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Tue",1,0)) AS nbrOfTuesdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Wed",1,0)) AS nbrOfWednesdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Thu",1,0)) AS nbrOfThursdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Fri",1,0)) AS nbrOfFridays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Sat",1,0)) AS nbrOfSaturdays,
SUM(IF(DATEFORMAT(myDateField,"%a")="Sun",1,0)) AS nbrOfSundays
FROM
myTable
WHERE
myDateField >= "3/10/04" AND myDateField <= "01/01/04"
0
 
LVL 7

Accepted Solution

by:
madwax earned 1000 total points
ID: 10902878
correction:

DATEFORMAT must be replaced with DATE_FORMAT

and the following query is tested and works:

SELECT
SUM(IF(DATE_FORMAT(myDateField,"%a")="Mon",1,0)) AS nbrOfMondays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Tue",1,0)) AS nbrOfTuesdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Wed",1,0)) AS nbrOfWednesdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Thu",1,0)) AS nbrOfThursdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Fri",1,0)) AS nbrOfFridays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Sat",1,0)) AS nbrOfSaturdays,
SUM(IF(DATE_FORMAT(myDateField,"%a")="Sun",1,0)) AS nbrOfSundays
FROM
datetable
WHERE
myDateField >= "2004-01-01" AND myDateField <= "2004-04-01"

You will probably have to change the formatting of your datestring so it is compatible with MySQL:s (swedish! :) ) notation "YYYY-MM-DD"

//madwax (of sweden :)

P.S. I didn't get the thing with separate queries... you may take one day at the time (at a separate query) but I don't really see the point... D.S.
0
 

Author Comment

by:keeper3
ID: 10902949
That will work as done, though I should of mentioned on thing.  In the database where the dates are stored, each record has a start_date and a end_date, and I am looking to analyze all the dates in between.  

bill_id    start_date    end_date
--------------------------------------
1         2003-02-12    2003-03-13
2         2003-03-13    2003-04-15

I'm having trouble using your statement b/c I don't have a list of dates to select through (myDateField), just a start and end date.  Is there a way around this?

Thanks
0
 
LVL 7

Expert Comment

by:madwax
ID: 10904066
That isn't possible without using either a written function compiled into MySQL OR having a programming language take care of the post and running the query for every tuple. The latter is the easiest to implement, but not very good if it is going to be run many times since you will have several queries to the db each time.

read more on mysql create functions:
http://dev.mysql.com/doc/mysql/en/Extending_MySQL.html

//madwax
0
 

Author Comment

by:keeper3
ID: 10905484
Well I succumed and imported a date_list field with every date from 2002 to 2005.  I can work wtih that for a while, or until MySQL 5.0 is more stable and can work with loops.

Thanks for the help, your query did the job.
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

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

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this article, we’ll look at how to deploy ProxySQL.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

661 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