CODEWITHSUNDEEP
sqloracle-database
blukit
blukit

Reputation: 113

min/max value within time range in different day

i have a table like this:

[absence_card_no]  [clocking_time]  
040000654           2014-12-04 07:50:00  
160300101           2014-12-13 16:47:00  
160300046           2014-12-27 13:31:00    
160300133           2015-01-05 07:55:00  
160300081           2014-12-27 13:04:00  
020001670           2014-12-15 17:35:00  
020400055           2014-10-27 07:37:00  
160300146           2014-12-27 17:22:00  
020001311           2014-12-27 15:21:00

i need to search min or max value within a time range for each day, and i use this query:

select NC.ABSENCE_CARD_NO, TRUNC(NC.CLOCKING_TIME) daydate, min(NC.CLOCKING_TIME)  
from nna_clocking_tbl nc  
where clocking_time BETWEEN TO_DATE(TO_CHAR(clocking_time,'DD-MM-YYYY')||'16:00:00','DD-MM-YYYY HH24:MI:SS')   
AND TO_DATE(TO_CHAR(clocking_time,'DD-MM-YYYY')||'23:59:00','DD-MM-YYYY HH24:MI:SS')  
group by NC.ABSENCE_CARD_NO, CLOCKING_TIME

so far it's working fine if the range within one day 16:00-23:59, but i need the range to be in different day for example 22:00 sunday until 02:00 monday. I try to modify 'where' clause by substract-1 in clocking time:

clocking_time BETWEEN TO_DATE(TO_CHAR(clocking_time-1,'DD-MM-YYYY')||'22:00:00','DD-MM-YYYY HH24:MI:SS')   
AND TO_DATE(TO_CHAR(clocking_time,'DD-MM-YYYY')||'02:00:00','DD-MM-YYYY HH24:MI:SS')

but it's not working as expected :( note: oracle 11.x

edit:
sorry for being unclear, what i'm looking for is min value within a time range for example 22:00 until 02:00 (next day). My query above only works if the range within one day (00:00-24:00)...i need to include next day in the range. if the where clause:

clocking_time BETWEEN TO_DATE(TO_CHAR(clocking_time-1,'DD-MM-YYYY')||'22:00:00','DD-MM-YYYY HH24:MI:SS') 
                             AND TO_DATE(TO_CHAR(clocking_time,'DD-MM-YYYY')||'01:59:00','DD-MM-YYYY HH24:MI:SS')

it only display result from 00:00-02:00

clocking_time BETWEEN TO_DATE(TO_CHAR(clocking_time,'DD-MM-YYYY')||'22:00:00','DD-MM-YYYY HH24:MI:SS') 
                             AND TO_DATE(TO_CHAR(clocking_time+1,'DD-MM-YYYY')||'01:59:00','DD-MM-YYYY HH24:MI:SS')

it only display result from 22:00-23:59

Upvotes: 1

Views: 992

Answers (2)

Noel
Noel

Reputation: 10525

Your GROUP BY clause seems incorrect in your first query. If there are more than one clocking_time for a card number on same day, you will get both in your result. You should group by trunc(clocking_time).

SQL Fiddle Oracle 11g R2 Schema Setup:

create table myt(
absence_card_no varchar2(10),
clocking_time date
);

