hdcowboyaz
asked on
SubQuery
SELECT e.EMP_ID, h.ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;
In the above query I need to select the only rows if d.ACTION = 'TERM' for the MAX(h.ACTIONDATE) when Group By (e.EMP_ID)
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;
In the above query I need to select the only rows if d.ACTION = 'TERM' for the MAX(h.ACTIONDATE) when Group By (e.EMP_ID)
ASKER
This is the data. The querry should not select EMP ID's: 19, 57 & 130 becasuse the ACTION for their MAX dates IS NOT 'TERM'
EMP ID DATE ACTION
13 9/5/1995 HIRE
13 1/30/2009 TERM
13 4/11/2011 REHIRE
13 5/20/2011 TERM
16 6/3/1996 HIRE
16 2/17/2007 TERM
19 5/15/1997 HIRE
20 5/27/1997 HIRE
20 11/16/2005 REHIRE
20 2/18/2009 TERM
20 10/24/2011 REHIRE
20 10/25/2011 TERM
22 5/7/1998 HIRE
22 11/20/2007 TERM
57 6/4/2001 HIRE
57 3/28/2008 TERM
57 6/11/2008 REHIRE
64 11/5/2001 HIRE
64 3/3/2008 REHIRE
64 7/22/2009 TERM
67 12/10/2001 HIRE
67 3/10/2009 REHIRE
67 4/28/2009 TERM
121 9/11/2002 HIRE
121 3/10/2005 TERM
122 9/11/2002 HIRE
122 7/23/2004 TERM
130 10/19/2002 HIRE
130 10/1/2008 TERM
130 10/1/2008 REHIRE
130 2/20/2012 REHIRE
This is the query... When I run it it does filter out EMP ID 19 but NOT 57 & 130
SELECT EMP_ID, ACTIONDATE, `ACTION`
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) AS ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
AND h.ACTION = 'TERM'
GROUP BY e.EMP_ID
ORDER BY d.EMPDATA_ID, h.ACTIONDATE
) q;
EMP_ID ACTIONDATE ACTION
13 5/20/2011 TERM
16 2/17/2007 TERM
20 10/25/2011 TERM
22 11/20/2007 TERM
57 3/28/2008 TERM
64 7/22/2009 TERM
67 4/28/2009 TERM
121 3/10/2005 TERM
122 7/23/2004 TERM
130 10/1/2008 TERM
EMP ID DATE ACTION
13 9/5/1995 HIRE
13 1/30/2009 TERM
13 4/11/2011 REHIRE
13 5/20/2011 TERM
16 6/3/1996 HIRE
16 2/17/2007 TERM
19 5/15/1997 HIRE
20 5/27/1997 HIRE
20 11/16/2005 REHIRE
20 2/18/2009 TERM
20 10/24/2011 REHIRE
20 10/25/2011 TERM
22 5/7/1998 HIRE
22 11/20/2007 TERM
57 6/4/2001 HIRE
57 3/28/2008 TERM
57 6/11/2008 REHIRE
64 11/5/2001 HIRE
64 3/3/2008 REHIRE
64 7/22/2009 TERM
67 12/10/2001 HIRE
67 3/10/2009 REHIRE
67 4/28/2009 TERM
121 9/11/2002 HIRE
121 3/10/2005 TERM
122 9/11/2002 HIRE
122 7/23/2004 TERM
130 10/19/2002 HIRE
130 10/1/2008 TERM
130 10/1/2008 REHIRE
130 2/20/2012 REHIRE
This is the query... When I run it it does filter out EMP ID 19 but NOT 57 & 130
SELECT EMP_ID, ACTIONDATE, `ACTION`
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) AS ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
AND h.ACTION = 'TERM'
GROUP BY e.EMP_ID
ORDER BY d.EMPDATA_ID, h.ACTIONDATE
) q;
EMP_ID ACTIONDATE ACTION
13 5/20/2011 TERM
16 2/17/2007 TERM
20 10/25/2011 TERM
22 11/20/2007 TERM
57 3/28/2008 TERM
64 7/22/2009 TERM
67 4/28/2009 TERM
121 3/10/2005 TERM
122 7/23/2004 TERM
130 10/1/2008 TERM
SELECT EMP_ID, ACTIONDATE, ACTION
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) as ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
GROUP BY e.EMP_ID
) q
WHERE ACTION = 'TERM'
ORDER BY EMP_ID, ACTIONDATE;
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) as ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
GROUP BY e.EMP_ID
) q
WHERE ACTION = 'TERM'
ORDER BY EMP_ID, ACTIONDATE;
ASKER
I get no results when I run that.
try this query.
SELECT e.EMP_ID,
h.ACTIONDATE,
h.ACTION
FROM emp e,
empdata d,
emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H'
AND e.emp_id IN (SELECT emp_id
FROM (SELECT t1.*,
IF(@emp_id = emp_id, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
IF(@emp_id <> emp_id, @emp_id := emp_id, -9999) AS dummy
FROM (SELECT e.EMP_ID,
h.ACTIONDATE,
h.ACTION
FROM emp e,
empdata d,
emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H') t1,
(SELECT @rownum := 1,
@emp_id := -9999) r
ORDER BY emp_id,
actiondate DESC) t1
WHERE rownum = 1
AND action = 'TERM')
ORDER BY e.emp_id,
d.ACTIONDATE;
Tested with your sample here: http://sqlfiddle.com/#!8/93bda/4
ASKER
Query : SELECT e.EMP_ID, h.ACTIONDATE, h.ACTION FROM emp e, empdata d, emphire h WHERE e.EMP_ID = d...
Error Code : 1054
Unknown column 'd.ACTIONDATE' in 'order clause'
Query : 13,16,20,22 19,57 & 130
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13,16,20,22
19,57 & 130' at line 1
Error Code : 1054
Unknown column 'd.ACTIONDATE' in 'order clause'
Query : 13,16,20,22 19,57 & 130
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '13,16,20,22
19,57 & 130' at line 1
ASKER
When I said this is the data, I meant it's the result of the query from the three tables (emp, empdata & emphire)
Query
SELECT e.EMP_ID, h.ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;
Results
EMP ID DATE ACTION
13 9/5/1995 HIRE
13 1/30/2009 TERM
13 4/11/2011 REHIRE
13 5/20/2011 TERM
16 6/3/1996 HIRE
16 2/17/2007 TERM
19 5/15/1997 HIRE
20 5/27/1997 HIRE
20 11/16/2005 REHIRE
20 2/18/2009 TERM
20 10/24/2011 REHIRE
20 10/25/2011 TERM
22 5/7/1998 HIRE
22 11/20/2007 TERM
57 6/4/2001 HIRE
57 3/28/2008 TERM
57 6/11/2008 REHIRE
64 11/5/2001 HIRE
64 3/3/2008 REHIRE
64 7/22/2009 TERM
67 12/10/2001 HIRE
67 3/10/2009 REHIRE
67 4/28/2009 TERM
121 9/11/2002 HIRE
121 3/10/2005 TERM
122 9/11/2002 HIRE
122 7/23/2004 TERM
130 10/19/2002 HIRE
130 10/1/2008 TERM
130 10/1/2008 REHIRE
130 2/20/2012 REHIRE
What is needed, I beleive is a subquery to only show results where d.ACTION = 'TERM' for the MAX(h.ACTIONDATE) when Group By (e.EMP_ID)
This should be the final results
EMP ID DATE ACTION
13 5/20/2011 TERM
16 2/17/2007 TERM
20 10/25/2011 TERM
22 11/20/2007 TERM
64 7/22/2009 TERM
67 4/28/2009 TERM
121 3/10/2005 TERM
122 7/23/2004 TERM
19, 57 & 130 should be filtered out.
Query
SELECT e.EMP_ID, h.ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;
Results
EMP ID DATE ACTION
13 9/5/1995 HIRE
13 1/30/2009 TERM
13 4/11/2011 REHIRE
13 5/20/2011 TERM
16 6/3/1996 HIRE
16 2/17/2007 TERM
19 5/15/1997 HIRE
20 5/27/1997 HIRE
20 11/16/2005 REHIRE
20 2/18/2009 TERM
20 10/24/2011 REHIRE
20 10/25/2011 TERM
22 5/7/1998 HIRE
22 11/20/2007 TERM
57 6/4/2001 HIRE
57 3/28/2008 TERM
57 6/11/2008 REHIRE
64 11/5/2001 HIRE
64 3/3/2008 REHIRE
64 7/22/2009 TERM
67 12/10/2001 HIRE
67 3/10/2009 REHIRE
67 4/28/2009 TERM
121 9/11/2002 HIRE
121 3/10/2005 TERM
122 9/11/2002 HIRE
122 7/23/2004 TERM
130 10/19/2002 HIRE
130 10/1/2008 TERM
130 10/1/2008 REHIRE
130 2/20/2012 REHIRE
What is needed, I beleive is a subquery to only show results where d.ACTION = 'TERM' for the MAX(h.ACTIONDATE) when Group By (e.EMP_ID)
This should be the final results
EMP ID DATE ACTION
13 5/20/2011 TERM
16 2/17/2007 TERM
20 10/25/2011 TERM
22 11/20/2007 TERM
64 7/22/2009 TERM
67 4/28/2009 TERM
121 3/10/2005 TERM
122 7/23/2004 TERM
19, 57 & 130 should be filtered out.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
That worked, however I left off several columns initially so as to make the query more easily explained. I've added these extra colums now and they work.
SELECT t1.EMP_ID,t1.ACTIONDATE,t1 .ACTION,t1 .ERATE,t1. ECCLASS,t1 .EMISC1,t1 .PTELEPH,t 1.EPHONE2, t1.EEMAIL
FROM (SELECT t2.*,
IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) AS dummy
FROM (SELECT e.EMP_ID,
h.ACTIONDATE,
h.ACTION,
d.ERATE,
d.ECCLASS,
d.EMISC1,
d.PTELEPH,
d.EPHONE2,
d.EEMAIL
FROM emp e,
empdata d,
emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H') t2,
(SELECT @rownum := 1,
@emp_id := -9999) r
ORDER BY EMP_ID,
ACTIONDATE DESC) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY EMP_ID,
ACTIONDATE;
However, I want to add two more colums but I can't get them to work.
ROUND(DATEDIFF('2013-10-04 ', e.EBORN)/365.25,0)
CONCAT(d.ECOMPANY, d.EDEPT)
ALso, how do you add an alias (e.g., d.EMISC1 'POSITION'
SELECT t1.EMP_ID,t1.ACTIONDATE,t1
FROM (SELECT t2.*,
IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum,
IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) AS dummy
FROM (SELECT e.EMP_ID,
h.ACTIONDATE,
h.ACTION,
d.ERATE,
d.ECCLASS,
d.EMISC1,
d.PTELEPH,
d.EPHONE2,
d.EEMAIL
FROM emp e,
empdata d,
emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H') t2,
(SELECT @rownum := 1,
@emp_id := -9999) r
ORDER BY EMP_ID,
ACTIONDATE DESC) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY EMP_ID,
ACTIONDATE;
However, I want to add two more colums but I can't get them to work.
ROUND(DATEDIFF('2013-10-04
CONCAT(d.ECOMPANY, d.EDEPT)
ALso, how do you add an alias (e.g., d.EMISC1 'POSITION'
>> I want to add two more columns ...
nb: CURDATE() might be used instead of '2013-10-04',
but wasn't certain if this is what you intended
Lines 9 & 27 implement the alias wanted for d.EMISC1 as POSITION
', e.EBORN)/365.25,0)
this is not an accurate calculation of age and is prone to error
Do you need a more accurate method?
ROUND(DATEDIFF('2013-10-04 ', e.EBORN)/365.25,0) ***see at end
CONCAT(d.ECOMPANY, d.EDEPT)
Lines 7 & 8 and lines 24 & 25 below will introduce those 2 columnsCONCAT(d.ECOMPANY, d.EDEPT)
nb: CURDATE() might be used instead of '2013-10-04',
but wasn't certain if this is what you intended
Lines 9 & 27 implement the alias wanted for d.EMISC1 as POSITION
SELECT
t1.EMP_ID
, t1.ACTIONDATE
, t1.ACTION
, t1.ERATE
, t1.ECCLASS
, t1.AppproxAge
, t1.CompanyDept
, t1.POSITION
, t1.PTELEPH
, t1.EPHONE2
, t1.EEMAIL
FROM (
SELECT
t2.*
, IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum--,
, IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) AS dummy
FROM (
SELECT
e.EMP_ID
, h.ACTIONDATE
, h.ACTION
, d.ERATE
, d.ECCLASS
, ROUND(DATEDIFF('2013-10-04', e.EBORN)/365.25,0) as AppproxAge
, CONCAT(d.ECOMPANY, d.EDEPT) as CompanyDept
, d.EMISC1 as POSITION
, d.PTELEPH
, d.EPHONE2
, d.EEMAIL
FROM emp e
, empdata d
, emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H'
) t2
, (SELECT @rownum := 1,@emp_id := -9999) r
ORDER BY
EMP_ID
, ACTIONDATE DESC
) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY
EMP_ID
, ACTIONDATE
;
*** ROUND(DATEDIFF('2013-10-04this is not an accurate calculation of age and is prone to error
Do you need a more accurate method?
ASKER
Query : SELECT t1.EMP_ID , t1.ACTIONDATE , t1.ACTION , t1.ERATE , t1.ECCLASS , t1.AppproxAge , t1.C...
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--,
, IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) ' at line 16
No the age appoximation is okay.
Did you want the " - -" after RowNum. It still fails whether I leave it in or take it out.
IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum--,
Error Code : 1064
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '--,
, IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) ' at line 16
No the age appoximation is okay.
Did you want the " - -" after RowNum. It still fails whether I leave it in or take it out.
IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum--,
ASKER
WORKS. There was an extra comma
SELECT
t1.EMP_ID
, t1.ACTIONDATE
, t1.ACTION
, t1.ERATE
, t1.ECCLASS
, t1.AppproxAge
, t1.CompanyDept
, t1.POSITION
, t1.PTELEPH
, t1.EPHONE2
, t1.EEMAIL
FROM (
SELECT
t2.*
, IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum
, IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) AS dummy
FROM (
SELECT
e.EMP_ID
, h.ACTIONDATE
, h.ACTION
, d.ERATE
, d.ECCLASS
, ROUND(DATEDIFF('2013-10-04 ', e.EBORN)/365.25,0) AS AppproxAge
, d.EMISC1 AS POSITION
, d.PTELEPH
, d.EPHONE2
, d.EEMAIL
, CONCAT(d.ECOMPANY, d.EDEPT) AS CompanyDept
FROM emp e
, empdata d
, emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H'
) t2
, (SELECT @rownum := 1,@emp_id := -9999) r
ORDER BY
EMP_ID
, ACTIONDATE DESC
) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY
EMP_ID
, ACTIONDATE
;
SELECT
t1.EMP_ID
, t1.ACTIONDATE
, t1.ACTION
, t1.ERATE
, t1.ECCLASS
, t1.AppproxAge
, t1.CompanyDept
, t1.POSITION
, t1.PTELEPH
, t1.EPHONE2
, t1.EEMAIL
FROM (
SELECT
t2.*
, IF(@emp_id = EMP_ID, @rownum := @rownum + 1, @rownum := 1) AS RowNum
, IF(@emp_id <> emp_id, @emp_id := EMP_ID, -9999) AS dummy
FROM (
SELECT
e.EMP_ID
, h.ACTIONDATE
, h.ACTION
, d.ERATE
, d.ECCLASS
, ROUND(DATEDIFF('2013-10-04
, d.EMISC1 AS POSITION
, d.PTELEPH
, d.EPHONE2
, d.EEMAIL
, CONCAT(d.ECOMPANY, d.EDEPT) AS CompanyDept
FROM emp e
, empdata d
, emphire h
WHERE e.EMP_ID = d.EMP_ID
AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N'
AND d.DELETED = 'N'
AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%'
AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A'
AND d.EDEPT = 'H'
) t2
, (SELECT @rownum := 1,@emp_id := -9999) r
ORDER BY
EMP_ID
, ACTIONDATE DESC
) t1
WHERE rownum = 1
AND ACTION = 'TERM'
ORDER BY
EMP_ID
, ACTIONDATE
;
ASKER
What does the -9999 represent? If I have more rows of data in the table does that need increased?
ASKER
SELECT COUNT(EMP_ID) FROM emp;
9994
SELECT COUNT(EMPDATA_ID) FROM empdata;
10,967
SELECT COUNT(EMPHIRE_ID) FROM emphire;
27,101
9994
SELECT COUNT(EMPDATA_ID) FROM empdata;
10,967
SELECT COUNT(EMPHIRE_ID) FROM emphire;
27,101
the -9999 is not related to the number of records in any table.
the method introduced by Sharath (who did most of the work here) emulates "row_number()" in MySQL and the purpose of it is to force a "partitioning" of the roe numbers per emp_id.
Unless you have emp_id values that are negative, which is very unlikely, then you should not need to worry about the -9999
Note, all I did was introduce a few extra fields, as you expanded the question.
>>"That worked, "
in reference to Sharath's work - so at that point the question was answered.
Everthing added after "That worked" should have been a new question I believe, and Sharath would feel justified in asking for points to be given to him.
>>"however I left off several columns initially"
This is not a comment directed solely at you - but a general one.
It has been my experience that when questions are "simplified" they tend to get long and slow to answer - often frustrating all parties.
It is preferable to state the full question up front and without simplification.
the method introduced by Sharath (who did most of the work here) emulates "row_number()" in MySQL and the purpose of it is to force a "partitioning" of the roe numbers per emp_id.
Unless you have emp_id values that are negative, which is very unlikely, then you should not need to worry about the -9999
Note, all I did was introduce a few extra fields, as you expanded the question.
>>"That worked, "
in reference to Sharath's work - so at that point the question was answered.
Everthing added after "That worked" should have been a new question I believe, and Sharath would feel justified in asking for points to be given to him.
>>"however I left off several columns initially"
This is not a comment directed solely at you - but a general one.
It has been my experience that when questions are "simplified" they tend to get long and slow to answer - often frustrating all parties.
It is preferable to state the full question up front and without simplification.
btw: sorry about the missing or extra comma - not sure where it was or how I missed it - but when it's not possible to run a query syntax errors can go undetected.
ASKER
I'm sorry. I wasn't paying attention that someone else answered. I don't know how to go back and give Sharath credit?
Thanks Paul for all your comments and guidance to hdcowboyaz for closing the question in right way.
Least I could do Sharath - it wasn't my intention to leverage your work to my advantage - just wanted to see that the question(s) solved for hdcowboyaz
FROM
(
SELECT e.EMP_ID, MAX(h.ACTIONDATE) as ACTIONDATE, h.ACTION
FROM emp e, empdata d, emphire h
WHERE e.EMP_ID = d.EMP_ID AND d.EMPDATA_ID = h.EMPDATA_ID
AND e.DELETED = 'N' AND d.DELETED = 'N' AND h.DELETED = 'N'
AND d.EPAY NOT LIKE 'SAL%' AND d.EPAY NOT LIKE 'REGADM'
AND d.ECOMPANY = 'A' AND d.EDEPT = 'H'
AND d.ACTION = 'TERM'
GROUP BY e.EMP_ID
) q
ORDER BY d.EMPDATA_ID, h.ACTIONDATE;