The below is the table:
ID_NO in_time out_time
S162Z 02-JUL-10 08.00.00 02-JUL-10 16.00.00
S162Z 07-OCT-10 10.00.00 08-OCT-10 13.00.00
S162Z 23-FEB-11 08.00.00 23-FEB-11 16.00.00
S162Z 01-FEB-14 09.00.00 12-FEB-14 18.00.00
S162Z 26-NOV-15 08.00.00 27-NOV-15 10.00.00
S682D 03-NOV-14 08.00.01 03-NOV-14 08.00.01
S801C 28-NOV-15 08.00.00 -
S801C 07-OCT-10 10.00.00 -
S801C 23-FEB-11 08.00.00 -
I want to find those id_no who have out time greater than 24 hours based on the in_time of particular month.
Like i want to find those ids based on the below conditions
1) who have out-time greater than 24 hrs based on the in-time for the 'mmyyyy' = '112015'. and also
2) who have no out-time for the particular month 'mmyyyy' = '112015'.
Sample output:
S162Z 26-NOV-15 08.00.00 27-NOV-15 10.00.00
S801C 28-NOV-15 08.00.00 -
1) who have out-time greater than 24 hrs based on the in-time for the 'mmyyyy' = '112015'
You could use TO_CHAR to compare MMYYYY and then subtract out_time and in_time to check if the difference is greater than 24 hours i.e. 1 day.
2) who have no out-time for the particular month 'mmyyyy' = '112015'.
It should be a simple IS NULL condition.
Assuming the data type of your in_time and out_time column to be DATE, your filter predicate would be:
WHERE TO_CHAR(in_time, 'MMYYYY') = TO_CHAR(out_time, 'MMYYYY')
AND out_time > in_time + 1
OR out_time IS NULL;
Working Demo:
SQL> WITH sample_data AS(
2 SELECT 1 ID, SYSDATE -1 in_time, SYSDATE + 2/24 out_time FROM dual UNION ALL
3 SELECT 1, SYSDATE -10, SYSDATE + 2/24 FROM dual UNION ALL
4 SELECT 2, SYSDATE -1/24, SYSDATE + 2/24 FROM dual UNION ALL
5 SELECT 2, SYSDATE -1/24, NULL FROM dual
6 )
7 -- end of sample_Data mimicking a real table
8 SELECT *
9 FROM sample_data
10 WHERE TO_CHAR(in_time, 'MMYYYY') = TO_CHAR(out_time, 'MMYYYY')
11 AND out_time > in_time + 1
12 OR out_time IS NULL;
ID IN_TIME OUT_TIME
---------- -------------------- --------------------
1 15-DEC-2015 10:49:27 16-DEC-2015 12:49:27
1 06-DEC-2015 10:49:27 16-DEC-2015 12:49:27
2 16-DEC-2015 09:49:27
If you want to restrict it to a particular month and year, just add another condition:
AND TO_CHAR(in_time, 'MMYYYY') = '112015'
As Sentinel points out, using TO_CHAR on the column would suppress any regular index on the in_time column, not efficient in terms of performance. The better way would be to use a range condition.
For example,
SQL> WITH sample_data AS(
2 SELECT 1 ID, SYSDATE -1 in_time, SYSDATE + 2/24 out_time FROM dual UNION ALL
3 SELECT 1, SYSDATE -10, SYSDATE + 2/24 FROM dual UNION ALL
4 SELECT 2, SYSDATE -1/24, SYSDATE + 2/24 FROM dual UNION ALL
5 SELECT 2, SYSDATE -1/24, NULL FROM dual
6 )
7 -- end of sample_Data mimicking a real table
8 SELECT *
9 FROM sample_data
10 WHERE in_time >= to_date('112015','mmyyyy')
11 AND in_time < add_months(to_date('112015','mmyyyy'), 1)
12 AND (out_time > in_time + 1
13 OR out_time IS NULL);
ID IN_TIME OUT_TIME
---------- -------------------- --------------------
1 15-DEC-2015 11:38:20 16-DEC-2015 13:38:20
1 06-DEC-2015 11:38:20 16-DEC-2015 13:38:20
2 16-DEC-2015 10:38:20