1

I have a scenario of which requires to accumulate the rows data into single line of result row with condition of：

WorkIn = the earliest time from the a day when datetime captured.
LunchIn = the earliest time to lunch hour between 11:50:00 to 13:00:00
LunchOut = time latest time from lunch hour before 13:10:00
WorkOut = time captured the latest after 18:00:00

I have a set of data below:

id       timer
20605    2015-06-16 07:53:00
20605    2015-06-16 12:00:00
20605    2015-06-16 13:06:00
20605    2015-06-16 18:59:00
20605    2015-06-17 07:52:00
20605    2015-06-17 11:52:00
20605    2015-06-17 12:58:00
20605    2015-06-17 18:26:00
20605    2015-06-18 07:56:00
20605    2015-06-18 11:56:00
20605    2015-06-18 12:56:00
20605    2015-06-18 19:18:00
20211    2015-06-16 07:50:00
20211    2015-06-16 12:05:00
20211    2015-06-16 12:59:00
20211    2015-06-16 18:30:00
20211    2015-06-16 18:59:00
20211    2015-06-16 22:02:00
20211    2015-06-17 07:54:00
20211    2015-06-17 11:56:00
20211    2015-06-17 12:59:00
20211    2015-06-17 12:59:00
20211    2015-06-17 18:28:00
20211    2015-06-17 18:28:00
20211    2015-06-17 19:00:00
20211    2015-06-17 22:20:00
20211    2015-06-18 07:41:00
20211    2015-06-18 11:56:00
20211    2015-06-18 12:55:00
20211    2015-06-18 15:03:00

I would like to produce a result of :

id      clockIn               LunchIn               LunchOut              ClockOut
20605    2015-06-16 07:53:00    2015-06-16 12:00:00    2015-06-16 13:06:00    2015-06-16 18:59:00
20605    2015-06-17 07:52:00    2015-06-17 11:52:00    2015-06-17 12:58:00    2015-06-17 18:26:00
20605    2015-06-18 07:56:00    2015-06-18 11:56:00    2015-06-18 12:56:00    2015-06-18 19:18:00
20211    2015-06-16 07:50:00    2015-06-16 12:05:00    2015-06-16 12:59:00    2015-06-16 18:30:00
20211    2015-06-17 07:54:00    2015-06-17 11:56:00    2015-06-17 12:59:00    2015-06-17 12:59:00
20211    2015-06-17 18:28:00    2015-06-17 18:28:00    2015-06-17 19:00:00    2015-06-17 22:20:00
20211    2015-06-18 07:41:00    2015-06-18 11:56:00    2015-06-18 12:55:00    2015-06-18 15:03:00

I've tried union, multi select, but seems not producing the result I want. Can anyone help?

Sachith Muhandiram
  • 2,819
  • 10
  • 45
  • 94
klynix
  • 11
  • 2
  • What you tried so far – Shehary Jul 22 '15 at 02:33
  • can you provide your sql please. – Venkatesh Panabaka Jul 22 '15 at 02:33
  • There seems to be [similar questions](https://stackoverflow.com/questions/10867780/select-multiple-sql-rows-into-one-row), but I think your question is sufficiently different to be not a duplicate. Your selection rule seems to be "for each `id` and date, select each group of four `timer`s, and if less than four `timer`s in the last group, discard them". Am I correct? – Ken Y-N Jul 22 '15 at 03:04
  • 1
    the data surely at least 4 timer in a row. If so happened less than 4,leave it blank. – klynix Jul 22 '15 at 03:21
  • I think this is a good question, but I cannot find an answer! I think experts would say that you cannot really do it in SQL, you should do it in the client (or in stored procedures). You can use [`GROUP_CONCAT`](https://www.percona.com/blog/2013/10/22/the-power-of-mysqls-group_concat/) to decrease the client-side processing by batching up each day into a single row. – Ken Y-N Jul 22 '15 at 06:33
  • I didn't come out any sql at all, I just based on what the result I want, but couldn't figure out how to form the sql statement. – klynix Jul 23 '15 at 08:48

0 Answers0