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 |
|
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 #601227 is a reply to message #601219] |
Mon, 18 November 2013 14:07 |
|
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 #601229 is a reply to message #601228] |
Mon, 18 November 2013 14:17 |
|
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 |
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 |
|
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 |
|
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 #601244 is a reply to message #601241] |
Mon, 18 November 2013 22:22 |
|
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 #601269 is a reply to message #601218] |
Tue, 19 November 2013 00:59 |
|
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 |
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 #601278 is a reply to message #601275] |
Tue, 19 November 2013 02:39 |
|
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 |
|
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
|
|
|
|
Goto Forum:
Current Time: Fri Mar 29 06:14:50 CDT 2024
|