count mondays in date range. also user defined variables

[blueman378]

Hi there,

 

Is it possible to count the number of times a monday appears in a date range,

 

so eg you pass in 2009-01-01 and 2010-12-31

 

it should spit out a number which is the number of times monday appears in that range.

 

also,

is it possible to have the return value of one column be accessible via a simple variable,

 

i ask because i have something like:

 

SELECT
SUM(
IF(e.eatin_monday_status,1,
IF(p.pickup_monday_status,1,
IF(d.delivery_monday_status,1,0)))+

IF(e.eatin_tuesday_status,1,
IF(p.pickup_tuesday_status,1,
IF(d.delivery_tuesday_status,1,0)))+

IF(e.eatin_wednesday_status,1,
IF(p.pickup_wednesday_status,1,
IF(d.delivery_wednesday_status,1,0)))+

IF(e.eatin_thursday_status,1,
IF(p.pickup_thursday_status,1,
IF(d.delivery_thursday_status,1,0)))+

IF(e.eatin_friday_status,1,
IF(p.pickup_friday_status,1,
IF(d.delivery_friday_status,1,0)))+

IF(e.eatin_saturday_status,1,
IF(p.pickup_saturday_status,1,
IF(d.delivery_saturday_status,1,0)))+

IF(e.eatin_sunday_status,1,
IF(p.pickup_sunday_status,1,
IF(d.delivery_sunday_status,1,0)))) as openweekly

FROM
location_hours_eatin e
LEFT JOIN
location_hours_pickup p
ON e.eatin_locationid = p.pickup_locationid
LEFT JOIN
location_hours_delivery d
ON e.eatin_locationid = d.delivery_locationid
WHERE e.eatin_locationid = 6;

 

and dont want to have to repeat that every time i want to use the value, i want to be able to go eg

 

SELECT
SET @days_open = SUM(
IF(e.eatin_monday_status,1,
IF(p.pickup_monday_status,1,
IF(d.delivery_monday_status,1,0)))+

IF(e.eatin_tuesday_status,1,
IF(p.pickup_tuesday_status,1,
IF(d.delivery_tuesday_status,1,0)))+

IF(e.eatin_wednesday_status,1,
IF(p.pickup_wednesday_status,1,
IF(d.delivery_wednesday_status,1,0)))+

IF(e.eatin_thursday_status,1,
IF(p.pickup_thursday_status,1,
IF(d.delivery_thursday_status,1,0)))+

IF(e.eatin_friday_status,1,
IF(p.pickup_friday_status,1,
IF(d.delivery_friday_status,1,0)))+

IF(e.eatin_saturday_status,1,
IF(p.pickup_saturday_status,1,
IF(d.delivery_saturday_status,1,0)))+

IF(e.eatin_sunday_status,1,
IF(p.pickup_sunday_status,1,
IF(d.delivery_sunday_status,1,0)))) as openweekly,
e.eatin_thursday_total/@days_open as thursday_total

FROM
location_hours_eatin e
LEFT JOIN
location_hours_pickup p
ON e.eatin_locationid = p.pickup_locationid
LEFT JOIN
location_hours_delivery d
ON e.eatin_locationid = d.delivery_locationid
WHERE e.eatin_locationid = 6;

 

is this possible?

 

[jskywalker]

mysql> select dt, dayofweek(dt), dayname(dt) from testdate;
+---------------------+---------------+-------------+
| dt                  | dayofweek(dt) | dayname(dt) |
+---------------------+---------------+-------------+
| 2008-11-14 12:14:08 |             6 | Friday      |
| 2008-11-14 12:14:22 |             6 | Friday      |
| 2010-02-04 12:19:27 |             5 | Thursday    |
+---------------------+---------------+-------------+
3 rows in set (0.00 sec)

mysql> desc testdate;
+-------+------------+------+-----+---------+----------------+
| Field | Type       | Null | Key | Default | Extra          |
+-------+------------+------+-----+---------+----------------+
| id    | tinyint(4) | NO   | PRI | NULL    | auto_increment |
| dt    | datetime   | NO   |     | NULL    |                |
+-------+------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

 

see next link for more info:

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_dayname

[jskywalker]

Hi there,

 

Is it possible to count the number of times a monday appears in a date range,

 

so eg you pass in 2009-01-01 and 2010-12-31

 

 

select round(datediff('2010-12-31','2009-01-01')/7,0);

[blueman378]

cheers, both answers are very close to what i need, except,

 

the previous reply will do an estimate of the days as it simply divides the number of days between two dates by 7 and rounds it to 0 decimal places, so if the date given starts on a tuesday it will consider a monday exists. i need the literal count.

[jskywalker]

And what about including, or excluding the start/end date?

How many mondays you count from '2010-01-04' - '2010-01-11'

If you incluse start and enddate it will be 2, my method only counts 1 monday.

 

Given your example of '2009-01-01' - '2010-12-31' made me do it the simple way,

Now i realize that it wil be more complex if you say:

 

Give the Mondays between '2010-01-03' and '2010-01-05'...(my solution gives 0, but correct answer is 1)

Sorry, i dont know of a simple way to do that in MySQL...