Home » SQL & PL/SQL » SQL & PL/SQL » Please Suggest in this query what should i do.. (Window)
Please Suggest in this query what should i do.. [message #601218] Mon, 18 November 2013 13:24 Go to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
Attached is a two table reference, I want to take out all the employee working in one or more process_level(Location of company)according to there latest check date.

Point
1.)I want to take out employee according to latest check_date(employee pay date).
2.) Also in some case one employee is working in two process_level and have two latest check_date how we can take out latest check date of both the process_level having same employee.(As in attached file emphistory table contain employee 212142 in two process_level 12121 and 23242 and i have to retrieve both check date of different process_level)

I have generated the query but not getting desired output


SELECT A.company, 
       A.process_level, 
       A.employee, 
       Max(B.check_date) Last_check_date, 
       A.emp_status, 
       A.adj_hire_date 
FROM   employee A 
       full outer join emphistory B 
                    ON A.process_level = B.process_level 
WHERE  b. check_date BETWEEN To_date ('Oct-01-2013', 'mon/dd/yyyy') AND 
                                    To_date ('oct-31-2013', 'mon/dd/yyyy') 
GROUP  BY A.company, 
          A.process_level, 
          A.employee, 
          B.check_date, 
          A.emp_status, 
          A.adj_hire_date 
ORDER  BY B.check_date DESC 


*BlackSwan add formatting & {code} tags. Please do so yourself in the future
  • Attachment: tables.txt
    (Size: 0.93KB, Downloaded 944 times)

[Updated on: Mon, 18 November 2013 13:34] by Moderator

Report message to a moderator

