none
How to Get 7th eldest employee detail by date?

Answers

  • SELECT top 1 * from 
    (select top 7 * from employeetable  order by birthday asc) as a 
    order by birthday desc
    hope this helps..
    • Marked as answer by Sasicumaar Friday, November 15, 2013 5:56 AM
    Friday, November 15, 2013 5:27 AM
  • You can use the DATEDIFF function for that, eg

    ;WITH cte AS
    (
    SELECT *, DATEDIFF( year, DOJ, GETDATE() ) years_experience
    FROM dbo.HRMEmployeeMaster
    WHERE DATEDIFF( month, DOJ, GETDATE() ) > (4 * 12)
    )
    SELECT * 
    FROM cte


    • Edited by wBob Friday, November 15, 2013 6:10 AM changed to month
    • Marked as answer by Sasicumaar Friday, November 15, 2013 6:10 AM
    Friday, November 15, 2013 6:05 AM

All replies

  • Try

    with cte as
    (select top 7 birthday,a.*
    from emp_tab
    order by 1)
    select top 1 *
    from cte
    order by 1 desc


    Many Thanks & Best Regards, Hua Min

    Friday, November 15, 2013 5:16 AM
  • Try the below:

    create Table test_Nov15_1(Empid int, DOB Date)
    
    Insert into test_Nov15_1
    Values(1,GETDATE())
    ,(234,GETDATE()-1)
    ,(235,GETDATE()-100)
    ,(456,GETDATE()-200)
    ,(123,GETDATE()-47)
    ,(5682,GETDATE()-20)
    ,(685,GETDATE()-454)
    ,(3,GETDATE()-2)
    ,(453,GETDATE()-200)
    
    Select * From test_Nov15_1 Order by DOB desc
    ;With cte
    as
    (
    	Select *, DENSE_RANK()Over(order by DOB desc) Rn
    	From test_Nov15_1
    ) Select * From cte where rn=7
    
    Drop table test_Nov15_1


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 5:17 AM
  • This is hard for us to guess without seeing and DDL or sample data, but here's an example using AdventureWorks2008r2 which might help:

    USE AdventureWorks2008R2
    
    ;WITH cte AS(
    SELECT RANK() OVER( ORDER BY BirthDate ) rn, *
    FROM [HumanResources].[Employee]
    )
    SELECT *
    FROM cte
    WHERE rn = 7

    Friday, November 15, 2013 5:23 AM
  • try,

    select * From employee where 
    dob =( select min(dob) from (select top 7 dob from employee order by dob asc) A )


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 5:26 AM
  • SELECT top 1 * from 
    (select top 7 * from employeetable  order by birthday asc) as a 
    order by birthday desc
    hope this helps..
    • Marked as answer by Sasicumaar Friday, November 15, 2013 5:56 AM
    Friday, November 15, 2013 5:27 AM
  • This is my table i need to get 7th Eldest Employee?
    Friday, November 15, 2013 5:33 AM
  • Try the below:(to accommodate your actual tables)

    ;With cte
    as
    (
    	Select *, DENSE_RANK()Over(order by DOJ desc) Rn
    	From [cutechappsdb].[dbo].[HRMEmployeeMaster]
    ) Select * From cte where rn=7


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 5:42 AM
  • sorry for inconvenience all are giving different queries results are different how can i take?
    Friday, November 15, 2013 5:54 AM
  • Its easy, check yourself the result and find the result that is matching with your desired output.

    Different queries shows the different approaches to your problem, you may choose the best.

    But of course, there will not be different result, that should be one as per your desired/expected result.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Friday, November 15, 2013 5:58 AM
  • How to Get employees who experienced  more than 4 years?

    Friday, November 15, 2013 5:59 AM
  • You can use the DATEDIFF function for that, eg

    ;WITH cte AS
    (
    SELECT *, DATEDIFF( year, DOJ, GETDATE() ) years_experience
    FROM dbo.HRMEmployeeMaster
    WHERE DATEDIFF( month, DOJ, GETDATE() ) > (4 * 12)
    )
    SELECT * 
    FROM cte


    • Edited by wBob Friday, November 15, 2013 6:10 AM changed to month
    • Marked as answer by Sasicumaar Friday, November 15, 2013 6:10 AM
    Friday, November 15, 2013 6:05 AM
  • Thank you for your query can Explain this query with few words?
    Friday, November 15, 2013 6:14 AM