select a.Personnel_Ref, a.Surname, a.leaving_date, a.Employee_Status, a.Start_Date
from (
  select *, row_number() over (partition by Personnel_Ref order by Start_date DESC) rn
  from MyTable
  ) a
where a.rn = 1

select *
from
(
select 
row_number() over (partition by w.Personnel_Ref order by w.Start_date DESC) rn
,a.Employer_Ref
, Client_Or_Pers_No
, First_Forename+' '+Surname as Worker_Name
,Case 
	When Employee_Status='!Archive' Then 'History' 
	Else Employee_Status End as Worker_Status
,SUBSTRING(Leaving_date,7,2)+'/'+SUBSTRING(Leaving_Date,5,2)+'/'+SUBSTRING(Leaving_Date,1,4) as Leaving_Date
,CONVERT(Varchar(4),a.Tax_Year)+CONVERT(Varchar(2),a.Tax_Period) as Year_Pd
,Case when Item_Description='WTR Accrual Debit' Then Amount Else 0 End as Accrual_Value
,Case when Item_Description='WTD Holiday Pay' Then Amount Else 0 End as Holiday_Pay
, Client_Name
from 
	Accounts_Entries a 
Inner Join 
	vw_All_Workers w 
	On	a.Employer_Ref = w.Company_Employer 
	and	a.Client_Or_Pers_No = w.Personnel_Ref
Inner Join 
	Valid_Timesheets t 
	On	a.Employer_Ref=t.Employer_Ref 
	and a.Client_Or_Pers_No=t.Personnel_Ref
	and a.Tax_Year=t.Tax_Year --and a.Tax_Period=t.Period_proc_by_Payroll
Inner Join 
	Client_Ref c 
	On	t.Employer_Ref=c.Employer_Ref and t.Client_Ref=c.Client_Ref
where 
	a.Tax_Year=2012 
and Item_Description in ('WTR Accrual Debit','WTD Holiday Pay')
and a.Employer_Ref = 'OMWEEK'
) aa
where 
aa.rn = 1

quote:

---

Originally posted by Jannette

Thanks for this, but this is not giving the correct number of records. I want to ensure that only 1 record is returned from vw_All_Workers, then all the associated records from Accounts_Entries is returned (more than 1 record from account entries)

Hope this makes sense and someone can help !

---

quote:

---

Originally posted by Jannette

Hi,

I hope this makes a little more sense, here is some sample data.

Table = vw_All_Workers
Personnel Ref Leaving Date Start Date
BBYER8 20120401 20120326 <==== Only want this record from vw_All_Workers
BBYER8 20120321 20111021


Table = Accounts_Entries
Client_Or_Pers_No Item_Description amount
BBYER8 PAYE Timesheet Pay 390
BBYER8 Employers NIC Debit 33.95
BBYER8 Net Pay -313.92
BBYER8 Employers NIC Credit -33.95
BBYER8 National Insurance - Employee -29.28
BBYER8 Income Tax - PAYE -46.8
BBYER8 Employers NIC Debit 64.94
BBYER8 Net Pay -97.94
BBYER8 Advance Recovery -400
BBYER8 WTD Holiday Pay 614.57
BBYER8 Employers NIC Credit -64.94
BBYER8 National Insurance - Employee -56.23
BBYER8 Income Tax - PAYE -60.4

---

quote:

---

Originally posted by Jannette

I have rewritten the query slighty as follows:, but the results are not quite as I expect.

Select
b.Employer_Ref, a.Personnel_Ref, Worker_Name, a.Worker_Status, a.Leaving_Date, Year_Pd, Accrual_Value, Holiday_Pay
From

(Select
ROW_NUMBER() over (partition by Personnel_Ref order by Start_Date DESC) as rn,
Personnel_Ref, Leaving_Date, First_Forename+' '+Surname as Worker_Name,
Case When Employee_Status='!Archive' Then 'History' Else Employee_Status End as Worker_Status,
Company_Employer
From vw_All_Workers
Where Company_Employer='OMWEEK') a

Inner Join

(Select
Client_or_Pers_No, CONVERT(Varchar(4),Tax_Year)+CONVERT(Varchar(2),Tax_Period) as Year_Pd,
Case when Item_Description='WTR Accrual Debit' Then Amount Else 0 End as Accrual_Value,
Case when Item_Description='WTD Holiday Pay' Then Amount Else 0 End as Holiday_Pay,
Employer_Ref
From Accounts_Entries
where Tax_Year=2012 and Item_Description In ('WTR Accrual Debit','WTD Holiday Pay') )b

On (a.Personnel_Ref=b.Client_Or_Pers_No and a.Company_Employer=b.Employer_Ref
and rn=1)

Order by a.Personnel_Ref, b.Year_Pd


I am now getting the following:
Employer_Ref Personnel_Ref Worker_Name Worker_Status Leaving_Date Year_Pd Accrual_Value Holiday_Pay
OMWEEK MMOOR347P Andrew Moore Live 0 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore History 20120401 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore History 20120416 20121 0 113.28
OMWEEK MMOOR347P Andrew Moore Live 0 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore History 20120401 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore History 20120416 20122 57.31 0
OMWEEK MMOOR347P Andrew Moore Live 0 20123 72.42 0
OMWEEK MMOOR347P Andrew Moore History 20120401 20123 72.42 0
OMWEEK MMOOR347P Andrew Moore History 20120416 20123 72.42 0

This worker has 3 records in wv_All_Workers, I only want the record with the latest start date, ie: the LIVE one in this case. Is this possible ?

---