get Employee Detail using SQL query in Dynamics AX 2012

the requirement came from payroll section to extract active employee detail including below column,

[EMPLOYEEID],[NAME],[TITLE],[GRADE],[DEPARTMENT],[DIVISION],[START],[EXPIRE]

I am putting the code here may be it will help you, with the same requirements.

SELECT W.PERSONNELNUMBER AS EMPLOYEEID,I.EMPLNAME AS NAME,pd.description as TITLE,

(select top 1 c.COMPENSATIONLEVELID from HCMJOBDETAIL as j join HCMCOMPENSATIONLEVEL as c

on c.RECID = j.COMPENSATIONLEVEL where j.JOB = pd.Job) as GRADE,

DEPARTMENT.NAME AS DEPARTMENT,DIVISION.NAME AS DEVISION,

CONVERT(NVARCHAR(12),I.STARTDATE,110) AS START,

CONVERT(NVARCHAR(12),I.ENDDATE ,110) AS EXPIRE

FROM PREMPLOYMENTINFORMATION AS I

JOIN HCMWORKER AS W ON W.PERSONNELNUMBER = I.HRMVIRTUALNETWORKREFERENCE

JOIN

(

select wa.POSITION,convert(nvarchar(12),wa.VALIDFROM,110) as VALIDFROM,convert(nvarchar(12),wa.VALIDTO,110) as VALIDTO

,wa.WORKER from HCMPOSITIONWORKERASSIGNMENT as wa

where DATEDIFF(m,wa.ValidFrom,wa.ValidTo)>1

group by wa.POSITION,convert(nvarchar(12),wa.VALIDFROM,110) ,convert(nvarchar(12),wa.VALIDTO,110),wa.WORKER

)

AS WA ON WA.WORKER = W.RECID and WA.VALIDFROM < GETDATE() and WA.VALIDTO > GETDATE()

join

(SELECT POSITION,convert(nvarchar(12),VALIDFROM,110) as VALIDFROM,

convert(nvarchar(12),VALIDTO,110) as VALIDTO,DEPARTMENT,

JOB,[DESCRIPTION],TITLE FROM (SELECT * FROM HCMPOSITIONDETAIL as pd

where DATEDIFF(m,pd.ValidFrom,pd.ValidTo)>1

and pd.DEPARTMENT <>0 and pd.POSITION <>0 and pd.JOB <> 0) as pd

group by POSITION,convert(nvarchar(12),VALIDFROM,110),convert(nvarchar(12),VALIDTO,110),DEPARTMENT,

JOB,[DESCRIPTION],TITLE

)

as pd on pd.POSITION = WA.POSITION and pd.VALIDFROM<GETDATE() and pd.VALIDTO > GETDATE()

LEFT OUTER JOIN dbo.DIRPARTYTABLE AS department ON department.RECID = pd.DEPARTMENT AND department.INSTANCERELATIONTYPE IN (2377)

LEFT OUTER JOIN dbo.DIRPARTYTABLE AS division ON division.NAMEALIAS = department.HRMDVRID AND division.INSTANCERELATIONTYPE IN (2377)

WHERE ((I.STARTDATE <= GETDATE() AND ENDDATE > GETDATE()))