SQLite query to find datetime difference between multiple rows-CodePudding

Here are my two tables' structures in SQLite

CREATE TABLE user
(
    id integer PRIMARY KEY,
    name TEXT
);

CREATE TABLE attendanceTable
(
    id Integer,
    mydate datetime,
    startJob boolean
);

if startJob is 1 it implies that the employee is starting the job and if startJob is 0 it means employee is stopping the job.

attendanceTable is sorted by mydate column

I want output as worked hour by individual employees.

Input of query can be two different dates e.g. 2021-08-20 and 2021-08-22

From which I want to know "How much each person has worked?"

Output should be:

[id, name, userWorkedTime]    
[1, Alice, 09:00]  
[2, Bob, 07:00]

12:00 to 16:00 22:00 to 03:00 = 9 hours
13:00 to 17:00 12:00 to 15:00 = 7 hours

Input of query 2021-08-20 and 2021-08-21 - output should be:

[id, name, userWorkedTime]    
[1, Alice, 09:00]  
[2, Bob, 04:00]

12:00 to 16:00 22:00 to 03:00 = 9 hours
13:00 to 17:00 = 4 hours

It may possible that Alice starts her job at 11 PM and end her job at 3 AM on next day[So working hour would be 4 hours]

CodePudding user response：

I believe that the following will accomplish the results you desire:-

WITH 
    /* The date selection parameters - change as necessary */
    cte_selection(selection_start,selection_end) AS (SELECT '2020-08-20','2020-08-22'),
    /* Extract data per shift - aka combine start and end
        note that extract is 1 day befor and 1 day after actual selection criteria
        as previous/subsequent days may be relevant
    */
    cte_part1(userid,name,periodstart,periodend,duration) AS 
    (
        SELECT 
            user.id,
            name, 
            strftime('%s',mydate), 
            strftime('%s',
                (
                    SELECT mydate 
                    FROM attendancetable 
                    WHERE id = at.id 
                        AND NOT startjob 
                        AND mydate > at.mydate 
                    ORDER BY mydate ASC 
                    LIMIT 1
                )
            ) AS endjob,
            (strftime('%s',
                (
                    SELECT mydate 
                    FROM attendancetable 
                    WHERE id = at.id 
                        AND NOT startjob 
                        AND mydate > at.mydate 
                    ORDER BY mydate ASC 
                    LIMIT 1
                )
            ) - strftime('%s',at.mydate)) AS duration
        FROM attendancetable AS at 
        JOIN user ON at.id = user.id 
        WHERE startjob 
            AND mydate 
                BETWEEN date
                (
                    (SELECT selection_start FROM cte_selection)
                    ,'-1 day'
                ) 
                AND date
                (
                    (SELECT selection_end FROM cte_selection)
                    ,' 1 day'
                )
    ),
    /* split times if period crosses a day*/
    cte_part2(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS
    (
        SELECT 
            userid,
            name,
            periodstart,
            date(periodstart,'unixepoch') AS startdate,
            periodend,
            date(periodend,'unixepoch') AS enddate,
            duration,
            CASE
                WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN duration
                ELSE strftime('%s',date(periodstart,'unixepoch')||'24:00:00') - periodstart
            END AS startday_duration,
            CASE
                WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN 0
                ELSE periodend - strftime('%s',date(periodend,'unixepoch')||'00:00:00')
            END AS nextday_duration
        FROM cte_part1      
    ),
    /* generate new rows for following days */
    cte_part3(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS 
    (
        SELECT 
            userid, 
            name, 
            strftime('%s',date(periodend,'unixepoch')||'00:00:00'),
            date(periodend,'unixepoch'),
            periodend,
            enddate,
            nextday_duration,
            nextday_duration,
            0
        FROM cte_part2 
        WHERE nextday_duration
    ),
    /* combine both sets */
    cte_part4 AS (SELECT * FROM cte_part2 UNION ALL SELECT * FROM cte_part3)
/* Group the final data */
SELECT *,time(sum(startday_duration),'unixepoch') AS time_worked
FROM cte_part4
WHERE startdate BETWEEN (SELECT selection_start FROM cte_selection) AND (SELECT selection_end FROM cte_selection) GROUP BY userid
;

e.g. :-

and :-

Note All results with the exception of the time_worked are arbitrary values from the underlying data. However, userid and name will be correct as they would be the same for each group. The other values will be a value from the group.

you can easily apply changes to the final query to include or exclude columns.

The full testing SQL being :-

DROP TABLE IF EXISTS user;
CREATE TABLE IF NOT EXISTS user (id integer PRIMARY KEY,name TEXT);
DROP TABLE IF EXISTS attendanceTable ;
CREATE TABLE attendanceTable(id Integer,mydate datetime,startJob boolean);
INSERT INTO user VALUES (1,'Alice'),(2,'Bob');
INSERT INTO attendanceTable VALUES
    (1,'2020-08-20 12:00:00',1),
    (2,'2020-08-20 13:00:00',1),
    (1,'2020-08-20 16:00:00',0),
    (2,'2020-08-20 17:00:00',0),
    (1,'2020-08-20 22:00:00',1),
    (1,'2020-08-21 03:00:00',0),
    (2,'2020-08-22 12:00:00',1),
    (2,'2020-08-22 15:00:00',0)
;
WITH 
    /* The date selection parameters - change as necessary */
    cte_selection(selection_start,selection_end) AS (SELECT '2020-08-20','2020-08-22'),
    /* Extract data per shift - aka combine start and end
        note that extract is 1 day befor and 1 day after actual selection criteria
        as previous/subsequent days may be relevant
    */
    cte_part1(userid,name,periodstart,periodend,duration) AS 
    (
        SELECT 
            user.id,
            name, 
            strftime('%s',mydate), 
            strftime('%s',
                (
                    SELECT mydate 
                    FROM attendancetable 
                    WHERE id = at.id 
                        AND NOT startjob 
                        AND mydate > at.mydate 
                    ORDER BY mydate ASC 
                    LIMIT 1
                )
            ) AS endjob,
            (strftime('%s',
                (
                    SELECT mydate 
                    FROM attendancetable 
                    WHERE id = at.id 
                        AND NOT startjob 
                        AND mydate > at.mydate 
                    ORDER BY mydate ASC 
                    LIMIT 1
                )
            ) - strftime('%s',at.mydate)) AS duration
        FROM attendancetable AS at 
        JOIN user ON at.id = user.id 
        WHERE startjob 
            AND mydate 
                BETWEEN date
                (
                    (SELECT selection_start FROM cte_selection)
                    ,'-1 day'
                ) 
                AND date
                (
                    (SELECT selection_end FROM cte_selection)
                    ,' 1 day'
                )
    ),
    /* split times if period crosses a day*/
    cte_part2(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS
    (
        SELECT 
            userid,
            name,
            periodstart,
            date(periodstart,'unixepoch') AS startdate,
            periodend,
            date(periodend,'unixepoch') AS enddate,
            duration,
            CASE
                WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN duration
                ELSE strftime('%s',date(periodstart,'unixepoch')||'24:00:00') - periodstart
            END AS startday_duration,
            CASE
                WHEN date(periodstart,'unixepoch') = date(periodend,'unixepoch') THEN 0
                ELSE periodend - strftime('%s',date(periodend,'unixepoch')||'00:00:00')
            END AS nextday_duration
        FROM cte_part1      
    ),
    /* generate new rows for following days */
    cte_part3(userid,name,periodstart,startdate,periodend,enddate,duration,startday_duration,nextday_duration) AS 
    (
        SELECT 
            userid, 
            name, 
            strftime('%s',date(periodend,'unixepoch')||'00:00:00'),
            date(periodend,'unixepoch'),
            periodend,
            enddate,
            nextday_duration,
            nextday_duration,
            0
        FROM cte_part2 
        WHERE nextday_duration
    ),
    /* combine both sets */
    cte_part4 AS (SELECT * FROM cte_part2 UNION ALL SELECT * FROM cte_part3)
/* Group the final data */
SELECT *,time(sum(startday_duration),'unixepoch') AS time_worked
FROM cte_part4
WHERE startdate BETWEEN (SELECT selection_start FROM cte_selection) AND (SELECT selection_end FROM cte_selection) GROUP BY userid
;
DROP TABLE IF EXISTS user;
DROP TABLE IF EXISTS attendanceTable ;