insert into myt values('040000654', to_date('2014-12-04 07:50:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('160300101', to_date('2014-12-13 16:47:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('160300046', to_date('2014-12-27 13:31:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('160300081', to_date('2014-12-27 13:04:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('020001670', to_date('2014-12-15 17:35:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('020400055', to_date('2014-10-27 07:37:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('160300146', to_date('2014-12-27 17:22:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('160300133', to_date('2015-01-05 16:21:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('160300133', to_date('2015-01-05 22:55:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('020400055', to_date('2015-01-06 01:55:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('020400055', to_date('2015-01-05 01:55:00','yyyy-mm-dd hh24:mi:ss'));
insert into myt values('020400055', to_date('2015-01-05 22:55:00','yyyy-mm-dd hh24:mi:ss'));

Query 1:

select 
absence_card_no, 
trunc(clocking_time) daydate, 
min(clocking_time)  
from myt
where clocking_time between to_date(to_char(clocking_time,'dd-mm-yyyy')||'16:00:00','dd-mm-yyyy hh24:mi:ss')   
and to_date(to_char(clocking_time,'dd-mm-yyyy')||'23:59:00','dd-mm-yyyy hh24:mi:ss')  
group by absence_card_no, trunc(clocking_time)

Results:

| ABSENCE_CARD_NO |                    DAYDATE |         MIN(CLOCKING_TIME) |
|-----------------|----------------------------|----------------------------|
|       160300101 | December, 13 2014 00:00:00 | December, 13 2014 16:47:00 |
|       160300146 | December, 27 2014 00:00:00 | December, 27 2014 17:22:00 |
|       020001670 | December, 15 2014 00:00:00 | December, 15 2014 17:35:00 |
|       020400055 |  January, 05 2015 00:00:00 |  January, 05 2015 22:55:00 |
|       160300133 |  January, 05 2015 00:00:00 |  January, 05 2015 16:21:00 |

If you want to have range that includes two days, you cant use BETWEEN and give the time range. Instead you can use NOT BETWEEN 02:00 and 22:00. Or you can use clocking_time >= 22:00 or clocking_time < 02:00.

Query 2:

select 
absence_card_no, 
trunc(clocking_time) daydate, 
min(clocking_time)  
from myt
where clocking_time >= to_date(to_char(clocking_time,'dd-mm-yyyy')||'22:00:00','dd-mm-yyyy hh24:mi:ss')   
or clocking_time < to_date(to_char(clocking_time,'dd-mm-yyyy')||'02:00:00','dd-mm-yyyy hh24:mi:ss')  
group by absence_card_no, trunc(clocking_time)

Results:

| ABSENCE_CARD_NO |                   DAYDATE |        MIN(CLOCKING_TIME) |
|-----------------|---------------------------|---------------------------|
|       020400055 | January, 05 2015 00:00:00 | January, 05 2015 01:55:00 |
|       020400055 | January, 06 2015 00:00:00 | January, 06 2015 01:55:00 |
|       160300133 | January, 05 2015 00:00:00 | January, 05 2015 22:55:00 |

But still, this wont give you the correct result. You are grouping by trunc(clocking_time). This wont consider the range if it extends to two days. Since your range extends 2 hours in to next day, simple thing is to group by trunc(clocking_time - interval '2' hour).

Query 3:

select 
absence_card_no, 
trunc(clocking_time - interval '2' hour) daydate, 
min(clocking_time)  
from myt
where clocking_time >= to_date(to_char(clocking_time,'dd-mm-yyyy')||'22:00:00','dd-mm-yyyy hh24:mi:ss')   
or clocking_time < to_date(to_char(clocking_time,'dd-mm-yyyy')||'02:00:00','dd-mm-yyyy hh24:mi:ss')  
group by absence_card_no, trunc(clocking_time - interval '2' hour)

Results:

| ABSENCE_CARD_NO |                   DAYDATE |        MIN(CLOCKING_TIME) |
|-----------------|---------------------------|---------------------------|
|       020400055 | January, 04 2015 00:00:00 | January, 05 2015 01:55:00 |
|       020400055 | January, 05 2015 00:00:00 | January, 05 2015 22:55:00 |
|       160300133 | January, 05 2015 00:00:00 | January, 05 2015 22:55:00 |

Upvotes: 1

riani witania satiajaya
riani witania satiajaya

Reputation: 113

Is this the answer?

select distinct CONVERT(VARCHAR(10),clocking_time,10) as [Date],
b.absence_card_no as min_absence_card, 
c.absence_card_no as max_absence_card
from absence a
inner join 
(
select CONVERT(VARCHAR(10),clocking_time,10) as min_clocking_time, 
absence_card_no 
from absence 
where clocking_time in ( 
    select  Min(clocking_time) as min_clocking_time
    from absence
    GROUP BY CONVERT(VARCHAR(10),clocking_time,10)
 )
) b 
on CONVERT(VARCHAR(10),a.clocking_time,10) = b.min_clocking_time
inner join 
(
   select CONVERT(VARCHAR(10),clocking_time,10) as max_clocking_time, 
   absence_card_no 
   from absence
    where clocking_time in ( 
        select Max(clocking_time) as max_clocking_time
        from absence
        GROUP BY CONVERT(VARCHAR(10),clocking_time,10)
    )
) c
on CONVERT(VARCHAR(10),a.clocking_time,10) = c.max_clocking_time

I did it in SQL SERVER maybe it needs some more conversion for ORACLE

Upvotes: 0

Related Questions