Professional Web Applications Themes

Group By Week - MySQL

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....

  1. #1

    Default 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.

    battle.chris@gmail.com Guest

  2. #2

    Default Re: Group By Week

    On Mar 7, 1:28 am, com wrote: 

    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/Database/MySQL/Q_22159173.html

    Anoop

    Anoop Guest

  3. #3

    Default Re: Group By Week

    On Mar 7, 6:22 pm, "Anoop" <com> wrote: 



    >
    > 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/Database/MySQL/Q_22159173.html
    >
    > Anoop[/ref]

    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.

    battle.chris@gmail.com Guest

  4. #4

    Default Re: Group By Week

    On Mar 7, 9:08 am, com wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]


    >
    > 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.[/ref]

    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


    strawberry Guest

  5. #5

    Default Re: Group By Week

    On Mar 7, 12:49 pm, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]







    >
    > 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[/ref]

    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 Guest

  6. #6

    Default Re: Group By Week

    On Mar 8, 12:10 am, "strawberry" <com> wrote: 
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]
    > [/ref]



    >
    > 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.[/ref]

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

    battle.chris@gmail.com Guest

Similar Threads

  1. "group by" - order of rows in group
    By aljosa.mohorovic@gmail.com in forum MySQL
    Replies: 1
    Last Post: September 1st, 08:50 AM
  2. May 29 Sydney Developers Group study group
    By 105 in forum Macromedia Flex General Discussion
    Replies: 0
    Last Post: May 25th, 02:56 PM
  3. cfgrid inside a <cfoutput query="myQuery" group="GROUP">
    By DavidGhous in forum Coldfusion Flash Integration
    Replies: 1
    Last Post: April 12th, 07:23 PM
  4. Replies: 3
    Last Post: December 22nd, 10:42 PM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139