Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Database > Oracle
| Search | Today's Posts | Mark Forums Read
Oracle General Oracle database discussions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Oracle section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
  #1 (permalink)  
Old September 11th, 2011, 03:02 AM
Registered User
Points: 23, Level: 1
Points: 23, Level: 1 Points: 23, Level: 1 Points: 23, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Post Help with count sql statment

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
  #2 (permalink)  
Old September 11th, 2011, 05:47 AM
Registered User
Points: 23, Level: 1
Points: 23, Level: 1 Points: 23, Level: 1 Points: 23, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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
  #3 (permalink)  
Old September 14th, 2011, 07:41 AM
Registered User
Points: 23, Level: 1
Points: 23, Level: 1 Points: 23, Level: 1 Points: 23, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Sep 2011
Posts: 6
Thanks: 0
Thanked 0 Times in 0 Posts
Default posted sample data

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


Similar Threads
Thread Thread Starter Forum Replies Last Post
Having trouble with sql statment. Dumb Dan ASP.NET 2.0 Professional 1 May 17th, 2011 02:42 AM
Help with sql statment Dumb Dan SQL Language 1 May 6th, 2011 01:13 PM
Hard SQL languge statment Questioin Mangore SQL Language 8 October 27th, 2008 03:01 AM
SQL Statment btpoole VB Databases Basics 1 February 18th, 2004 01:03 PM
SQL statment arshad mahmood VB Databases Basics 1 July 1st, 2003 02:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.