Skip to Main Content
  • Questions
  • how to count active login hours with minutes of a employee

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, singamaneni venkatesh.

Asked: May 01, 2017 - 12:18 pm UTC

Last updated: May 08, 2017 - 8:51 am UTC

Version: oracle 11g

Viewed 1000+ times

You Asked

how to count login hours with minutes of a employee

my scenario is if a employee login at time 'x' and he continue his work up to a certain time period 'y'.

my question is how to calculate the active login hours of a employee from time 'x' to time 'y'.and generate a report of employee on daily/weekly/monthly like this format


report table like this only

Employee Name,Dept Name,Org. Salary,Total Month shift mins,Present Mins,Absent Min,Gross Salary,OT Amount,Penalty,Net Salary

and Connor said...

No 'create table', no insert statements, no data...no nothing.

Tell you what...we'll do the work for you....

SQL> create table emp_activity
  2   ( empid     int,
  3     status    varchar2(10),
  4     status_date  date
  5   );

Table created.

SQL>
SQL> insert into emp_activity values (1, 'Login',  date '2017-01-01' );

1 row created.

SQL> insert into emp_activity values (1, 'Logout', date '2017-01-04' );

1 row created.

SQL> insert into emp_activity values (1, 'Login',  date '2017-02-01' );

1 row created.

SQL> insert into emp_activity values (1, 'Logout', date '2017-04-04' );

1 row created.

SQL> insert into emp_activity values (1, 'Login',  date '2017-05-01' );

1 row created.

SQL> insert into emp_activity values (1, 'Logout', date '2017-08-06' );

1 row created.

SQL> insert into emp_activity values (1, 'Login',  date '2017-09-01' );

1 row created.

SQL> insert into emp_activity values (1, 'Logout', date '2017-11-07' );

1 row created.

SQL>
SQL>
SQL> insert into emp_activity values (2, 'Login',  date '2017-01-01' );

1 row created.

SQL> insert into emp_activity values (2, 'Logout', date '2017-01-04' );

1 row created.

SQL> insert into emp_activity values (2, 'Login',  date '2017-01-14' );

1 row created.

SQL> insert into emp_activity values (2, 'Logout', date '2017-01-25' );

1 row created.

SQL> insert into emp_activity values (2, 'Login',  date '2017-03-01' );

1 row created.

SQL> insert into emp_activity values (2, 'Logout', date '2017-03-06' );

1 row created.

SQL> insert into emp_activity values (2, 'Login',  date '2017-03-25' );

1 row created.

SQL> insert into emp_activity values (2, 'Logout', date '2017-03-31' );

1 row created.

SQL>
SQL> select empid, sum(case when status = 'Logout' then status_date - prev_status_date end ) duration
  2  from (
  3    select empid, status, status_date,
  4          lag(status_date) over ( partition by empid order by status_date) prev_status_date
  5    from emp_activity
  6  )
  7  group by empid;

     EMPID   DURATION
---------- ----------
         1        229
         2         25


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

it's not correct answer please read the requirement properly

singamaneni venkatesh kumar, May 02, 2017 - 11:16 am UTC

it's not correct answer please read the requirement properly.

i want the result table should consist of these columns.

table(Employee Name,Dept Name,Org. Salary,Total Month shift mins,Present Mins,Absent Min,Gross Salary,OT Amount,Penalty,Net Salary)

What are You really asking ?

anonymous, May 02, 2017 - 12:30 pm UTC

Hi

I think that You have now misinterpeted what the AskTom is for.

As I see it, purpose of AskTom is not to Do the work for You. I tries to give You guidence how to utilize oracle features in Your tasks.

Now You were given instructions how to gather logins times.

Most of the 'requirements' are very much application dependent, e.g. how penalty or overtime is calculates or are coming from some other systems, e.g. salary, names etc.


How on earth You think that You should have gotten better answer?

Connor McDonald
May 03, 2017 - 12:58 am UTC

It's my mistake...I should used my crystal ball to work out the table definitions, the data, and all the required calculations :-)

CRYSTAL_BALL

Wonderful response Connor!

A reader, May 05, 2017 - 6:37 pm UTC

Nice crystal ball!
Chris Saxon
May 08, 2017 - 8:51 am UTC

:)

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.