Help with oracle-sql statment - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Help with oracle-sql statment

Author

Topic

Gazillionaire
Starting Member

4 Posts

Posted - 2011-09-11 : 03:22:59

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
Starting Member

4 Posts

Posted - 2011-09-11 : 05:46:42

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-09-11 : 11:02:27

try dbforums.com. SQLTeam.com is on Microsoft SQL Server

KH
[spoiler]Time is always against us[/spoiler]

Gazillionaire
Starting Member

4 Posts

Posted - 2011-09-14 : 07:37:40

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

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-09-14 : 08:05:57

You really should try an Oracle forum, like www.dbforums, as KHTAN sugested. We really just do Microsoft's t-SQL here, not Oracle.

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources