Solved

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

Posted on 2004-04-23
5
916 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
  • 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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Suggested Solutions

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…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…

825 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