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