CODEWITHSUNDEEP
mysqlsql
Gerald Zehetner
Gerald Zehetner

Reputation: 606

weekly working report from mysql database

I'm trying to get a weekly working report for a employee from my mysql database.

I have four tables:

stdjobs (ID, NR, Name, Gruppe)
user (ID, vname, nname, NR)
wtime (ID, usr, job, tin, tout)
Groups (idGroups, NR, Name)

In stdjobs are full names of some standard job-codes. In user I have some more details about my users. Groups is not used for this query.

wtime is the main table, where the employees enter their workingtime (by a simple c-prog and a barcode-scanner).

select * from wtime order by id desc limit 5;

returns something like this:

ID    usr    job      tin                  tout
4843  M0005  A001942  2015-11-10 15:18:47  NULL
4842  M0006  A001843  2015-11-10 14:47:23  NULL
4841  M0007  A001814  2015-11-10 13:33:37  NULL
4840  M0007  .000002  2015-11-10 13:27:10  2015-11-10 13:33:37
4839  M0006  A001814  2015-11-10 13:25:19  2015-11-10 14:47:23

tout-fields with NULL represent jobs actually been worked on.

I want to get a weekly per employee report with sums by day and for the whole week. It should look something like that:

Report for Gxxxxxxx Zxxxxxxxxxx

Date             Job      In        Out       Time
Mon, 15.01.2015  A001814  06:58:35  08:45:26  01:46:51
Mon, 15.01.2015  A001925  08:45:26  09:45:26  01:00:00
Mon, 15.01.2015  NULL     NULL      NULL      02:46:51
Thu, 16.01.2015  A001814  06:00:00  08:00:00  02:00:00
Thu, 16.01.2015  A001925  08:00:00  10:00:00  02:00:00
Thu, 16.01.2015  NULL     NULL      NULL      04:00:00
NULL             NULL     NULL      NULL      06:46:51

For now i have following query:

SET lc_time_names = 'de_AT';
SELECT 
    (SELECT 
            CONCAT(vname, ' ', nname)
        FROM
            zeiterf.user
        WHERE
            nr = zeiterf.wtime.usr
        LIMIT 1) AS 'Name',
    DATE_FORMAT(tin, '%a %e.%c.%Y') AS 'Datum',
    IF((SELECT 
                name
            FROM
                zeiterf.stdjobs
            WHERE
                nr = zeiterf.wtime.job
            LIMIT 1) != '',
        (SELECT 
                name
            FROM
                zeiterf.stdjobs
            WHERE
                nr = zeiterf.wtime.job
            LIMIT 1),
        job) AS 'Auftrag',
    TIME(tin) AS 'Ein',
    IF(job != '...ENDE', TIME(tout), NULL) AS 'Aus',
    (IF(job != '...ENDE',
        SEC_TO_TIME(UNIX_TIMESTAMP(tout) - UNIX_TIMESTAMP(tin)),
        NULL)) AS 'Zeit'
FROM
    zeiterf.wtime
WHERE
    usr = 'M0006' AND job != '...ENDE'
        AND DATE(tin) > (DATE_SUB(CURDATE(), INTERVAL 1 WEEK))
GROUP BY usr , DATE(tin) , id;

which works fine for giving me all the rows i need. But I don't know how to insert the sums per day and week.

Current Output:

