Get the last record inserted

I am trying to get the last record inserted into tblSRPAttendance, a person can have multiple records in here, I treid the ranking fucntion but It show all their records from tblSRPAttendance and then cuts out my LEft join function to show everything.  I want to be able4 to tie in there last record inserted into that table with all their personnel informaton from teh main table.


Select	Case when (mn.strAttchUIC <> NULL or mn.strAttchUIC <> '') THEN mn.strAttchUic else strUIC END UNIT,
		mn.strFullname NAME,
		mn.strRank RANK,
		a.DT_SRP,
		a.strSRPName SRP_TYPE,
		a.bitDep as SRP_DEP,
		Case When mn.bitDep = 0 THEN 'DEP' ELSE 'NONDEP' END C_DEP
from	tblMNNatPersonnel mn LEFT JOIN
		(Select sa.bitDeployable, Convert(varchar(10), sa.dtTimein, 111) DT_SRP, sa.strSSN, s.strSRPName, RNK  = row_number() Over (partition by strssn Order by strssn)
		from tblSRPAttendance sa INNER JOIN tblSRP s on s.intSRPId = sa.intSRPID where sa.bitActive = 0)  a on a.strSSN = mn.strSSN


Also tried this but it returns everything on the personnel die but nothing on the record side.
Select	Case when (mn.strAttchUIC <> NULL or mn.strAttchUIC <> '') THEN mn.strAttchUic else strUIC END UNIT,
		mn.strFullname NAME,
		mn.strRank RANK,
		a.DT_SRP,
		a.strSRPName SRP_TYPE,
		a.bitDep as SRP_DEP,
		Case When mn.bitDeployable = 0 THEN 'DEP' ELSE 'NONDEP' END C_DEP
from	tblMNNatPersonnel mn LEFT JOIN
		(Select  top 1 sa.bitDep, Convert(varchar(10), sa.dtTimein, 111) DT_SRP, sa.strSSN, s.strSRPName 
		from tblSRPAttendance sa INNER JOIN tblSRP s on s.intSRPId = sa.intSRPID where sa.bitActive = 0 order by dtTimeIn desc) a on a.strSSN = mn.strSSN

Open in new window

kdeutschAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
should your first query be this?


not really following what the problem is

please restate...
please detail what you mean and how determined last attendance row...

some example data and results you desire would also assist

Select	Case when coalesce(mn.strAttchUIC,'')   <> '' THEN mn.strAttchUic else strUIC END UNIT,
		mn.strFullname NAME,
		mn.strRank RANK,
		a.DT_SRP,
		a.strSRPName SRP_TYPE,
		a.bitDep as SRP_DEP,
		Case When mn.bitDep = 0 THEN 'DEP' ELSE 'NONDEP' END C_DEP
from	tblMNNatPersonnel mn LEFT JOIN
                ( select * from 		
(Select sa.bitDeployable, Convert(varchar(10), sa.dtTimein, 111) DT_SRP, sa.strSSN, s.strSRPName
                  , RNK  = row_number() Over (partition by strssn Order by strssn)
		from tblSRPAttendance sa 
                INNER JOIN tblSRP s 
                   on s.intSRPId = sa.intSRPID 
                 where sa.bitActive = 0) as x
                 where rnk=1) as a
                 on a.strSSN = mn.strSSN

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
kdeutschAuthor Commented:
HI,

Nope the query above took care of the problem what I did not know how to do was put in the where clause for teh rank fucntion, now i know to make it a sub query of the query.

Thanks
0
kdeutschAuthor Commented:
Thanks
0
LowfatspreadCommented:
ok good

please also note the coalesce as well as the only way to test for NULL  is    via IS (Not) NULL ...  
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.