Obtain week number between years

Obtain week number between years

am 29.04.2010 14:12:24 von Tompkins Neil

--00151747c0fe1eb83004855f0a50
Content-Type: text/plain; charset=ISO-8859-1

Hi

We need to produce a query to return the total number of user visits between
two date ranges that span over two year e.g from 2009-04-29 to 2010-04-29.
My question is how can I compute the totals for each week within a query ?
for example

2009-04-29 to 2009-05-06 100 visits
2009-05-07 to 2009-05-14 250 visits etc

Cheers
Neil

--00151747c0fe1eb83004855f0a50--

Re: Obtain week number between years

am 29.04.2010 14:30:40 von Johan De Meersman

--0016367d6ce86944ed04855f4b9c
Content-Type: text/plain; charset=ISO-8859-1

group by week(yourdate) ?


On Thu, Apr 29, 2010 at 2:12 PM, Tompkins Neil > wrote:

> Hi
>
> We need to produce a query to return the total number of user visits
> between
> two date ranges that span over two year e.g from 2009-04-29 to 2010-04-29.
> My question is how can I compute the totals for each week within a query ?
> for example
>
> 2009-04-29 to 2009-05-06 100 visits
> 2009-05-07 to 2009-05-14 250 visits etc
>
> Cheers
> Neil
>



--
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

--0016367d6ce86944ed04855f4b9c--

Re: Obtain week number between years

am 29.04.2010 14:33:55 von Baron Schwartz

Neil,

I would start with something like this, assuming the date column is called =
"d":

SELECT
FROM


GROUP BY d - INTERVAL DAYOFWEEK(d) DAY;

- Baron

On Thu, Apr 29, 2010 at 8:12 AM, Tompkins Neil
wrote:
> Hi
>
> We need to produce a query to return the total number of user visits betw=
een
> two date ranges that span over two year e.g from 2009-04-29 to 2010-04-29=
..
> My question is how can I compute the totals for each week within a query =
?
> for example
>
> 2009-04-29 to 2009-05-06 =A0 100 visits
> 2009-05-07 to 2009-05-14 =A0 250 visits etc
>
> Cheers
> Neil
>



--=20
Baron Schwartz
Percona Inc
Consulting, Training, Support & Services for MySQL

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg

RE: Obtain week number between years

am 29.04.2010 15:05:29 von Steven Staples

You could also try it this way, and then in the application, you can =
find
out what the dates are, that they are between.


SELECT YEARWEEK(`datefield`) AS 'week',=20
COUNT(`visits`) AS 'visits'
FROM `mytable` WHERE YEAR(`datefield`) =3D '2009'
GROUP BY YEARWEEK(`datefield`);

(this was just taken off the top of my head, but it should give =
something
that you're looking for... hopefully ;) )

Steven Staples


> -----Original Message-----
> From: baron.schwartz@gmail.com [mailto:baron.schwartz@gmail.com] On
> Behalf Of Baron Schwartz
> Sent: April 29, 2010 8:34 AM
> To: MySql
> Subject: Re: Obtain week number between years
>=20
> Neil,
>=20
> I would start with something like this, assuming the date column is
> called "d":
>=20
> SELECT
> FROM


> GROUP BY d - INTERVAL DAYOFWEEK(d) DAY;
>=20
> - Baron
>=20
> On Thu, Apr 29, 2010 at 8:12 AM, Tompkins Neil
> wrote:
> > Hi
> >
> > We need to produce a query to return the total number of user visits
> between
> > two date ranges that span over two year e.g from 2009-04-29 to 2010-
> 04-29.
> > My question is how can I compute the totals for each week within a
> query ?
> > for example
> >
> > 2009-04-29 to 2009-05-06 =A0 100 visits
> > 2009-05-07 to 2009-05-14 =A0 250 visits etc
> >
> > Cheers
> > Neil
> >
>=20
>=20
>=20
> --
> Baron Schwartz
> Percona Inc
> Consulting, Training, Support & Services for MySQL
>=20
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: =
http://lists.mysql.com/mysql?unsub=3Dsstaples@mnsi.net
>=20
> No virus found in this incoming message.
> Checked by AVG - www.avg.com
> Version: 9.0.814 / Virus Database: 271.1.1/2783 - Release Date:
> 04/29/10 02:27:00


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=3Dgcdmg-mysql-2@m.gmane.o rg