Select last record from a table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Select last record from a table

Author

Topic

rusmanicai
Starting Member

13 Posts

Posted - 2013-07-01 : 15:37:43

I am trying to create a report of all employees and their last login process code.
I have three tables to work with:
1. Employee (PK EmployeeCode)
2. Production (PK Code-index key, assigned every time a new line is added to the table- not the employee code, although that field is part of the table structure)-lists all employees and all processes they clocked into for every day
3. ShopLogIn (PK EmployeeCode) - lists last time the employee clocked in with any process.
My report needs to show:
EmployeeName, EmployeeCode -Employee table
LastLoginTime-ShopLogIn table or Production table
LastProcess -Production Table

How can I get that? This is what I did so far, but it still shows me all processcodes the only thing that I accomplished is displaying the last time they clocked into some process, show for every process, per employeecode.

SELECT shoplogin.employeecode,
shoplogin.loggeddate,
shoplogin.loggedshift,
shoplogin.entrytime,
employee.employeename,
employee.departmentcode,
employee.plantid AS EmployeePlantID
,Process=(
select top 1
a.ProcessCode
from
Production a
inner join
(select max(Code) as maxid from Production group by ProcessCode) as b on
a.Code = b.maxid
)
,production.ProcessCode
,production.EntryTime
,production.Code
FROM shoplogin
INNER JOIN employee
ON shoplogin.employeecode = employee.code
left join Production on production.EmployeeCode=Employee.Code and ShopLogIn.LoggedDate=production.StartDate
WHERE production.EmployeeCode=ShopLogIn.EmployeeCode

Result:
employeecode loggeddate loggedshift entrytime departmentcode EmployeePlantID Process ProcessCode EntryTime Code
0059 2013-07-01 00:00:00.000 1 1900-01-01 07:20:20.000 10 MSI 0 9020 1900-01-01 12:11:21.000 380031
0059 2013-07-01 00:00:00.000 1 1900-01-01 07:20:20.000 10 MSI 0 9027 1900-01-01 12:11:21.000 380032
0059 2013-07-01 00:00:00.000 1 1900-01-01 07:20:20.000 10 MSI 0 9025 1900-01-01 12:11:21.000 380498
0059 2013-07-01 00:00:00.000 1 1900-01-01 07:20:20.000 10 MSI 0 9027 1900-01-01 12:11:21.000 380542
0136 2013-07-01 00:00:00.000 1 1900-01-01 06:02:00.000 12 MSI 0 9045 1900-01-01 12:22:40.000 380051
0136 2013-07-01 00:00:00.000 1 1900-01-01 06:02:00.000 12 MSI 0 9026 1900-01-01 12:22:40.000 380005
0136 2013-07-01 00:00:00.000 1 1900-01-01 06:02:00.000 12 MSI 0 9020 1900-01-01 12:22:40.000 379924
0136 2013-07-01 00:00:00.000 1 1900-01-01 06:02:00.000 12 MSI 0 9025 1900-01-01 12:22:40.000 380461
0136 2013-07-01 00:00:00.000 1 1900-01-01 06:02:00.000 12 MSI 0 9045 1900-01-01 12:22:40.000 380587
0219 2013-07-01 00:00:00.000 1 1900-01-01 07:03:38.000 90 MSI 0 9020 1900-01-01 13:59:54.000 379994
0219 2013-07-01 00:00:00.000 1 1900-01-01 07:03:38.000 90 MSI 0 4723 1900-01-01 13:59:53.000 379995
0219 2013-07-01 00:00:00.000 1 1900-01-01 07:03:38.000 90 MSI 0 4724 1900-01-01 13:59:53.000 380129
0219 2013-07-01 00:00:00.000 1 1900-01-01 07:03:38.000 90 MSI 0 4723 1900-01-01 13:59:53.000 380173
0219 2013-07-01 00:00:00.000 1 1900-01-01 07:03:38.000 90 MSI 0 4723 1900-01-01 13:59:53.000 380207

Thank you!

RJulia

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-01 : 16:42:40

Try something like shown below. I may not have all the columns correctly, but the basic idea is the following:

1. You start with the Employee table and pick up all the columns you want.
2. You use an OUTER APPLY to pick up just one row from the shoplogin table. Which row you pickup is determined by the order by clause in the outer apply query.
3. Do the same thing for the produciton table.

SELECT
	s.loggeddate,
	s.loggedshift,
	s.entrytime,
    e.employeename ,
    e.departmentcode ,
    e.plantid AS EmployeePlantID,
    p.ProcessCode,
    p.EntryTime,
    p.Code
FROM
	Employee e
	OUTER APPLY
	(
		SELECT 	TOP (1) loggedindate, loggedshift, entrytime
		FROM shoplogin s
		WHERE s.EmployeeCode = e.code
		ORDER BY s.loggeddate DESC, s.entrytime DESC
		
	) AS s
	OUTER APPLY
	(
		SELECT TOP (1) ProcessCode,EntryTime,Code
		FROM Production p
		WHERE p.EmployeeCode = e.code
		ORDER BY ProcessCode DESC
	) p;

rusmanicai
Starting Member

13 Posts

Posted - 2013-07-02 : 09:44:57

Thank you James. That worked.

RJulia

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-07-02 : 09:53:58

You are welcome - glad to help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources