Group By Week

Hello.

We have a table called CLIENTS and another table called
SERVICE_REQUESTS with fields fk_clients and request_date. What we'd
like to do is run a query which returns all the weeks in which a
service request has been lodged for any given client with our business
week running from Monday to Sunday. If a client has made three
requests in a week, then only one entry from that week should be
returned (it doesn't matter which one).

Is this possible?

Thanks.

On Mar 7, 1:28 am, battle.ch...@gmail.com wrote:
> Hello.
>
> We have a table called CLIENTS and another table called
> SERVICE_REQUESTS with fields fk_clients and request_date. What we'd
> like to do is run a query which returns all the weeks in which a
> service request has been lodged for any given client with our business
> week running from Monday to Sunday. If a client has made three
> requests in a week, then only one entry from that week should be
> returned (it doesn't matter which one).
>
> Is this possible?
>
> Thanks.

This might give you some helpful hints,... actually the responses were
from me - so feel free to ask me for further info if necessary.
http://www.experts-exchange.com/Data..._22159173.html

Anoop

On Mar 7, 6:22 pm, "Anoop" <anoopkum...@gmail.com> wrote:
> On Mar 7, 1:28 am, battle.ch...@gmail.com wrote:
>
> > Hello.
>
> > We have a table called CLIENTS and another table called
> > SERVICE_REQUESTS with fields fk_clients and request_date. What we'd
> > like to do is run a query which returns all the weeks in which a
> > service request has been lodged for any given client with our business
> > week running from Monday to Sunday. If a client has made three
> > requests in a week, then only one entry from that week should be
> > returned (it doesn't matter which one).
>
> > Is this possible?
>
> > Thanks.
>
> This might give you some helpful hints,... actually the responses were
> from me - so feel free to ask me for further info if necessary.http://www.experts-exchange.com/Data..._22159173.html
>
> Anoop

Thanks Anoop; that's really close.

Your result is returning a count of hits for each week; what I'm after
is a list of clients who made a request in a week, so there may well
be more than one result per week.

An example result might be:

weeki client no calls

0 week of 2003 Hungry Jacks 2
0 week of 2003 McDonalds 1
0 week of 2003 Wendy's 1
1 week of 2003 Wendy's 2
1 week of 2003 Kentucky F Chicken 1
2 week of 2003 McDonalds 1

The no. of calls would be helpful but not essential.

Thanks again.

On Mar 7, 9:08 am, battle.ch...@gmail.com wrote:
> On Mar 7, 6:22 pm, "Anoop" <anoopkum...@gmail.com> wrote:
>
>
>
> > On Mar 7, 1:28 am, battle.ch...@gmail.com wrote:
>
> > > Hello.
>
> > > We have a table called CLIENTS and another table called
> > > SERVICE_REQUESTS with fields fk_clients and request_date. What we'd
> > > like to do is run a query which returns all the weeks in which a
> > > service request has been lodged for any given client with our business
> > > week running from Monday to Sunday. If a client has made three
> > > requests in a week, then only one entry from that week should be
> > > returned (it doesn't matter which one).
>
> > > Is this possible?
>
> > > Thanks.
>
> > This might give you some helpful hints,... actually the responses were
> > from me - so feel free to ask me for further info if necessary.http://www.experts-exchange.com/Data..._22159173.html
>
> > Anoop
>
> Thanks Anoop; that's really close.
>
> Your result is returning a count of hits for each week; what I'm after
> is a list of clients who made a request in a week, so there may well
> be more than one result per week.
>
> An example result might be:
>
> weeki client no calls
>
> 0 week of 2003 Hungry Jacks 2
> 0 week of 2003 McDonalds 1
> 0 week of 2003 Wendy's 1
> 1 week of 2003 Wendy's 2
> 1 week of 2003 Kentucky F Chicken 1
> 2 week of 2003 McDonalds 1
>
> The no. of calls would be helpful but not essential.
>
> Thanks again.

Hmm, this result set doesn't quite match your original request - but
it seems straightforward enough.
You need to clarify your definition of Week 1. Select from the
following:

Week 1 is the first week ...

1 Monday 0-53 with more than 3 days this year
5 Monday 0-53 with a Monday in this year

On Mar 7, 12:49 pm, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 7, 9:08 am, battle.ch...@gmail.com wrote:
>
>
>
> > On Mar 7, 6:22 pm, "Anoop" <anoopkum...@gmail.com> wrote:
>
> > > On Mar 7, 1:28 am, battle.ch...@gmail.com wrote:
>
> > > > Hello.
>
> > > > We have a table called CLIENTS and another table called
> > > > SERVICE_REQUESTS with fields fk_clients and request_date. What we'd
> > > > like to do is run a query which returns all the weeks in which a
> > > > service request has been lodged for any given client with our business
> > > > week running from Monday to Sunday. If a client has made three
> > > > requests in a week, then only one entry from that week should be
> > > > returned (it doesn't matter which one).
>
> > > > Is this possible?
>
> > > > Thanks.
>
> > > This might give you some helpful hints,... actually the responses were
> > > from me - so feel free to ask me for further info if necessary.http://www.experts-exchange.com/Data..._22159173.html
>
> > > Anoop
>
> > Thanks Anoop; that's really close.
>
> > Your result is returning a count of hits for each week; what I'm after
> > is a list of clients who made a request in a week, so there may well
> > be more than one result per week.
>
> > An example result might be:
>
> > weeki client no calls
>
> > 0 week of 2003 Hungry Jacks 2
> > 0 week of 2003 McDonalds 1
> > 0 week of 2003 Wendy's 1
> > 1 week of 2003 Wendy's 2
> > 1 week of 2003 Kentucky F Chicken 1
> > 2 week of 2003 McDonalds 1
>
> > The no. of calls would be helpful but not essential.
>
> > Thanks again.
>
> Hmm, this result set doesn't quite match your original request - but
> it seems straightforward enough.
> You need to clarify your definition of Week 1. Select from the
> following:
>
> Week 1 is the first week ...
>
> 1 Monday 0-53 with more than 3 days this year
> 5 Monday 0-53 with a Monday in this year

Ah, from the results I can see that you're definition is '5'.

So,

SELECT WEEK( sr.request_date, 5 ) weeki,
client_id,count( sr.client_id )
FROM service_requests sr
GROUP BY weeki, client_id
ORDER BY weeki, client_id
LIMIT 0 , 30

The JOIN part of the query has been left as an exercise for the reader.

On Mar 8, 12:10 am, "strawberry" <zac.ca...@gmail.com> wrote:
> On Mar 7, 12:49 pm, "strawberry" <zac.ca...@gmail.com> wrote:
>
>
>
> > On Mar 7, 9:08 am, battle.ch...@gmail.com wrote:
>
> > > On Mar 7, 6:22 pm, "Anoop" <anoopkum...@gmail.com> wrote:
>
> > > > On Mar 7, 1:28 am, battle.ch...@gmail.com wrote:
>
> > > > > Hello.
>
> > > > > We have a table called CLIENTS and another table called
> > > > > SERVICE_REQUESTS with fields fk_clients and request_date. What we'd
> > > > > like to do is run a query which returns all the weeks in which a
> > > > > service request has been lodged for any given client with our business
> > > > > week running from Monday to Sunday. If a client has made three
> > > > > requests in a week, then only one entry from that week should be
> > > > > returned (it doesn't matter which one).
>
> > > > > Is this possible?
>
> > > > > Thanks.
>
> > > > This might give you some helpful hints,... actually the responses were
> > > > from me - so feel free to ask me for further info if necessary.http://www.experts-exchange.com/Data..._22159173.html
>
> > > > Anoop
>
> > > Thanks Anoop; that's really close.
>
> > > Your result is returning a count of hits for each week; what I'm after
> > > is a list of clients who made a request in a week, so there may well
> > > be more than one result per week.
>
> > > An example result might be:
>
> > > weeki client no calls
>
> > > 0 week of 2003 Hungry Jacks 2
> > > 0 week of 2003 McDonalds 1
> > > 0 week of 2003 Wendy's 1
> > > 1 week of 2003 Wendy's 2
> > > 1 week of 2003 Kentucky F Chicken 1
> > > 2 week of 2003 McDonalds 1
>
> > > The no. of calls would be helpful but not essential.
>
> > > Thanks again.
>
> > Hmm, this result set doesn't quite match your original request - but
> > it seems straightforward enough.
> > You need to clarify your definition of Week 1. Select from the
> > following:
>
> > Week 1 is the first week ...
>
> > 1 Monday 0-53 with more than 3 days this year
> > 5 Monday 0-53 with a Monday in this year
>
> Ah, from the results I can see that you're definition is '5'.
>
> So,
>
> SELECT WEEK( sr.request_date, 5 ) weeki,
> client_id,count( sr.client_id )
> FROM service_requests sr
> GROUP BY weeki, client_id
> ORDER BY weeki, client_id
> LIMIT 0 , 30
>
> The JOIN part of the query has been left as an exercise for the reader.

Strawberry, you've solved my problem completely. Thank you!