Show Last record of Employee a/c to EndDate

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

CSS

i have some  tables

name employeeshifts
Shifts 1= A,2=B,3=C

columns are
sno     Shifts      EmployeeID      Startdate                   EndDate                     ChangedDate
1       1               1           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         2012-12-13 00:00:00.000

2       2               1           2012-12-13 00:00:00.000 2012-12-18 00:00:00.000         null

3       3               1           2012-12-19 00:00:00.000 2012-12-21 00:00:00.000         null

4       1               2           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         null

5       2               4           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         null

6       3               5           2012-12-12 00:00:00.000 2012-12-15 00:00:00.000         null

7       1               9           2012-12-16 00:00:00.000 2012-12-18 00:00:00.000         2012-12-16 00:00:00.000

8       2               9           2012-07-07 00:00:00.000 2012-08-17 00:00:00.000         null

there is employee table as well

what i need to show
last record of employee where employee has performed or is performing duty

suppose
employee 1 has performed his last duty a/c to above table
2012-12-21 00:00:00.000 in C shifts
Employee 9 will perform  will be performing duty
2012-08-17 00:00:00.000 in B shifts

Just like this i need to show last record where employee performs duty

thanks

Posted 4-Jul-12 2:55am

qasimidl

Add a Solution

3 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Sebastian T Xavier · Answer 1 · 2012-07-04T03:02:00

Solution 1

what about using top1 with

SQL

order by EndDate  desc

and add group by employeeid if needed

Regards
Sebatian

Posted 4-Jul-12 3:02am

Sebastian T Xavier

Updated 4-Jul-12 3:09am

v2

devsuja · Answer 2 · 2012-07-04T23:55:00

hi,

u need the query employeeid enddate or grouping based on shifts,

If u need employeeid enddate

select * from employeeshifts group by employeeid order by endate desc

if it is on shifts with employee

select * from employeeshifts group by shifts,employeeid order by endate desc

__TR__ · Answer 3 · 2012-07-05T01:10:00

If you want to get the latest record of all employees where employee has performed or is performing duty you can use the below query.

SQL

SELECT sno
, EmployeeID
, CASE WHEN Shifts = 1 THEN 'A'
       WHEN Shifts = 2 THEN 'B'
       WHEN Shifts = 3 THEN 'C' END AS Shifts
, StartDate
, EndDate
, ChangedDate
 FROM employeeshifts
WHERE sno IN
(
    SELECT MAX(sno) AS sno FROM employeeshifts
    GROUP BY EmployeeID
)

Show Last record of Employee a/c to EndDate

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0