sqlnexusdb

Group by date, day interval in SQL query

Say I have a table similar to the following (simplified):

| Name  | Date     | Hours |
| Bob   | 10/1/13  |  5    |
| John  | 10/1/13  |  8    |
| Bob   | 10/2/13  |  6    |
| Ashley| 10/2/13  |  4    |
...
| Bob   | 10/17/13 |  4    |
| John  | 10/17/13 |  6    |
| John  | 10/18/13 |  3    |
...

Given a starting date (say 10/1/13), how can I construct a query to group Name, SUM(Hours) by 14-day intervals? So that the result would be something like:

| Name  | Period              | SUM(Hours) |
| Bob   | 10/1/13 - 10/14/13  |  11        |
| John  | 10/1/13 - 10/14/13  |  8         |
| Ashley| 10/1/13 - 10/14/13  |  4         |
| Bob   | 10/15/13 - 10/29/13 |  4         |
| John  | 10/15/13 - 10/29/13 |  9         |

I have tried suggestions listed in posts, such as this: Group by date range on weeks/months interval But they usually assume you want actual weeks. Since these are just 14-day intervals, they are not necessarily aligned with the weeks of the year.

Any suggestion or guidance appreciated!

Edit: This is querying a NexusDB server, so it uses the SQL:2003 standard.

Solution

  • May be something like this could work? (Assuming it's MySQL)

    UPDATE: tested in SQL Fiddle (thanks @pm-77-1): http://sqlfiddle.com/#!2/3d7af/2

    The word of caution: due to the fact that we are doing date arithmetic here, this query might become heavy if it will be run on a large table. 

    SELECT Name, 
CONCAT(DATE_ADD(<your-starting-date>, INTERVAL period*14 day),
" - ",
DATE_ADD(<your-starting-date>, INTERVAL period*14+13 day)) as Period,
Hours FROM 
(SELECT Name, 
FLOOR(DATEDIFF(Date,<your-starting-date>)/14) AS period, 
SUM(Hours) as Hours 
FROM <yourtable> GROUP BY period, name) p;

    UPDATE for NexusDB. I have found out some piece of info for DateDiff replacement in NexusDB:

    http://www.nexusdb.com/support/index.php?q=node/10091

    Taking that into account, you have two choices: either to add that function into your DB, then you don't have to modify the query, or to replace DATEDIFF with that definition:

    SELECT Name, 
CONCAT(DATE_ADD(<your-starting-date>, INTERVAL period*14 day),
" - ",
DATE_ADD(<your-starting-date>, INTERVAL period*14+13 day)) as Period,
Hours FROM 
(SELECT Name, 
FLOOR(cast((cast(Date as float ) - cast(<your-starting-date> as float)) as integer)/14) as period,
SUM(Hours) as Hours 
FROM <yourtable> GROUP BY period, name) p;