Select latest and earliest times within a time group and a pivot statement

Question

I have attandance data that contains a username, time, and status (IN or OUT). I want to show attendance data that contains a name, and the check in/out times. I expect a person to check in and out no more than twice a day. The data looks like this:

As you can see, my problem is that one person can have multiple data entries in different seconds for the same login attempt. This is because I get data from a fingerprint attendace scanner, and the machine in some cases makes multiple entries, sometimes just within 5-10 seconds. I want to select the data to be like this:

How can I identify the proper time for the login attempt, and then select the data with a pivot?

How many in/out paris can you have each day? What are the rules for ignoring multiple ins/outs in a row? — Gordon Linoff, Dec 31 '18 at 01:44
Why is Doe's second `IN` time not being shown? I see no logic for this expected output. — Tim Biegeleisen, Dec 31 '18 at 01:45
@Tim - because Doe have 2 IN, 07:31:35 and 07:31:43 and i just want the latest data — Dwiky Restu, Dec 31 '18 at 01:47
the rules for ignoring multiple ins/out in a row is : if multiple in/out rows have a different in 1 minutes and the latest data is taken — Dwiky Restu, Dec 31 '18 at 01:55
Why are the middle two values empty in your sample results? How do you determine what values go in what columns? — Gordon Linoff, Dec 31 '18 at 02:07

SandPiper · Accepted Answer · 2018-12-31T03:25:17.920

First, you need to normalize your data by removing the duplicate entries. In your situation, that's a challenge because the duplicated data isn't easily identified as a duplicate. You can make some assumptions though. Below, I assume that no one will make multiple login attempts in a two minute window. You can do this by first using a Common Table Expression (CTE, using the WITH clause).

Within the CTE, you can use the LAG function. Essentially what this code is saying is "for each partition of user and entry type, if the previous value was within 2 minutes of this value, then put a number, otherwise put null." I chose null as the flag that will keep the value because LAG of the first entry is going to be null. So, your CTE will just return a table of entry events (ID) that were distinct attempts.

Now, you prepare another CTE that a PIVOT will pull from that has everything from your table, but only for the entry IDs you cared about. The PIVOT is going to look over the MIN/MAX of your IN/OUT times.

WITH UNIQUE_LOGINS AS (
    SELECT ID FROM LOGIN_TABLE 
    WHERE CASE WHEN LAG(TIME, 1, 0) OVER (PARTITION BY USERNAME, STATUS ORDER BY TIME) 
              + (2/60/24) < TIME THEN NULL ELSE 1 END IS NULL ),   -- Times within 2 minutes
TEMP_FOR_PIVOT AS (
    SELECT USERNAME, TIME, STATUS FROM LOGIN_TABLE WHERE ID IN (SELECT ID FROM UNIQUE_LOGINS)
)
SELECT * FROM TEMP_FOR_PIVOT
PIVOT (
    MIN(TIME), MAX(TIME) FOR STATUS IN ('IN', 'OUT')
)

From there, if you need to rearrange or rename your columns, then you can just put that last SELECT into yet another CTE and then select your values from it. There is some more about PIVOT here: Rotate/pivot table with aggregation in Oracle

sorry for late, i tried now and theres an error says : ORA-30483: window functions are not allowed here, i think error is in WITH AS — Dwiky Restu, Jan 02 '19 at 02:36

Select latest and earliest times within a time group and a pivot statement

1 Answers1