1

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.

Community
  • 1
  • 1
computmaxer
  • 1,677
  • 17
  • 28

1 Answers1

3

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;
Ashalynd
  • 12,363
  • 2
  • 34
  • 37
  • 1
    How will it produce data for 2nd, 3rd, etc. 14-day intervals? – PM 77-1 Oct 16 '13 at 02:13
  • period in the inner query is the number of 14-day chunks, counted from CURDATE() i.e. today. This is used in the outer query to recalculate first and last day of that period (I forgot to multiply it again by 14). – Ashalynd Oct 16 '13 at 02:14
  • Ah wait, you have a starting date, not the CURDATE(). Will make the changes. – Ashalynd Oct 16 '13 at 02:17
  • I'm not the OP. Just an observer. – PM 77-1 Oct 16 '13 at 02:18
  • I see:) Corrected for the fact that there is the starting date. – Ashalynd Oct 16 '13 at 02:19
  • 1
    In `GROUP BY` in the last line should be `period, name`. – PM 77-1 Oct 16 '13 at 02:32
  • let us [continue this discussion in chat](http://chat.stackoverflow.com/rooms/39314/discussion-between-ashalynd-and-pm-77-1) – Ashalynd Oct 16 '13 at 02:35
  • Everything seems to be working. Here's [**SQL Fiddle**](http://sqlfiddle.com/#!2/3d7af/1) with **your** solution. – PM 77-1 Oct 16 '13 at 02:37
  • Thanks for the great answer! I just discovered that since this is not MySQL we need to modify the DATE_ADD call a little: `DATE_ADD('2013-10-01', period*INTERVAL '14' day)` – computmaxer Oct 16 '13 at 06:25
  • I do have one problem with this however, still. It fails on `GROUP BY period, AssignedTo` stating "Unknown column: period". Is there a different SQL syntax that will allow this to work? – computmaxer Oct 16 '13 at 06:30
  • Aha, here is a solution to that specific issue: http://stackoverflow.com/questions/497241/how-do-i-perform-a-group-by-on-an-aliased-column-in-ms-sql-server/497260#497260 . Just need to wrap it in another SELECT, unfortunately. – computmaxer Oct 16 '13 at 06:36
  • MySQL is more forgiving. Glad that you got it worked! – Ashalynd Oct 16 '13 at 07:01
  • It claims to not have a `DATEDIFF` function. This is probably more googlable but if anyone has any solutions I'd greatly appreciate it. I'll be looking more in the morning. – computmaxer Oct 16 '13 at 07:25