Solved

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

Posted on 2004-04-23
5
918 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 250 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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

I use MySQL for many of my development projects in a Windows environment. To manage my databases (and perform queries) for years I used a tool called MySQL administrator.  This tool has since been replaced by MySQL Workbench. So I decided to m…
Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

751 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