From: Peter Brawley
Date: April 28 2005 5:01pm
Subject: Re: determing number of Tuesdays within a date range
List-Archive: http://lists.mysql.com/mysql/183318
Message-Id: <427116EA.5040701@earthlink.net>
MIME-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit

James,

 >I need to figure out how many Tuesdays are contained
 >within 1/1/2004 - 5/1/2004, and I need to come up with
 >a result where I know how many of each day of the week
 >is within that date range.

Supposing a table named tbl and datetime columns named d1 and d2, 
something like this (not optimised)...

SELECT
  d1,
  d2,
  @dow1 := DAYOFWEEK(d1) AS dow1,
  @dow2 := DAYOFWEEK(d2) AS dow2,
  @days := DATEDIFF(d2,d1) AS Days,
  @tuesdays := FLOOR( @days / 7 ) +
               IF( @dow1=@dow2,
                   IF( @dow1=3, 1, 0 ),
                   IF( @dow1<=3,
                       IF( @dow2>=3,
                           1,
                           IF( @days>0, 1, 0 )
                         ),
                       IF( @dow2 >=3, 1, 0 )
                     )
                 )
               AS Tuesdays
FROM tbl
ORDER BY d1,d2;

PB

-----

James Black wrote:

>-----BEGIN PGP SIGNED MESSAGE-----
>Hash: SHA1
>
>I have a table with session information, with a start and stop time.
>
>I can aggregate the information, grouping it by hour or weekday.
>
>But, when I group it by hour and weekday, I need to also show the
>average usage, so I need to figure out how many Tuesdays are contained
>within 1/1/2004 - 5/1/2004, and I need to come up with a result where I
>know how many of each day of the week is within that date range.
>
>I am hoping someone may have a solution, as, once I know the number of
>Tues then I can state the average number of sessions on a Tuesday at 4pm.
>
>Thanx for any help.
>
>- --
>"Love is mutual self-giving that ends in self-recovery." Fulton Sheen
>James Black    james@stripped
>-----BEGIN PGP SIGNATURE-----
>Version: GnuPG v1.4.1 (MingW32)
>Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org
>
>iD8DBQFCcPaMikQgpVn8xrARAoSKAJ96AzRIgGXnjbn/Krlxehz7K/HTlgCfSXZe
>VxslEjt9ERmUBiDJoBti3SE=
>=bwx8
>-----END PGP SIGNATURE-----
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.308 / Virus Database: 266.10.4 - Release Date: 4/27/2005