Nested query 3rd level

[emmy]

I've gotten my query this far, however if 2 actions happened on a record on the same day - I get 2 results for that record.
Say a person 006 changed locations and got a raise on the same day, they will have EFFSEQ 0 and EFFSEQ 1 for that day - thus giving me 2 records for person 006 in my results. What I want to see is EFFSEQ 1 only (the max one). I think, in order to get that I need to pull MAX(EFFSEQ) from the results showing here, but I'm just not good enough to figure out a 3rd nested query. Can anybody help me with that?

SELECT
a.EMPLID, 
(CONVERT(CHAR(10),a.[EFFDT],110)) AS EFF_DT,
a.EMPL_STATUS,
a.HR_STATUS,
a.EFFSEQ,
a.EMPL_RCD,
a.DEPTID
FROM PS_JOB a 
INNER JOIN
(
SELECT 
	PS_JOB.EMPLID, 
	Max((CONVERT(CHAR(10),PS_JOB.[EFFDT],110))) AS Max_EFF_DATE
FROM 
	PS_JOB
GROUP BY PS_JOB.EMPLID
) b
ON (a.EMPLID=b.EMPLID) 
AND (a.EFFDT = b.Max_EFF_DATE) 
WHERE a.EMPL_RCD = 0
GROUP BY a.EMPLID, a.EFFDT, a.EMPL_STATUS, a.EFFSEQ, a.HR_STATUS, a.EMPL_RCD, a.DEPTID

 

[mrdenny]

If they had two records in PS_JOB wouldn't the EFFEFFDT of the second record be the one that is returned by the nested query already, or could the EFFSEQ 0 record have a higher EFFDT than the EFFSEQ1 record.

You can probably simply add a MAX(EFFSEQ) to the existing nested query couldn't you?

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[emmy]

Sometimes a person is terminated at one location, then hired at another. This transition will be recorded on the same day. So on Effective Date Aug 8 Jane could be terminated at location A (EFFSEQ0), then hired at location B (EFFSEQ1).

For whatever reason, if I change the first chunk to read:

a.EMPLID,
(CONVERT(CHAR(10),a.[EFFDT],110)) AS EFF_DT,
a.EMPL_STATUS,
a.HR_STATUS,
MAX(a.EFFSEQ,)
a.EMPL_RCD,
a.DEPTID
FROM PS_JOB a

...I get the same number of records, so those with 2 transactions on the same day still show twice.

Thanks for looking at this.

 

[mrdenny]

I think you will want to add the MAX(EFFSEQ) within the "b" query like this.

...
INNER JOIN
(
SELECT 
    PS_JOB.EMPLID, 
    MAX(EFFSEQ) as Max_EFFSEQ,
    Max((CONVERT(CHAR(10),PS_JOB.[EFFDT],110))) AS Max_EFF_DATE
FROM 
    PS_JOB
GROUP BY PS_JOB.EMPLID
) b
...

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[emmy]

What I found out is I have 3 criteria, so had to have a 3-deep nested query. EMPLID (employee id)- the '0' EMPL_RCD (employee record), the latest date, and if one date has more than one transaction, the latest transaction (EFFSEQ)
Here is the code that works -

SELECT 
a.EMPLID, 
a.EMPL_RCD, 
a.EFFDT, 
a.EFFSEQ, 
a.HR_STATUS, 
a.EMPL_STATUS, 
a.LOCATION, 
a.DEPTID, 
a.POSITION_NBR
FROM PS_JOB a
INNER JOIN
(
SELECT
x.EMPLID, 
x.EMPL_RCD, 
x.EFFDT, 
Max(x.EFFSEQ) AS MaxEFFSEQ
FROM PS_JOB x

INNER JOIN
(
SELECT 
PS_JOB.EMPLID, 
Min(PS_JOB.EMPL_RCD) AS MinEMPL_RCD, 
Max(PS_JOB.EFFDT) AS MaxEFFDT
FROM PS_JOB 
GROUP BY PS_JOB.EMPLID
) y
ON
x.EMPLID = y.EMPLID
AND x.EMPL_RCD = y.MinEMPL_RCD
AND x.EFFDT = y.MaxEFFDT
GROUP BY x.EMPLID, x.EMPL_RCD, x.EFFDT
) b
ON
a.EMPLID = b.EMPLID
AND a.EMPL_RCD = b.EMPL_RCD
AND a.EFFDT = b.EFFDT
AND a.EFFSEQ = b.MaxEFFSEQ