Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2011
    Posts
    5

    Unanswered: Need Help with Sql Query

    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. Also we need to consider the employee might be in the shift which he were came in yesterday before 12:00(midnight) and he is still in the office in working hours we need to bring those employees in the count. so the query need to consider previous days as well for the count.



    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


    Thanks
    Last edited by Gazillionaire; 09-12-11 at 05:48.

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    If you need what each table have here we go
    Without specifying at least data type of all involved columns, this is insufficient. Also, for testing reasons, many people here prefer posting a complete test case - CREATE TABLE and INSERT statements.
    from the above tables if we make a sql stmt according to my requirement the output should be. Also we need to consider the employee might be in the shift which he were came in yesterday before 12:00(midnight) and he is still in the office in working hours we need to bring those employees in the count. so the query need to consider previous days as well for the count.
    What shall be the result when there are no rows from yesterday, but the day before yesterday?
    What shall be the result when there is a row from today?
    What shall be the result when there are two rows with the same EMPLOYEECODE+ENTRYDATE+ENTRYTIME values?

    Anyway, you may use FIRST/LAST aggregate function or ROW_NUMBER/RANK/DENSE_RANK analytic function in subquery. Something like this (due to missing test case, it is based on standard HR.EMP table):
    Code:
    select deptno,
      max(ename) keep (dense_rank last order by hiredate) ename
    from emp
    group by deptno;
    
    select deptno, ename
    from
    (
      select deptno, ename,
             row_number() over (partition by deptno order by hiredate desc) rn
      from emp
    )
    where rn = 1
    For further description, consult SQL Reference book, which is available with other Oracle documentation e.g. online on http://tahiti.oracle.com/

  3. #3
    Join Date
    Sep 2011
    Posts
    5

    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

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    Thank you for your test case scripts. It very strange design, as DATE can store time values to seconds.
    Also using TO_TIMESTAMP to construct value for DATE data type is unusual, especially as the time part is empty.
    Just curious: does 0.16 mean 16 minutes or 9 minutes 36 seconds (as it would be its decimal ratio to one hour)?
    It does not change anything but it is really strange design.

    I had to remove primary key on TAS_EMPBOOKINGS.EMPLOYEECODE, as data violate it (there are more entries there for one EMPLOYEECODE).

    I do not understand your comments about different times, but it seems you want actual status of TAS_EMPBOOKINGS (no rows to filter). If some rows are supposed to be filtered from the result set, introduce appropriate WHERE clause to the inner query.
    I also still do not know what shall happen when there are two rows with the same EMPLOYEECODE+ENTRYDATE+ENTRYTIME values, but different TRXTYPE. In this case I suppose that OU should be taken. Otherwise, you should use MIN in the inner query (as 'IN' < 'OU').
    Also (for simplicity) I suppose that TRXDATE does not contain time part, otherwise I would have to add up those two time components regarding Oracle date arithmetics (1 day = 1) instead of simple listing them.

    Just curious what problem did you have with implementing my suggestion. The first example one would result in something like this:
    Code:
    with last_empbookings as
    (
      select employeecode,
             max(trxtype) keep (dense_rank last order by trxdate, trxtime) last_trxtype
      from tas_empbookings
      group by employeecode
    )
    select e.departmentcode, e.name, count( e.employeecode )
    from last_empbookings b
      inner join pms_employee e on b.employeecode = e.employeecode
    where b.last_trxtype = 'IN'
    group by e.departmentcode, e.name
    order by e.departmentcode, e.name;
    This shows employee names with last (based on TRXDATE, TRXTIME) entry 'IN' in TAS_EMPBOOKINGS. The last part is simple - just get rid of E.NAME everywhere in the query.

  5. #5
    Join Date
    Sep 2011
    Posts
    5

    exact output

    Thanks flyboy i really appreciated ur help

    ur output comes like this

    deptcode emplname count
    1062 bbb 1
    1096 aaa 1
    1096 cc 1
    1096 dd 1
    1098 ee 1
    1099 ff 1
    1136 gg 1
    1156 rr 1

    I don't want employee wise i want department wise

    deptcode deptname count
    1062 HR 1
    1096 IT 2
    1098 Sales 6
    1099 Market 4
    1136 Finance 3
    1156 Accounts 10

    does 0.16 mean 16 minutes or 9 minutes 36 seconds (as it would be its decimal ratio to one hour)? this entry wouldn't be like it is a 24 hour time system 1 -24 no points here.

    I also still do not know what shall happen when there are two rows with the same EMPLOYEECODE+ENTRYDATE+ENTRYTIME values, but different TRXTYPE. In this case I suppose that OU should be taken. Otherwise, you should use MIN in the inner query (as 'IN' < 'OU'). In this Last In of the employee with time is consider if Type is 'IN' last then count should be 1.


    just need how many employees are inside the office in each department. Department wise break not employee wise break up

    Thanks once again i appreciated your help

  6. #6
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Gazillionaire View Post
    I don't want employee wise i want department wise
    Maybe I did not emphasize it, but I posted the query for demonstrating its functionality. You really should understand it. Anyway, I stated how to achieve your required output (as it did not contain DEPTNAME originally). It leads to another simple join though.
    Quote Originally Posted by Gazillionaire View Post
    does 0.16 mean 16 minutes or 9 minutes 36 seconds (as it would be its decimal ratio to one hour)? this entry wouldn't be like it is a 24 hour time system 1 -24 no points here.
    Why did you include to the test case then?
    Quote Originally Posted by Gazillionaire View Post
    In this Last In of the employee with time is consider if Type is 'IN' last then count should be 1.
    Still does not answer when there are two "last" rows with different type.
    Code:
    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'),15.00);
    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);
    Anyway, I posted how to achieve the correct result so it is on you.

  7. #7
    Join Date
    Sep 2011
    Posts
    5
    Still does not answer when there are two "last" rows with different type.
    Code:

    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'),15.00);
    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);

    In the above scenario last swipe is for Out. He is outside the office now so no need to be count him.

  8. #8
    Join Date
    Mar 2007
    Posts
    623
    Maybe you *think* you achieved the desired result, but I doubt so.

    Maybe that scenario cannot happen in real operation, but it is allowed by non-existence of a database constraint. Just be aware that the previous scenario is the same as
    Code:
    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 ('05611','IN',to_timestamp('25-JAN-11 00.00.00.000000000',
    'DD-MON-RR HH24.MI.SS.FF'),15.00);
    There is no order of rows (e.g. by the insert time - just imagine two sessions running in parallel) in relational database unless there is some column which holds it; as TRXDATE and TRXTIME are same, both these rows are "last". Anyway, unless you correct the data model, you will have to choose the fixed TRXTYPE as "last" (IN or OUT).

  9. #9
    Join Date
    Sep 2011
    Posts
    5

    filter

    22518 12-Oct-10 IN 6.43
    22518 12-Oct-10 OU 6.43
    22518 12-Oct-10 IN 6.42 --> how to filter to get this entry because this last swipe in for this employee we need to count. There might be mulitple INs and OUTs but we need to check the last swipe of IN to consider him in the count.

  10. #10
    Join Date
    Mar 2007
    Posts
    623
    Quote Originally Posted by Gazillionaire View Post
    22518 12-Oct-10 IN 6.43
    22518 12-Oct-10 OU 6.43
    22518 12-Oct-10 IN 6.42 --> how to filter to get this entry because this last swipe in for this employee we need to count. There might be mulitple INs and OUTs but we need to check the last swipe of IN to consider him in the count.
    As I said, there is no "default order" of rows in a table: http://asktom.oracle.com/pls/asktom/...12391875000391
    Repeat it until you believe it.

    So, you need to define rules for determining the "last" row only from data itself. After identifying it, use the query I gave you with appropriate ORDER BY clause in the analytic function.
    It is impossible to get this information from data which are not containing it.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •