SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Select last record from a table
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

rusmanicai
Starting Member

USA
12 Posts

Posted - 07/01/2013 :  15:37:43  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 07/01/2013 :  16:42:40  Show Profile  Reply with Quote
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;
Go to Top of Page

rusmanicai
Starting Member

USA
12 Posts

Posted - 07/02/2013 :  09:44:57  Show Profile  Reply with Quote
Thank you James. That worked.

RJulia
Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3651 Posts

Posted - 07/02/2013 :  09:53:58  Show Profile  Reply with Quote
You are welcome - glad to help.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.06 seconds. Powered By: Snitz Forums 2000