quote:

---

Originally posted by kdeutsch

What I am doing is trying to get the last record for an individual inserted into this table as part of my sub query. tblSRPAttendance

I have tried row_number, top 1 but nothing seems to limit it to the last record inserted.

ALTER view [dbo].[vw_UnitSRpReadiness]
as
Select s.SSN_SM strssn,
s.RANK,
s.NAME,
Convert(varchar(10), s.DOB, 111) DOB,
COnvert(varchar(10), a.dttimein, 111) dttimein,
s.RPT_SEQ_CODE,
s.ATCH_CODE,
s.STAT,
s.UIC
from OPENQUERY (SIDPERS, 'SELECT vw.SSN_SM, vw.RANK,
vw.NAME,
vw.RPT_SEQ_CODE,
vw.ATCH_CODE,
vw.STAT,
pt.DOB,
Case WHEN ATCH_CODE = ''A'' THEN ATCH_UIC ELSE ASGN_UIC END UIC
from DOIM_UMR_VW vw
LEFT JOIN PERS_PERSON_TBL pt on pt.SSN_SM = vw.SSN_SM WHERE vw.SSN_SM IS NOT NULL') s
LEFT JOIN (select dttimein, strssn, ROW_NUMBER() over(partition by strssn order by dttimein desc) as rn from tblAttendance) a on a.strSSN = s.SSN_SM
Where s.SSN_SM not in (Select strssn from tblPersonnel where intSRpId = 156 and dtExpire > GETDATE())
or
s.SSN_SM not in (Select strssn from tblPersonnel where intSRpId = 157 and dtExpire > GETDATE())

GO

---

ALTER view [dbo].[vw_UnitSRpReadiness]
as
Select s.SSN_SM strssn, 
s.RANK, 
s.NAME, 
Convert(varchar(10), s.DOB, 111) DOB,
COnvert(varchar(10), a.dttimein, 111) dttimein,
s.RPT_SEQ_CODE,
s.ATCH_CODE,
s.STAT,
s.UIC
from OPENQUERY (SIDPERS, 'SELECT vw.SSN_SM, vw.RANK, 
vw.NAME,
vw.RPT_SEQ_CODE,
vw.ATCH_CODE,
vw.STAT,
pt.DOB,
Case WHEN ATCH_CODE = ''A'' THEN ATCH_UIC ELSE ASGN_UIC END UIC 
from DOIM_UMR_VW vw 
LEFT JOIN PERS_PERSON_TBL pt on pt.SSN_SM = vw.SSN_SM WHERE vw.SSN_SM IS NOT NULL') s 
LEFT JOIN (select dttimein, strssn, 
   ROW_NUMBER() over(partition by strssn order by dttimein desc) as rn from tblAttendance) a 
   on a.strSSN = s.SSN_SM AND rn=1
Where s.SSN_SM not in (Select strssn from tblPersonnel where intSRpId = 156 and dtExpire > GETDATE()) 
or 
s.SSN_SM not in (Select strssn from tblPersonnel where intSRpId = 157 and dtExpire > GETDATE())

GO