Databases
StackOverflow2013
Postsdbo
POUsing Left Join based on date tables condition so that know who employees is absent
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POUsing Left Join based on date tables condition so that know who employees is absent
    primarykey
    data
    text
    <p>I have two table From <code>microsoft Access Database</code> like this</p> <p>1.HR_Personnel</p> <pre><code>+-----+----------+----------------------+ | ID | NIP | Name | +----------------+----------------------+ | 1 | 200 | Teguh | | 2 | 201 | Supomo | | 3 | 203 | DHINI ADHITYAS M | | 4 | 204 | Dhani Amanda | +-----+----------+----------------------+ </code></pre> <p>TA_Record_Info</p> <pre><code>+---------+-----------------------+ | Per_Code| Date_Time | +---------+-----------------------+ | 3 | 2013-02-20 07:45:57 | | 2 | 2013-02-20 07:46:13 | | 1 | 2013-02-20 08:48:07 | | 1 | 2013-02-20 15:53:40 | | 3 | 2013-02-20 16:01:02 | | 2 | 2013-02-21 07:31:57 | | 3 | 2013-02-21 07:39:29 | | 3 | 2013-02-21 15:51:47 | | 2 | 2013-02-21 16:11:21 | | 2 | 2013-02-22 07:47:45 | | 1 | 2013-02-22 07:53:31 | | 3 | 2013-02-22 16:01:43 | | 2 | 2013-02-22 16:11:19 | | 1 | 2013-02-22 16:15:26 | +---------+-----------------------+ </code></pre> <p>Expected Result <strong>EDIT</strong></p> <pre><code>+-------+-----------------+-------------+-------------+-------------+ | NIP | Nama | adate | InTime | OutTime | +-------+-----------------+-------------+-------------+-------------+ | 201 | Teguh | 2013-02-20 | 08:48:07 | 15:53:40 | | 202 | Supomo | 2013-02-20 | 07:46:13 | - | | 203 | DHINI ADHITYAS M| 2013-02-20 | 07:45:57 | 16:01:02 | | 204 | Dhani Amanda | 2013-02-20 | - | - | | 201 | Teguh | 2013-02-21 | - | - | | 202 | Supomo | 2013-02-21 | 07:31:57 | 16:11:21 | | 203 | DHINI ADHITYAS M| 2012-08-21 | 07:39:29 | 15:51:47 | | 204 | Dhani Amanda | 2013-02-21 | - | - | | 201 | Teguh | 2012-08-22 | 07:53:31 | 16:15:26 | | 202 | Supomo | 2012-08-22 | 07:47:45 | 16:11:19 | | 203 | DHINI ADHITYAS M| 2012-08-22 | - | 16:01:43 | | 204 | Dhani Amanda | 2013-02-22 | - | - | +-------+-----------------+-------------+-------------+-------------+ </code></pre> <p>I have try with this Query <strong>EDIT</strong></p> <pre><code>SELECT p.NIP AS NIP, p.Name AS Nama, Format (a.Date_Time, 'yyyy-mm-dd') as adate, IIF((Min(a.Date_Time) &lt;&gt; Max(a.Date_Time)), Format (Min(a.Date_Time), 'hh:mm:ss'), IIF( Format (Min(a.Date_Time), 'hh:mm:ss') &lt; '12:00:00', Format (Min(a.Date_Time), 'hh:mm:ss'), '-' ) )as InTime, IIF((Max(a.Date_Time) &lt;&gt; Min(a.Date_Time)), Format (Max(a.Date_Time), 'hh:mm:ss'), IIF( Format (Max(a.Date_Time), 'hh:mm:ss') &gt; '12:00:00', Format (Max(a.Date_Time), 'hh:mm:ss'), '-' ) )as OutTime FROM HR_Personnel AS p LEFT JOIN TA_Record_Info a ON p.ID = a.Per_Code GROUP BY p.Per_Code, p.Per_Name, Format (a.Date_Time, 'yyyy-mm-dd') Order BY Format (a.Date_Time, 'yyyy-mm-dd'), Right(p.Per_Code,2), p.Per_Name </code></pre> <p>but the results display like this <strong>EDIT</strong></p> <pre><code>+-------+-----------------+-------------+-------------+-------------+ | NIP | Nama | adate | InTime | OutTime | +-------+-----------------+-------------+-------------+-------------+ | 204 | Dhani Amanda | | | - | | 201 | Teguh | 2013-02-20 | 08:48:07 | 15:53:40 | | 202 | Supomo | 2013-02-20 | 07:46:13 | - | | 203 | DHINI ADHITYAS M| 2013-02-20 | 07:45:57 | 16:01:02 | | 202 | Supomo | 2013-02-21 | 07:31:57 | 16:11:21 | | 203 | DHINI ADHITYAS M| 2012-08-21 | 07:39:29 | 15:51:47 | | 201 | Teguh | 2012-08-22 | 07:53:31 | 16:15:26 | | 202 | Supomo | 2012-08-22 | 07:47:45 | 16:11:19 | | 203 | DHINI ADHITYAS M| 2012-08-22 | - | 16:01:43 | +-------+-----------------+-------------+-------------+-------------+ </code></pre> <p>I think the results of my query as it caused by i left join using </p> <blockquote> <p>ON p.ID=a.Per_Code</p> </blockquote> <p><strong>EDIT</strong> so teguh are not present at 2013-02-21 date is not displayed. Table only shows Dhani Amanda who was absent from the date 2013-02-22 - 2013-02-22 in overall.</p> <p>I just wanted to show employee roomates table is not present in all the particular date or Dates marked with Intime and OutTime empty</p> <p>Finnally what must i change from my query? I hope you can help me. thanks.</p> <p><strong>UPDATE</strong></p> <p>I was wrong.I write upside down between the table result and expected result table.And then the result table not present <code>Teguh</code> as employees who are not present.I have change my explain. And then in the query</p> <pre><code>SELECT p.NIP AS NIP, p.Name AS Nama, </code></pre> <p>change by </p> <pre><code>SELECT p.NIP AS NIP, p.Name AS Nama, </code></pre> <p>I have edit my question with <code>EDIT</code> tag. thanks.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USMohamad Damba Putrabangga
    1. USMohamad Damba Putrabangga
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POUsing Left Join based on date tables condition so that know who employees is absent
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POUsing Left Join based on date tables condition so that know who employees is absent
      1. This table or related slice is empty.
      1. VTModeratorReview
    1. COSo the question is; why does Dhani Amanda keep showing up, and always with a 2013-02-20 date? Are you POSITIVE there isn't a record for this person in TA_Record_Info?
      singulars
      1. POUsing Left Join based on date tables condition so that know who employees is absent
      1. USJohnny Bones
    2. COAlso `p.Per_Code AS NIP` on the first line of your `SELECT` seems wrong as Per_Code does not exist in the table `P`/`HR_Personnel`. It needs to be `p.ID` or `a.Per_Code`, but most likely the first because of the `LEFT JOIN`. Same thing for the `Group By`.
      singulars
      1. POUsing Left Join based on date tables condition so that know who employees is absent
      1. USuser2088176
    3. COTo user2088176:I have edit the query
      singulars
      1. POUsing Left Join based on date tables condition so that know who employees is absent
      1. USMohamad Damba Putrabangga
 

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