Name            Datum               Auftrag Ein         Aus         Zeit
"Manuel Rxxxx"  "Mit 4.11.2015"     A001814 06:58:35    08:45:26    01:46:51
"Manuel Rxxxx"  "Mit 4.11.2015"     Pause   08:45:26    08:59:10    00:13:44
"Manuel Rxxxx"  "Mit 4.11.2015"     A001814 08:59:10    09:06:52    00:07:42
"Manuel Rxxxx"  "Mit 4.11.2015"     Allgeme 09:06:52    10:15:01    01:08:09
"Manuel Rxxxx"  "Mit 4.11.2015"     A001814 10:15:01    10:44:35    00:29:34
"Manuel Rxxxx"  "Mit 4.11.2015"     A001860 10:44:35    12:01:19    01:16:44
"Manuel Rxxxx"  "Mit 4.11.2015"     Pause   12:01:19    13:00:22    00:59:03
"Manuel Rxxxx"  "Mit 4.11.2015"     A001860 13:00:22    15:25:13    02:24:51
"Manuel Rxxxx"  "Mit 4.11.2015"     A001814 15:25:13    16:51:08    01:25:55
"Manuel Rxxxx"  "Don 5.11.2015"     Allgeme 07:00:40    07:32:55    00:32:15
"Manuel Rxxxx"  "Don 5.11.2015"     A001860 07:32:55    07:54:22    00:21:27
"Manuel Rxxxx"  "Don 5.11.2015"     SM Allg 07:54:22    08:41:51    00:47:29
"Manuel Rxxxx"  "Don 5.11.2015"     Pause   08:41:51    08:57:44    00:15:53
"Manuel Rxxxx"  "Don 5.11.2015"     SM Allg 08:57:44    10:59:16    02:01:32
"Manuel Rxxxx"  "Don 5.11.2015"     A001814 10:59:16    11:21:09    00:21:53
"Manuel Rxxxx"  "Don 5.11.2015"     SM Allg 11:21:09    12:00:38    00:39:29
"Manuel Rxxxx"  "Don 5.11.2015"     Pause   12:00:38    13:00:13    00:59:35
"Manuel Rxxxx"  "Don 5.11.2015"     SM Allg 13:00:13    13:15:37    00:15:24
"Manuel Rxxxx"  "Don 5.11.2015"     A001814 13:15:37    15:27:08    02:11:31
"Manuel Rxxxx"  "Don 5.11.2015"     A001860 15:27:08    16:47:01    01:19:53
"Manuel Rxxxx"  "Mon 9.11.2015"     Allgeme 07:00:20    07:02:14    00:01:54
"Manuel Rxxxx"  "Mon 9.11.2015"     A001860 07:02:14    07:48:43    00:46:29
"Manuel Rxxxx"  "Mon 9.11.2015"     Pause   07:48:43    08:03:17    00:14:34
"Manuel Rxxxx"  "Mon 9.11.2015"     A001860 08:03:17    12:00:54    03:57:37
"Manuel Rxxxx"  "Mon 9.11.2015"     Pause   12:00:55    12:59:23    00:58:28
"Manuel Rxxxx"  "Mon 9.11.2015"     A001843 12:59:23    16:48:21    03:48:58
"Manuel Rxxxx"  "Die 10.11.2015"    A001843 07:04:25    08:18:06    01:13:41
"Manuel Rxxxx"  "Die 10.11.2015"    Pause   08:18:06    08:18:46    00:00:40
"Manuel Rxxxx"  "Die 10.11.2015"    A001843 08:18:46    08:45:39    00:26:53
"Manuel Rxxxx"  "Die 10.11.2015"    Pause   08:45:39    09:01:55    00:16:16
"Manuel Rxxxx"  "Die 10.11.2015"    A001843 09:01:55    12:02:49    03:00:54
"Manuel Rxxxx"  "Die 10.11.2015"    Pause   12:02:49    12:58:47    00:55:58
"Manuel Rxxxx"  "Die 10.11.2015"    A001843 12:58:47    13:25:19    00:26:32
"Manuel Rxxxx"  "Die 10.11.2015"    A001814 13:25:19    NULL        NULL

Example Data: http://sqlfiddle.com/#!9/79ed6d/3

Can anyone give me a hint?

Upvotes: 0

Views: 99

Answers (1)

David Isla
David Isla

Reputation: 629

SQLFiddle doesn't work right now, but I'll try to explain you how to accomplish your goal.

First, you need to know the week for each tint date with WEEKOFYEAR() function. This value helps you to sum every week separately. Then, you can make a query like this: 

select WEEKOFYEAR(tin) as numWeek,
           DATE(tin) as dateJob, 
           DAYOFWEEK(tin) dayOfWeek,
           usr,
           job,
           (IF( ISNULL(tout), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(tout) ) - UNIX_TIMESTAMP(tin)) as DiffInOut
    from wtime;

Now, you can group your data using with every field (numWeek, usr, dayOfWeek, dateJob and job) to get the detailed query:

select numWeek,
       usr,
       dayOfWeek,
       datejob,
       job,
       sum(DiffInOut)
from
  (
    select WEEKOFYEAR(tin) as numWeek,
           DATE(tin) as dateJob, 
           DAYOFWEEK(tin) dayOfWeek,
           usr,
           job,
           (IF( ISNULL(tout), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(tout) ) - UNIX_TIMESTAMP(tin)) as DiffInOut
    from wtime
  ) result
group by numWeek, usr, dayOfWeek, datejob, job

and later make an union to get only info per week..

union
select numWeek,
       usr,
       dayOfWeek,
       null,
       null,
       sum(DiffInOut)
from
  (
    select WEEKOFYEAR(tin) as numWeek,
           NULL as dateJob, 
           8 dayOfWeek,
           usr,
           job,
           (IF( ISNULL(tout), UNIX_TIMESTAMP(), UNIX_TIMESTAMP(tout) ) - UNIX_TIMESTAMP(tin)) as DiffInOut
    from wtime
  ) result
group by numWeek, usr, dayOfWeek
order by numWeek, usr, dayOfWeek;

SQLFiddle Example

I hope that SQL Fiddle works for you.

Result:

| numWeek |   usr | dayOfWeek |                    datejob |     job | sum(DiffInOut) |
|---------|-------|-----------|----------------------------|---------|----------------|
|      46 | M0005 |         3 | November, 10 2015 00:00:00 | A001942 |          61314 |
|      46 | M0005 |         8 |                     (null) |  (null) |          61314 |
|      46 | M0006 |         3 | November, 10 2015 00:00:00 | A001843 |          61314 |
|      46 | M0006 |         3 | November, 10 2015 00:00:00 | A001814 |              0 |
|      46 | M0006 |         8 |                     (null) |  (null) |          61314 |
|      46 | M0007 |         3 | November, 10 2015 00:00:00 | A001814 |          61314 |
|      46 | M0007 |         3 | November, 10 2015 00:00:00 | .000002 |              0 |
|      46 | M0007 |         8 |                     (null) |  (null) |          61314 |

PD: Field 8 dayOfWeek is a little trick to order week row correctly.

PD2: My query with your SQL Fiddle example, throws this result:

| numWeek |   usr | dayOfWeek |                    datejob |     job | sum(DiffInOut) |
|---------|-------|-----------|----------------------------|---------|----------------|
|      45 | M0006 |         4 | November, 04 2015 00:00:00 | ...ENDE |          50972 |
|      45 | M0006 |         5 | November, 05 2015 00:00:00 | A001860 |           6080 |
|      45 | M0006 |         5 | November, 05 2015 00:00:00 | ...ENDE |         310399 |
|      45 | M0006 |         5 | November, 05 2015 00:00:00 | .000001 |           1935 |
|      45 | M0006 |         5 | November, 05 2015 00:00:00 | .000002 |           4528 |
|      45 | M0006 |         5 | November, 05 2015 00:00:00 | .000031 |          13434 |
|      45 | M0006 |         5 | November, 05 2015 00:00:00 | A001814 |           9204 |
|      45 | M0006 |         8 |                     (null) |  (null) |         396552 |
|      46 | M0006 |         2 | November, 09 2015 00:00:00 | ...ENDE |          51363 |
|      46 | M0006 |         2 | November, 09 2015 00:00:00 | .000001 |            114 |
|      46 | M0006 |         2 | November, 09 2015 00:00:00 | .000002 |           4382 |
|      46 | M0006 |         2 | November, 09 2015 00:00:00 | A001843 |          13738 |
|      46 | M0006 |         2 | November, 09 2015 00:00:00 | A001860 |          17046 |
|      46 | M0006 |         3 | November, 10 2015 00:00:00 | ...ENDE |           1561 |
|      46 | M0006 |         3 | November, 10 2015 00:00:00 | .000002 |           4374 |
|      46 | M0006 |         3 | November, 10 2015 00:00:00 | A001814 |           4924 |
|      46 | M0006 |         3 | November, 10 2015 00:00:00 | A001843 |          25662 |
|      46 | M0006 |         8 |                     (null) |  (null) |         123164 |

Upvotes: 1

Related Questions