CODEWITHSUNDEEP
oracle-database
code_Finder
code_Finder

Reputation: 305

Check whether a date exist in a specific month

Here are some sample rows from my ATTENDANCE table

    EMP_NO   RECORD_DATE CLOCKIN CLOCKOUT

    361      09-AUG-16 08:34:17 16:50:17
    361      11-AUG-16 09:09:22 17:32:45
    361      15-AUG-16 08:56:09 16:54:42
    361      22-AUG-16 08:21:58 16:54:43
    361      24-AUG-16 08:29:54 17:02:35
    361      26-AUG-16 08:46:42 19:02:40
    361      29-AUG-16 08:56:15 19:00:52
    361      31-AUG-16 08:31:31 19:00:38

I need to generate an absenteeism report and so I need to find all the dates for a specific employee which doesn't exist in the calendar month.

I thought about following.

SELECT MY_DATE FROM table_FOO 
  where MY_DATE NOT IN (SELECT RECORD_DATE FROM attendance WHERE emp_no='361')

Is there any way to call all the dates into a column (here my_date in table_FOO) and it should be matched to the month.

Kindly seek u r help

Upvotes: 0

Views: 68

Answers (3)

XING
XING

Reputation: 9886

Assuming the table table_foo and attandence date column have same datatype you can use the below query to create our attendence report.

SELECT MY_DATE
  FROM table_FOO
 WHERE NOT EXISTS (SELECT 1
                         FROM attendance
                        WHERE emp_no = '361'
                        and record_date = MY_DATE
                        and to_char(record_date,'mm') = <input_month_number > );

Here input_month_number can be 1 for Jan and so on..12 for Dec.

Upvotes: 1

Wernfried Domscheit
Wernfried Domscheit

Reputation: 59523

Assuming data type of column RECORD_DATE and MY_DATE is DATE or TIMESTAMP (if this is not the case, then you should consider to change that) the query can be as this:

SELECT MY_DATE 
FROM table_FOO 
where TRUNC(MY_DATE, 'MM') NOT IN 
   (SELECT TRUNC(RECORD_DATE, 'MM') FROM attendance WHERE emp_no='361')

Upvotes: 1

Kacper
Kacper

Reputation: 4818

To fill your table_foo you can use loop:

declare
  d date;
begin
  d := to_date('01-01-2016','dd-mm-yyyy');
  for i in 0..364 loop
    insert into table_foo values (d + i);
  end loop;
  commit;
end;

Example code will fill all days for current year.

Upvotes: 1

Related Questions