CODEWITHSUNDEEP
sqloracle-database
Arief Grando
Arief Grando

Reputation: 209

Get data in range using oracle

Please help me to solve this.

I have a table that contain users check in (checktype = I) and check out (checktype = 0) time everyday, and I would like to get the total amount of check in time per user which occur > 08:00 AM in a specific date range.

I am using the query below, but only handle one day per query not in a range, so I have to loop using javascript to get the amount of delay ( > 08:00 AM) per user for example from 01/06/2012 to 06/06/2012

Please help me to get the amount (count) check in time > 08:00 AM per user (ex: userid 708) from ex:01/06/2012 to 06/06/2012 in a single query.

with tt as 
     ( 
     select TO_DATE('01/06/2012 08:00:00','dd/mm/yyyy hh24:mi:ss') date1 , 
            checktime date2 
     from 
            checkinout 
     where 
            userid = '708' and 
            to_char(checktime,'dd/mm/yyyy') = '01/06/2012' and 
            checktype='I'  -- checktype I is check in
     ) , t2 as 
            ( 
               select numtodsinterval(date2 - date1,'day') dsinterval from tt 
            ) 
            select extract(hour from dsinterval) || ' hours ' || 
                   extract(minute from dsinterval) || ' minutes ' || 
                   round(extract(second from dsinterval)) || ' seconds' late from t2

Upvotes: 0

Views: 574

Answers (2)

GWu
GWu

Reputation: 2787

I assume you wanted to get how many hours late (i.e. after 08:00) the checkins have been done:

with t2 as (
select userid
      ,numtodsinterval(sum(checktime - (trunc(checktime)+8/24)),'day') dsinterval
      ,count(1) cnt
   from checkinout
  where userid='708'
    and checktime > trunc(checktime)+8/24
    and trunc(checktime) between to_date('01/06/2012','DD/MM/YYYY') and to_date('06/06/2012','DD/MM/YYYY')
    and checktype = 'I'
  group by  userid 
)
select extract(hour from dsinterval) || ' hours ' || 
       extract(minute from dsinterval) || ' minutes ' || 
       round(extract(second from dsinterval)) || ' seconds' late 
      ,cnt
   from t2;

See http://sqlfiddle.com/#!4/c4670/11 for my test case.

edit: added column "cnt" to show how many times

Upvotes: 1

jaychapani
jaychapani

Reputation: 1509

Consider the following example on base of this you can write your own logic

WITH tbl AS
     (SELECT SYSDATE dt
        FROM DUAL
      UNION
      SELECT SYSDATE + (1 + (10 / 1440))
        FROM DUAL
      UNION
      SELECT SYSDATE + (2 + (12 / 1440))
        FROM DUAL
      UNION
      SELECT SYSDATE + (3 + (13 / 1440))
        FROM DUAL
      UNION
      SELECT SYSDATE + (6 + (15 / 1440))
        FROM DUAL
      UNION
      SELECT SYSDATE + (8 + (18 / 1440))
        FROM DUAL)
SELECT    EXTRACT (HOUR FROM dsinterval)
       || ' hours '
       || EXTRACT (MINUTE FROM dsinterval)
       || ' minutes '
       || ROUND (EXTRACT (SECOND FROM dsinterval))
       || ' seconds' late
  FROM (SELECT NUMTODSINTERVAL (dt1 - dt2, 'day') dsinterval
          FROM (SELECT TO_DATE (TO_CHAR (dt, 'DD/MM/YYYY') || ' 08:00:00',
                                'DD/MM/YYYY HH24:MI:SS'
                               ) dt1,
                       TO_DATE (TO_CHAR (dt, 'DD/MM/YYYY HH24:MI:SS'),
                                'DD/MM/YYYY HH24:MI:SS'
                               ) dt2
                  FROM tbl
                 WHERE dt BETWEEN SYSDATE + 2 AND SYSDATE + 5))

As per code you can write like

SELECT    EXTRACT (HOUR FROM dsinterval)
       || ' hours '
       || EXTRACT (MINUTE FROM dsinterval)
       || ' minutes '
       || ROUND (EXTRACT (SECOND FROM dsinterval))
       || ' seconds' late
  FROM (SELECT NUMTODSINTERVAL (dt1 - dt2, 'day') dsinterval
          FROM (SELECT TO_DATE (TO_CHAR (checktime , 'DD/MM/YYYY') || ' 08:00:00',
                                'DD/MM/YYYY HH24:MI:SS'
                               ) dt1,
                       TO_DATE (checktime, 'DD/MM/YYYY HH24:MI:SS') dt2
                  FROM checkinout
                 WHERE checktime BETWEEN start_date AND end_date 
                   AND checktype='I'))

Upvotes: 0

Related Questions