Help with count sql statment

gazillionaire · September 11th, 2011, 03:03 AM

I have employee table, dept table and timeentry table.

employee table - employeecode, employeename, departmentcope.

department table - departmentcode, departmentname

timeentrytable - employeecode, timetype(IN/OUt), Date, Time(7:00)

Here i want a result as

DepartName count(TimeIn)*-

account 256

finance 134

I.T 450

H.R 333

Note: Here only In count there may be mulitple ins and outs but need to find last Ins(here need to check how employees are inside the office in each department wise).

from the above result it means 256 employee are inside the office they belong to account departments

gazillionaire · September 11th, 2011, 05:47 AM

I need to write this query in Oracle.

If you need what each table have here we go

Employee Table:

EmployeeCode EmployeeName DepartmentCode

100 Â A Â 1

200 Â B Â 2

300 Â C 3

Department Table:

DepartmentCode DepartmentName

1 Â H.R

2 Â I.T

3 FINANCE

TimeEntry Table:

EmployeeCode TimeType EntryDate EntryTime

100 Â IN 11-sep-2011 7:00

100 out 11-sep-2011 8:00

100 Â IN 11-sep-2011 8:10

100 out 11-sep-2011 8:50

100 Â IN 11-sep-2011 9:00 --> This is the LastIN i have to count these value for this employee

200 Â IN 11-sep-2011 8:10

200 out 11-sep-2011 16:50

300 Â IN 11-sep-2011 7:10

from the above tables if we make a sql stmt according to my requirement the output should be

OUPUT:

DepartmentName Count(TimeIN)

H.R Â 1 - still in the office

I.T Â 0 - In this case he left the office (Time out entry has been punched)

FINANCE Â 1 - still in the office

Let me know this is clear for you.

Thanks

gazillionaire · September 14th, 2011, 07:45 AM

CREATE TABLE PMS_EMPLOYEE
(

EMPLOYEECODE VARCHAR2(15 BYTE) NOT NULL
, NAME VARCHAR2(100 BYTE)
, DEPARTMENTCODE VARCHAR2(10 BYTE)

, CONSTRAINT PK_PMS_EMPLOYEE PRIMARY KEY
(

, EMPLOYEECODE
)
ENABLE
)
LOGGING
TABLESPACE "USERS"
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 3145728
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
);

Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05611','Khan','01');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05612','chan','02');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05613','tina','03');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05614','bety','04');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05615','brad','04');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05616','kuty','03');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05617','rose','02');
Insert into PMS_EMPLOYEE (EMPLOYEECODE,NAME,DEPARTMENTCODE) values ('05618','jack','01');

CREATE TABLE PMS_DEPARTMENT
(
DEPARTMENTCODE VARCHAR2(10 BYTE) NOT NULL
, DESCRIPTION VARCHAR2(50 BYTE)

, CONSTRAINT PK_PMS_DEPARTMENT PRIMARY KEY
(
, DEPARTMENTCODE
)
ENABLE
)
LOGGING
TABLESPACE "USERS"
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 65536
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
);

Insert into PMS_DEPARTMENT (DEPARTMENTCODE,DESCRIPTIOND) values ('01','HR');
Insert into PMS_DEPARTMENT (DEPARTMENTCODE,DESCRIPTIOND) values ('02','IT');
Insert into PMS_DEPARTMENT (DEPARTMENTCODE,DESCRIPTIOND) values ('03','MA');
Insert into PMS_DEPARTMENT (DEPARTMENTCODE,DESCRIPTIOND) values ('04','FN');

CREATE TABLE TAS_EMPBOOKINGS
(
EMPLOYEECODE VARCHAR2(10 BYTE) NOT NULL
, TRXTYPE VARCHAR2(2 BYTE)
, TRXDATE DATE
, TRXTIME FLOAT(126)

, CONSTRAINT PK_TAS_EMPBOOKINGS PRIMARY KEY
(
EMPLOYEECODE
)
ENABLE
)
LOGGING
TABLESPACE "USERS"
PCTFREE 10
INITRANS 1
STORAGE
(
INITIAL 233832448
MINEXTENTS 1
MAXEXTENTS 2147483645
BUFFER_POOL DEFAULT
);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05611','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),6.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05611','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),15.00);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05612','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),7.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05612','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),10.00);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05612','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),12.36);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05613','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),7.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05613','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),10.00);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05613','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),12.25);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05613','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),13.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05613','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),13.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05613','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),14.16);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),7.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),10.00);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),12.25);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),13.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),13.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),15.16);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05614','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),15.16);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05615','IN',to_timestamp('24-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),11.16);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05616','IN',to_timestamp('24-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),10.00);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05616','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),8.00);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05617','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),7.00);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05617','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),12.30);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05617','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),14.00);

Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05618','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),7.00);
Insert into TAS_EMPBOOKINGS (EMPLOYEECODE,TRXTYPE,TRXDATE,TRXTIME) values ('05618','OU',to_timestamp('25-JAN-11 00.00.00.000000000',
'DD-MON-RR HH24.MI.SS.FF'),16.30);

I need to count the number of employees inside the office in each department wise.

In case of employee 05611 he is In and out - no count

In case of employee 05612 he is In,out and In - need to count him as he is in at 12.36 still inside the office.

In case of employee 05613 he is In,out,In,out and In - need to count him as he is in at 14.16 still inside the office.

In case of employee 05614 he is In,out,In,out and In - need to count him as he is in at 15.16 still inside the office.

In case of employee 05615 he is In at yesterday but still in the office - need to count him as he is in at 11.16. Query should bring today and previous day

employee who are still inside the office in each department.

In case of employee 05616 he is In and out - in was 24 jan and out at next day so no count.

In case of employee 05617 he is In,out and In - need to count him as he is in at 14.00 still inside the office.

Note: Here they can swipe the card multiple times in or out. we need to find the last swipe in and last swipe out to make them countable and not countable.

In this sample data my output should be like this

Here don't show the Department if the count is 0 (no employee are inside the office) only count greater or equal to 1.

Our output case dept HR will not be shown in output data

Description(that is DepartmentName) InCount

02 2 ( as both chan and rose are inside the office need to count)

03 1 ( tina is in and kutty out. need to count tina only )

04 2 ( as both betty and brad are inside the office need to count then but brad was inside the office from
yesterday not gone out we need to consider him in the count as well)

if the create or insert does not run please correct it and then run.

Thanks