Thanks for your response LampreyBelow is the codeFrom the resulting query what im trying to eliminate are lines 1 and 2 (learner_id 44302 and 47316). but im not too sure how i would do this. The first four learners are the same people but have been referred 4 times, only thing in the system that is same for this learner is name, dob and NI number, everything else will be different. All i would like to capture is where there is a Y for inductionchecklist for a Ni number, that and anything after that, however if someone hasn't got a Y ever i need to have this in the results tooHope its makes sense - if i could post a spreadsheet of example results as i can't figure how to attach data. Apologies if this is something easy.CREATE TABLE [dbo].[WPR]( [Learner_ID] [int] NOT NULL, [Firstname] [char](30) NULL, [Surname] [char](30) NULL, [NI] [char](9) NULL, [DOB] [smalldatetime] NULL, [Datereferral] [smalldatetime] NULL,) CREATE TABLE [dbo].[HISTORY]( [HistoryID] [int] NOT NULL, [Learner_ID] [int] NULL, [DateEntered] [datetime] NOT NULL, [Category_ID] [int] NULL) INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (22161,'JEFF','SMITH','KK991155K','1979-12-28 00:00','2012-08-07 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (42457, 'JO','BLOGGS','PP059991Z','1968-09-28 00:00','2012-06-01 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (47962, 'JOHN','SMITH','TT229940Z', '1966-06-03 00:00','2012-08-16 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (44302, 'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-06-27 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (47316, 'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-08-07 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (50359, 'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-09-19 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (53672, 'AUGUSTUS','LOCKWOOD','JK221199Z','1965-12-25 00:00','2012-10-31 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (59305, 'DEAN','BENT','ZZ999999Z','1974-02-01 00:00','2013-01-15 00:00')INSERT INTO wpr (Learner_ID, Firstname, Surname, NI,DOB,Datereferral) VALUES (60773, 'DEAN','BENT','ZZ999999Z','1974-02-01 00:00','2013-02-01 00:00')INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered,Category_ID) VALUES (1,47962, '2012-08-16 00:00',215)INSERT INTO HISTORY (HistoryID,Learner_ID,DateEntered,Category_ID) VALUES (2,50359, '2012-09-19 00:00',215)select w.NI, w.Learner_ID, w.Datereferral, ROW_NUMBER() OVER(PARTITION BY w.ni ORDER BY w.Datereferral ASC) as ReferralPosition, case when h.Learner_ID IS null then 'N' else 'Y' end as InductionChecklistReceivedfrom wpr w left join HISTORY H (nolock) on w.Learner_ID = h.Learner_ID and h.Category_ID = 215Order by w.NI
Ketan M Mistry