How can I do a subquery to select the newest employee rate from the premprt table using a data dictionary and ssrs report

Kinetic ERP
1

I have an ssrs report that uses a data dictionary and report style. I have setup up the premprt table linked to the prempmas table in the data dictionary. I want to do a subquery in my ssrs report that will get me the latest, newest pay rate from the premprt table. I did this in a sql query shown below. How do I do it in the ssrs report query.

Please Help!!!

Thanks,
Richard

with subquerynewestrate as
(select
company,
emplink,
payrate,
ratedate,
(Row_Number() over (PARTITION BY PREmpRt.EmpLink ORDER BY PREmpRt.RateDate DESC)) as newestrownum
from erp.premprt as premprt)
select subquerynewestrate.company,prempmas.empid,subquerynewestrate.newestrownum,
subquerynewestrate.payrate,subquerynewestrate.ratedate
from subquerynewestrate
inner join erp.prempmas on prempmas.company = subquerynewestrate.company
and prempmas.emplink = subquerynewestrate.emplink
where newestrownum = 1
order by empid

1 Like