Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 922
  • Last Modified:

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

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
keeper3
Asked:
keeper3
  • 3
  • 2
1 Solution
 
madwaxCommented:
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
 
madwaxCommented:
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
 
keeper3Author Commented:
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
 
madwaxCommented:
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
 
keeper3Author Commented:
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now