0

I have a table named tbl_attendance with these columns and values:

Attendance_id          emp_id          time                   status
     1                14100003     2019-09-09 04:00:00          1
     2                14100003     2019-09-09 12:00:00          2
     3                14100004     2019-09-09 22:00:00          1
     4                14100004     2019-09-10 06:00:00          2
     5                14100005     2019-09-09 23:00:00          1
     6                14100005     2019-09-09 23:11:00          1
     7                14100005     2019-09-10 07:00:00          2
     8                14100005     2019-09-10 07:04:00          2

Now i want to have a result like this:

5                14100005     2019-09-09 23:00:00          1
7                14100005     2019-09-10 07:00:00          2

from the result, i want this conditions to be satisfied,

  1. there are duplicate values from employee ID 14100005 but i want to get the first with status 1 and 2

  2. i need to require the condition if the date has no status 1 or 2, then the query will return nothing.

i have this query and it returns nothing:

SELECT DISTINCT DATE(t.time) AS time, t.employee_id
FROM tbl_attendance AS t
JOIN tbl_attendance AS t2
     ON DATE(t.time) = DATE(t2.time) AND t.employee_id = t2.employee_id 
WHERE t.`status` = 1 AND t2.`status` = 2
  AND DATE(t.time) BETWEEN "2019-09-09" AND "2019-09-10"
  AND t.employee_id = 14100005
ORDER BY t.time
Jc John
  • 1,799
  • 2
  • 34
  • 69
  • 1
    Your conditions are conflicting. Hint: You dont need join. Simply use `WHERE status IN (1,2)` and date between your input range and `emp_id = 14100004` – Madhur Bhaiya Sep 19 '19 at 08:35
  • 1
    Another tip would be to avoid using `Date(..)` function on fields inside a `WHERE` clause. This hinders the usage of indexing (if any) defined on the `time` field. Refer: https://stackoverflow.com/questions/799584/what-makes-a-sql-statement-sargable – Madhur Bhaiya Sep 19 '19 at 08:38
  • @Strawberry i want also the status 1 and 2 – Jc John Sep 19 '19 at 09:12
  • @MadhurBhaiya i want to get the two outputs with status 1 and 2 – Jc John Sep 19 '19 at 09:12
  • 1
    Ah - http://sqlfiddle.com/#!9/8594bbe/2 (this does seem ridiculously simple) – Strawberry Sep 19 '19 at 09:13
  • @Strawberry thanks. thats what i need. can you answer formally ? – Jc John Sep 19 '19 at 09:14
  • @Strawberry sorry.. another problem.. again i want to show based on query where it has status 1 and 2. if it has only status 1 then i'll not show it – Jc John Sep 19 '19 at 09:56
  • @Strawberry something like, i want to get the date with plus 1 interval and has status 1 and 2, if a date has no status 2, then i will not include it in my query – Jc John Sep 19 '19 at 09:57
  • @Strawberry can you make some change between the query i've posted above. I think here is the error AS t2 ON DATE(t.time)= DATE (t2.time). – Jc John Sep 19 '19 at 10:00
  • @Strawberry ive already edited my question – Jc John Sep 19 '19 at 10:09
  • @JcJohn besides Strawberry's answer, I have marked two more dupe questions that is similar to your problem. You may use Strawberry's answer, or the ones suggested in the dupe. – Madhur Bhaiya Sep 19 '19 at 10:21

1 Answers1

1

Consider the following:

DROP TABLE IF EXISTS attendance;

CREATE TABLE attendance
(Attendance_id SERIAL PRIMARY KEY
,emp_id INT NOT NULL 
,time DATETIME
,status TINYINT NOT NULL
);

INSERT INTO attendance VALUES
(1,3,'2019-09-09 04:00:00',1),
(2,3,'2019-09-09 12:00:00',2),
(3,4,'2019-09-09 22:00:00',1),
(4,4,'2019-09-10 06:00:00',2),
(5,5,'2019-09-09 23:00:00',1),
(6,5,'2019-09-10 07:00:00',2);

SELECT x.* 
  FROM attendance x 
  JOIN attendance y 
    ON y.emp_id = x.emp_id 
   AND y.attendance_id <> x.attendance_id 
   AND y.status IN(1,2) 
   AND y.status <> x.status 
   AND y.time >= '2019-09-09 00:00:00' 
   AND y.time <= '2019-09-10 06:00:00' 
 WHERE x.emp_id = 4 
   AND x.status IN(1,2) 
   AND x.time >= '2019-09-09 00:00:00' 
   AND x.time <= '2019-09-10 06:00:00';
+---------------+--------+---------------------+--------+
| Attendance_id | emp_id | time                | status |
+---------------+--------+---------------------+--------+
|             4 |      4 | 2019-09-10 06:00:00 |      2 |
|             3 |      4 | 2019-09-09 22:00:00 |      1 |
+---------------+--------+---------------------+--------+
Strawberry
  • 33,750
  • 13
  • 40
  • 57