SitePoint Sponsor

User Tag List

Results 1 to 4 of 4

Thread: Week commencing

  1. #1
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Week commencing

    I am currently working on a stats program for a helpdesk system using MySQL 5. At the moment I have one table called "calls" that only has three columns: "id" (mediumint), "created" (datetime) and "closed" (datetime) with the following test data:

    Code:
    (12444,'2006-01-15 11:08:55','2006-01-16 12:30:02'),
    (12452,'2006-01-16 08:47:33','2006-01-24 14:15:08'),
    (12460,'2006-01-16 10:53:00',NULL),
    (12461,'2006-01-16 11:00:10',NULL),
    (12463,'2006-01-16 11:28:57','2006-01-20 15:22:46'),
    (12466,'2006-01-16 13:04:12','2006-01-17 15:27:36'),
    (12469,'2006-01-16 13:35:40','2006-01-24 11:42:31'),
    (12470,'2006-01-16 13:53:22','2006-01-23 12:45:47'),
    (12479,'2006-01-16 16:51:16','2006-01-18 13:57:10'),
    (12496,'2006-01-17 10:45:11','2006-01-19 14:12:24'),
    (12497,'2006-01-17 10:47:42',NULL),
    (12499,'2006-01-17 12:28:36',NULL),
    (12516,'2006-01-17 17:09:34','2006-01-26 15:21:49'),
    (12529,'2006-01-18 10:12:08',NULL),
    (12532,'2006-01-18 10:28:44',NULL),
    (12534,'2006-01-18 10:53:50',NULL),
    (12535,'2006-01-18 10:58:14','2006-01-20 15:21:13'),
    (12541,'2006-01-18 14:24:00','2006-01-18 16:57:43'),
    (12542,'2006-01-18 16:12:31',NULL),
    (12544,'2006-01-18 16:16:33',NULL),
    (12551,'2006-01-19 08:56:05',NULL),
    (12553,'2006-01-19 09:12:06','2006-01-19 09:52:03'),
    (12558,'2006-01-19 10:04:23',NULL),
    (12563,'2006-01-19 11:29:39','2006-01-20 10:21:12'),
    (12564,'2006-01-19 12:01:14',NULL),
    (12572,'2006-01-19 14:44:19',NULL),
    (12577,'2006-01-19 15:45:50','2006-01-20 16:14:03'),
    (12578,'2006-01-19 16:11:09','2006-01-27 10:08:34'),
    (12583,'2006-01-20 07:21:54','2006-01-24 10:20:29'),
    (12585,'2006-01-20 08:50:49',NULL),
    (12592,'2006-01-20 10:03:59','2006-01-24 10:17:27'),
    (12608,'2006-01-20 14:28:42',NULL),
    (12609,'2006-01-20 14:36:39','2006-01-20 15:19:21'),
    (12614,'2006-01-20 15:49:06','2006-01-26 14:16:19'),
    (12651,'2006-01-23 17:03:59',NULL),
    (12676,'2006-01-24 15:36:40',NULL),
    (12696,'2006-01-25 11:33:49',NULL),
    (12697,'2006-01-25 11:36:00','2006-01-26 14:22:46'),
    (12698,'2006-01-25 11:45:39',NULL),
    (12710,'2006-01-25 19:05:30',NULL),
    (12717,'2006-01-26 09:38:16',NULL),
    (12727,'2006-01-26 12:14:17',NULL),
    (12738,'2006-01-26 16:40:36',NULL),
    (12750,'2006-01-27 09:19:23',NULL);
    What I need to do is show the total number of calls created and closed each week by the date that week began, e.g.

    Code:
    Week commencing | Calls created
    -------------------------------
    08/01/2006      | 0
    15/01/2006      | 34
    22/01/2006      | 10
    I can get the number of calls created (except when no calls were created) using the following query, but it is the week commencing part that I am struggling with:

    Code:
    SELECT DATE_FORMAT(created, '%U %Y') as week,
    COUNT(*) as total
    FROM calls
    GROUP BY week
    Thanks

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature

  2. #2
    SitePoint Wizard stereofrog's Avatar
    Join Date
    Apr 2004
    Location
    germany
    Posts
    4,324
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Maybe something like
    Code:
    FROM_DAYS(
       TO_DAYS(created) - DAYOFWEEK(created) + 1
    ) AS last_sunday

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    rats, stereofrog beat me to it (i was busy testing it)

    i've used FROM_DAYS(TO_DAYS(...)) before, it's quite agreeable

    Code:
    select from_days(to_days(
              date_add(created
                   , interval 1-dayofweek(created) day)
                         )) as WeekCommencing
         , count(*)         as CallsCreated
      from seanf
    group
        by WeekCommencing    
         
    WeekCommencing CallsCreated
      2006-01-15        34
      2006-01-22        10
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    Mlle. Ledoyen silver trophy seanf's Avatar
    Join Date
    Jan 2001
    Location
    UK
    Posts
    7,168
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Brilliant! Thanks both.

    Sean
    Harry Potter

    -- You lived inside my world so softly
    -- Protected only by the kindness of your nature


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
  •