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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.