Re: Please Suggest in this query what should i do.. [message #601219 is a reply to message #601218] Mon, 18 November 2013 13:35 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
It would be helpful if you provided DDL (CREATE TABLE ...) for tables involved.
It would be helpful if you provided DML (INSERT INTO ...) for test data.
It would be helpful if you followed Posting Guidelines - http://www.orafaq.com/forum/t/88153/0/311
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.
Re: Please Suggest in this query what should i do.. [message #601227 is a reply to message #601219] Mon, 18 November 2013 14:07 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
Employee Table

create table  employee
(
company  NUMBER(7), 
process_level  NUMBER(7), 
emp_status varchar(4),
adj_hire_date date, 
employee NUMBER(7)
)

Insert into employee VALUES (1, 21211, a, 01/10/2013, 212142
)
Insert into employee VALUES (1, 61216, a, 29/11/2012, 212164
)
Insert into employee VALUES (1, 32313, a, 21/12/2102, 121121
)
Insert into employee VALUES (1, 23242, a, 21/09/2013, 214213
)
Insert into employee VALUES (1, 12121, a, 02/04/2013, 231212
)
Insert into employee VALUES (1, 31232, a, 04/02/2013, 312122
)
Insert into employee VALUES (1, 23242, a, 06/06/2012, 112234
)
Insert into employee VALUES (1, 42232, a, 04/02/2013, 121232
)
Insert into employee VALUES (1, 23231, a, 21/09/2013, 212133)


Table Emphistroty

create table  emphistory
(
company  NUMBER(7), 
process_level  NUMBER(7), 
check_date date,
employee NUMBER(7)
)

Insert into employee VALUES (1, 12121, 15/10/2013, 212142)
Insert into employee VALUES (1, 12121, 01/10/2013, 212142)
Insert into employee VALUES (1, 61216, 04/10/2013, 212164)
Insert into employee VALUES (1, 61216, 01/10/2013, 212164)
Insert into employee VALUES (1, 32313, 03/10/2013, 121121)
Insert into employee VALUES (1, 32313, 10/10/2013, 121121)
Insert into employee VALUES (1, 12121, 04/10/2013, 231212)
Insert into employee VALUES (1, 12121, 01/10/2013, 231212)

Insert into employee VALUES (1, 23242, 03/10/2013, 212142)
Insert into employee VALUES (1, 23242, 11/10/2013, 212142)

[Updated on: Mon, 18 November 2013 14:09]

Report message to a moderator

Re: Please Suggest in this query what should i do.. [message #601228 is a reply to message #601227] Mon, 18 November 2013 14:13 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
Now Please help as i have provided you all the details
Re: Please Suggest in this query what should i do.. [message #601229 is a reply to message #601228] Mon, 18 November 2013 14:17 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
[oracle@localhost samples]$ sqlplus user1/user1

SQL*Plus: Release 11.2.0.2.0 Production on Mon Nov 18 12:15:36 2013

Copyright (c) 1982, 2010, Oracle.  All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> set term on echo on
SQL> create table  employee
(
company  NUMBER(7), 
process_level  NUMBER(7), 
emp_status varchar(4),
adj_hire_date date, 
employee NUMBER(7)
)  2    3    4    5    6    7    8  ;

Table created.

SQL> Insert into employee VALUES (1, 21211, a, 01/10/2013, 212142
)  2  ;
Insert into employee VALUES (1, 21211, a, 01/10/2013, 212142
                                       *
ERROR at line 1:
ORA-00984: column not allowed here


SQL> 




posted SQL should actually be valid & NOT throw any error.
Re: Please Suggest in this query what should i do.. [message #601230 is a reply to message #601228] Mon, 18 November 2013 14:22 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
I can't test any code because your INSERT statements don't work. You need to think about data types.

However, for your question, I think your query is attempting to apply an aggregation, MAX, to a column that you are grouping by, CHECK_DATE. Is that correct?
Re: Please Suggest in this query what should i do.. [message #601238 is a reply to message #601230] Mon, 18 November 2013 21:44 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
Table Emphistory

create table  emphistory
(
company  NUMBER(7), 
process_level  NUMBER(7), 
check_date date,
employee NUMBER(7)
)

Insert into emphistory VALUES (1, 12121, 15/10/2013, 212142)
Insert into emphistory VALUES (1, 12121, 01/10/2013, 212142)
Insert into emphistory VALUES (1, 61216, 04/10/2013, 212164)
Insert into emphistory VALUES (1, 61216, 01/10/2013, 212164)
Insert into emphistory VALUES (1, 32313, 03/10/2013, 121121)
Insert into emphistory VALUES (1, 32313, 10/10/2013, 121121)
Insert into emphistory VALUES (1, 12121, 04/10/2013, 231212)
Insert into emphistory VALUES (1, 12121, 01/10/2013, 231212)

Insert into emphistory VALUES (1, 23242, 03/10/2013, 212142)
Insert into emphistory VALUES (1, 23242, 11/10/2013, 212142)
Re: Please Suggest in this query what should i do.. [message #601240 is a reply to message #601238] Mon, 18 November 2013 22:03 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> create table  emphistory
(
company  NUMBER(7), 
process_level  NUMBER(7), 
check_date date,
employee NUMBER(7)
)  2    3    4    5    6    7  ;

Table created.

SQL> Insert into emphistory VALUES (1, 12121, 15/10/2013, 212142);
Insert into emphistory VALUES (1, 12121, 15/10/2013, 212142)
                                              *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected DATE got NUMBER

SQL> select 15/10/2013 from dual;

15/10/2013
----------
.000745156




you MUST use TO_DATE() function to convert string to DATE datatype

[Updated on: Mon, 18 November 2013 22:04]

Report message to a moderator

Re: Please Suggest in this query what should i do.. [message #601241 is a reply to message #601238] Mon, 18 November 2013 22:03 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
I want to see latest Check_date of employee that's why i am using Max (Check_Date)
Re: Please Suggest in this query what should i do.. [message #601244 is a reply to message #601241] Mon, 18 November 2013 22:22 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
Emphistory table
Insert into emphistory VALUES (1, 12121, to_date ('15/10/2013', 'dd/mm/yyyy'), 212142)
Insert into emphistory VALUES (1, 12121,to_date ( '01/10/2013','dd/mm/yyyy'), 212142)
Insert into emphistory VALUES (1, 61216, to_date('04/10/2013','dd/mm/yyyy'),  212164)
Insert into emphistory VALUES (1, 61216, to_date('01/10/2013','dd/mm/yyyy') 212164)
Insert into emphistory VALUES (1, 32313, to_date('03/10/2013', 'dd/mm/yyyy'), 121121)
Insert into emphistory VALUES (1, 32313, to_date ('10/10/2013','dd/mm/yyyy'), 121121)
Insert into emphistory VALUES (1, 12121, to_date ('04/10/2013', 'dd/mm/yyyy'), 231212)
Insert into emphistory VALUES (1, 12121, to_date('01/10/2013','dd/mm/yyyy')) 231212)

Insert into emphistory VALUES (1, 23242, to_date('03/10/2013','dd/mm/yyyy'), 212142)
Insert into emphistory VALUES (1, 23242, to_date('11/10/2013','dd/mm/yyyy'), 212142)

Re: Please Suggest in this query what should i do.. [message #601245 is a reply to message #601244] Mon, 18 November 2013 22:26 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
BlackSwan how to use Max in where clause.

[Updated on: Mon, 18 November 2013 22:27]

Report message to a moderator

Re: Please Suggest in this query what should i do.. [message #601246 is a reply to message #601245] Mon, 18 November 2013 22:33 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SQL> select * from emp where sal = (select max(sal) from emp);

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7839 KING       PRESIDENT            17-NOV-81       5000
        10


Re: Please Suggest in this query what should i do.. [message #601269 is a reply to message #601218] Tue, 19 November 2013 00:59 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
I have used Max(check_date) but it is retriving all the row only it move the max column above.
SELECT A.company, 
       A.process_level, 
       A.employee, 
       B.check_date, 
       A.emp_status, 
       A.adj_hire_date 
FROM   employee A 
       full outer join emphistory B 
                    ON A.process_level = B.process_level 
WHERE  b. check_date BETWEEN To_date ('Oct-01-2013', 'mon/dd/yyyy') AND 
                                    To_date ('oct-31-2013', 'mon/dd/yyyy')
                                    and B.check_date = (select max(B.check_date) from emphistory)
 
GROUP  BY A.company, 
          A.process_level, 
          A.employee, 
          B.check_date, 
          A.emp_status, 
          A.adj_hire_date 
ORDER  BY B.check_date DESC
Re: Please Suggest in this query what should i do.. [message #601270 is a reply to message #601269] Tue, 19 November 2013 01:18 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Your GROUP BY clause groups by all six columns that you are projecting. That means that the only rows that will be aggregated are those where all six columns have identical values: in effect, a DISTINCT. What you (probably) want is to group by a subset of the projected columns, and to apply an aggregation to the remaining columns.
Re: Please Suggest in this query what should i do.. [message #601274 is a reply to message #601270] Tue, 19 November 2013 02:30 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
ok thanks, But when I am applying max keyword it is only picking the first value of employee-212142 with process_level - 12121 , but same Employee is working in two process_level(company working)=


process_level Employee checkdate
12121 212142 15/10/2013 -->picking value this by max
23242 212142 11/10/2013--> but not picking this value by max

how we can take out both the value according to process_level
Re: Please Suggest in this query what should i do.. [message #601275 is a reply to message #601274] Tue, 19 November 2013 02:32 Go to previous messageGo to next message
John Watson
Messages: 8922
Registered: January 2010
Location: Global Village
Senior Member
Invisible SQL. Hard to debug.
Re: Please Suggest in this query what should i do.. [message #601278 is a reply to message #601275] Tue, 19 November 2013 02:39 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
I have tried this query


SELECT A.company, 
       A.process_level, 
       A.employee, 
       max(B.check_date)over (partition by  a.process_level), 
       A.emp_status, 
       A.adj_hire_date 
FROM   employee A, emphistory B 
                   WHERE   
                  a.company = b.company
                  and( A.employee = B.employee
                  or b.employee =a.employee)
  and b. check_date BETWEEN To_date ('Oct-01-2013', 'mon/dd/yyyy') AND 
                                    To_date ('oct-31-2013', 'mon/dd/yyyy')
and A.company = 1
GROUP  BY A.company, 
          A.process_level, 
          A.employee, 
          B.check_date, 
          A.emp_status, 
          A.adj_hire_date 
ORDER  BY B.check_date DESC 




But when I am applying max keyword it is only picking the first value of employee-212142 with process_level - 12121 , but same Employee is working in two process_level(company working). in 12121 and 23242


process_level ,Employee , checkdate
12121 , 212142 , 15/10/2013 -->picking value this by max
23242 , 212142 , 11/10/2013--> but not picking this value by max

how we can take out both the value according to process_level

[Updated on: Tue, 19 November 2013 02:47]

Report message to a moderator

Re: Please Suggest in this query what should i do.. [message #601400 is a reply to message #601278] Wed, 20 November 2013 09:16 Go to previous messageGo to next message
sahnra
Messages: 39
Registered: November 2013
Location: India
Member
How we can check in this case which coulmn is to be grouped because query is working fine but the only problem is that query is retriving one colunm twice.

Please Suggest...
Select B.COMPANY, B.Process_level, C.Head_company,
 D.Head_proc_Lev,  B.EMPLOYEE,
 max(b.check_date)over (partition by b.employee)as Last_check_date,  A.Term_date, 
 A.Pay_frequency, A.EMP_STATUS, A.date_hired, A.adj_hire_date, A.Fica_NBR, 
C.GBU,C.Service_Mgr STM, C.hrp
FROM EMPLOYEE A, PAYMASTR B, 
COMPROF C, TAXGROUP D

[Updated on: Wed, 20 November 2013 09:21]

Report message to a moderator

Re: Please Suggest in this query what should i do.. [message #601417 is a reply to message #601400] Wed, 20 November 2013 12:50 Go to previous message
joy_division
Messages: 4963
Registered: February 2005
Location: East Coast USA
Senior Member
sahnra wrote on Wed, 20 November 2013 10:16
query is working fine but the only problem is that query is retriving one colunm twice.
[/code]


Invisible output. Hard to debug. [1]



------------------
1. John Watson, OraFAQ 2013
Previous Topic: Sql Query
Next Topic: How to minus 1 day
Goto Forum:
  


Current Time: Fri Mar 29 06:14:50 CDT 2024