Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You would left outer join the table to itself:</p> <pre><code>select a.punchId from timePunches a left outer join timePunches b on a.punchId = b.punchDTC_LINK where b.punchDTC_LINK is null and a.punchDirection = 'IN'; </code></pre> <p>This will return the stuff you are looking for, I think.</p> <p>I edited to add the check for punchDirection of in.</p> <p>You can also change the select piece to select out a.*, which would get you everything from the record.</p> <p>Additional Edit:</p> <p>The code above appears to have solved the original problem but what the real problem is when someone checks in twice in a row, then checks out. In MYSQL, this is an incredibly difficult problem, but in Postgres &amp; Oracle, it is relatively simple, and can be solved with windowing functions. I know that this does not solve your problem, but you can probably work out the problem with a script after the fact. In postgres/oracle, the following would find the records that you are looking for:</p> <pre><code>select * from (select a.punchId, lag(b.punchDTC_LINK,1) over ( partition by a.employeeId order by a.punchDATETIME ) prev_link, a.punchDTC_LINK cur_link from timePunches a left outer join timePunches b on a.punchId = b.punchDTC_LINK) where prev_link is null and cur_link is not null; </code></pre> <p>Or something similar. I haven't tested it.</p> <p>As for a script, you could use the following query:</p> <pre><code>select a.punchId, a.employeeID, a.punchDATETIME, a.punchDIRECTION, b.punchDATETIME, b.punchDIRECTION from timePunches a left outer join timePunches b on a.punchId = b.punchDTC_LINK order by a.employeeID, a.punchDATETIME desc; </code></pre> <p>and by looping over the data, and/or writing a script to loop over the data and find places where the b.punchDIRECTION is null and it is not the first record for a given employee, you will easily be able to find the bad records.</p>
    singulars
    1. This table or related slice is empty.
    1. POFind missing MySQL Data
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USHorus
    1. USHorus
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. COUnfortunately, that didn't work :( That is still returning just the IN's. You see, when a user punches `out`, the `punchDTC_LINK` is set to whatever the time was when they punched `in`. So, when they punch OUT, they actually have TWO records for the day. I need to find out when there is a missing OUT. I.E. they have punched IN, but didn't punch OUT.
      singulars
      1. PO
      1. This table or related slice is empty.
    2. COI have updated the question to be more clear, sorry for that. Please review when you can, and hopefully we can work this out :)
      singulars
      1. PO
      1. This table or related slice is empty.
    3. COBut this did solve the original problem, right, IN's without an out? Just making sure I understand the problem properly.
      singulars
      1. PO
      1. USHorus
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload