Tell me more ×
Stack Overflow is a question and answer site for professional and enthusiast programmers. It's 100% free, no registration required.
up vote 0 down vote favorite 
<pre>
I have 3 tables and one table valued function: EmpHistory,EmpRank,Emp and fnEmpRank.
The sample data are given as follows:
EmpHistory
EmpHistID  EmpID  RankID  MonitorDate  RankName
1          aba     JPR     2008-10-06   Junior Prof
2          aba     JPR     2009-11-07   Junior Prof
3          aba     TERM    2012-2-08    Termoinated Prof
4          aba     ASST    2012-6-22     lab Assistant
5          aba     ASST    2012-7-2      Lab Assistant
6          aba     TSST    2012-8-4      Terminated Assistant

EmpRank
RankID    RankName
JPR       Junior Professor
SPR       Senior Professor
ASST      Junior Assistant
SASST     Senior Assistant
PL        Principal

Employee
EmpID    EmpStartDate
aba       2008-10-06
abc01     2007-09-23
sdh        2009-7-26
sbs        2012-2-8


The fnEmpRank function takes the emproleID and gives the employee history same as the empHistory Table. There is also empoyeerole table which has employeeroleid column.

Now my problem is: I want the second last professor rank of the employee i.e in this case I want Junior Professor row(i.e) 2nd row from emphistory table). Currntly my code is using emphistory table but now intead of that table I want to use fnEmpRank as it gives the same data. I am also giving the sample code.


select
a.EmpID,
a.StartDate,
J.RankID,
c.MonitorDate,
from dbo.vwEmployee A(nolock)
INNER join dbo.EmpHistory c(nolock) on c.Empid = a.EmpID
and c.EmpHistoryID = (select max(c1.EmpHistoryID)
            from dbo.EmpHistory c1(nolock)
            where c1.Empid = c.EmpID
            and c1.MonitorDate = 
                    (

I have 3 tables and one table valued function: EmpHistory,EmpRank,Emp and fnEmpRank. The sample data are given as follows:

create table EmpHistory( EmpHistID int, EmpID varchar, RankID varchar, Monitordate Date, Rankname varchar)

insert into EmpHistory select 1,'aba','JPR','2008-10-6','Junior Professor'

insert into EmpHistory select 2,'aba','JPR','2009-11-7','Junior Professor'

insert into EmpHistory select 3,'aba','TERM','2012-2-8','Terminated Prof'

insert into EmpHistory select 4,'aba','ASST','2012-6-22','Lab Assistant'

insert into EmpHistory select 5,'aba','ASST','2012-7-2','Lab Assistant'

insert into EmpHistory select 1,'aba','JPR','2012-8-4','Terminated Assistant'

create table EmpRank( RankID varchar, RankName varchar )

insert into EmpRank select 'JPR','Junior Professor'

insert into EmpRank select 'SPR','Senior Professor'

insert into EmpRank select 'ASST','Junior Assistant'

insert into EmpRank select 'SASST','Senior Assistant'

insert into EmpRank select 'PL','Principal'

create table Employee( EmpID varchar, EmpStartDate date )

insert into Employee select 'aba','2008-10-06'

insert into Employee select 'abc01','2007-9-23'

insert into Employee select 'sdh','2009-7-26'

insert into Employee select 'sbs','2012-2-8'

The fnEmpRank function takes the emproleID and gives the employee history same as the empHistory Table. There is also empoyeerole table which has employeeroleid column.

Now my problem is: I want the second last professor rank of the employee i.e in this case I want Junior Professor row(i.e) 2nd row from emphistory table). Currntly my code is using emphistory table but now intead of that table I want to use fnEmpRank as it gives the same data. I am also giving the sample code.


select
a.EmpID,
a.StartDate,
J.RankID,
c.MonitorDate,
from dbo.vwEmployee A(nolock)
INNER join dbo.EmpHistory c(nolock) on c.Empid = a.EmpID
and c.EmpHistoryID = (select max(c1.EmpHistoryID)
            from dbo.EmpHistory c1(nolock)
            where c1.Empid = c.EmpID
            and c1.MonitorDate = 
                    (
                    SELECT MAX(C2.MonitorDate)
                    FROM dbo.EmpHistory C2
                    WHERE C2.EmpID = C1.EmpID
                    )

                    )
join dbo.EmpRank d(nolock) on d.RankID = a.RankID
left join dbo.EmpHistory f(nolock) on f.EmpID = a.EmpID
    and f.EmpHistoryID = (select max(g.EmpHistoryID)
                from dbo.EmpHistory g(nolock)
                where g.EmpID = a.EmpID
                AND G.RankID not like 'T%'
                and g.EmpHistoryID &lt; c.EmpHistoryID)
left join dbo.EmpRank h(nolock) on h.RankID = f.RankID

LEFT JOIN dbo.EmpHistory J(NOLOCK) ON J.EmpID = A.EmpID 
    AND J.EmpHistoryID = (
        SELECT max(K.EmpHistoryID )
        FROM dbo.EmpHistory K(NOLOCK) 
        WHERE K.EmpID = J.EmpID
        AND K.AgentRankID NOT LIKE 'T%'
        AND K.MonitorDate = (
            SELECT max(M.MonitorDate )
            FROM dbo.EmpHistory M(NOLOCK)
            WHERE M.EmpID = J.EmpID 
            AND M.RankID NOT LIKE 'T%'
        )
    )

where
A.Prof=1
 c.RankID like 'T%'
AND c.RankID <>'TSST'
AND A.StartDate is not null

Here there is one more problem: Even if the Employee is terminated from professor to Assitant, A.Prof values is still 1 and basically Assistant dont have the start dates but when professor are transformed to Assitant, they still contain the start date. How can I handle this in the code. Basically this code assumes that that if emp has the start date then he is the professor. Can any one help me?


                    SELECT MAX(C2.MonitorDate)
                    FROM dbo.EmpHistory C2
                    WHERE C2.EmpID = C1.EmpID
                    )

                    )
join dbo.EmpRank d(nolock) on d.RankID = a.RankID
left join dbo.EmpHistory f(nolock) on f.EmpID = a.EmpID
    and f.EmpHistoryID = (select max(g.EmpHistoryID)
                from dbo.EmpHistory g(nolock)
                where g.EmpID = a.EmpID
                AND G.RankID not like 'T%'
                and g.EmpHistoryID < c.EmpHistoryID)
left join dbo.EmpRank h(nolock) on h.RankID = f.RankID

LEFT JOIN dbo.EmpHistory J(NOLOCK) ON J.EmpID = A.EmpID 
    AND J.EmpHistoryID = (
        SELECT max(K.EmpHistoryID )
        FROM dbo.EmpHistory K(NOLOCK) 
        WHERE K.EmpID = J.EmpID
        AND K.AgentRankID NOT LIKE 'T%'
        AND K.MonitorDate = (
            SELECT max(M.MonitorDate )
            FROM dbo.EmpHistory M(NOLOCK)
            WHERE M.EmpID = J.EmpID 
            AND M.RankID NOT LIKE 'T%'
        )
    )

where
A.Prof=1
 c.RankID like 'T%'
AND c.RankID <>'TSST'
AND A.StartDate is not null

Here there is one more problem: Even if the Employee is terminated from professor to Assitant, A.Prof values is still 1 and basically Assistant dont have the start dates but when professor are transformed to Assitant, they still contain the start date. How can I handle this in the code. Basically this code assumes that that if emp has the start date then he is the professor. Can any one help me?
</pre>
share|improve this question
It helps if your sample data and your descriptions match. Your commentary refers to several xRoleID columns, but these don't exist in your samples. I could take a guess, but it's easier for you to correct this since you know what you mean (hopefully) – Damien_The_Unbeliever Oct 19 '12 at 6:59

1 Answer

up vote 0 down vote 
 create fnemprank(@inputrankid int)
 returns @result table(
         EmpHistID int,
         EmpID nvarchar,
         RankID nvarchar,
         MonitorDate  date,
         RankName nvarchar)
 as
 begin
     insert into @result (EmpHistID,EmpID,RankID,MonitorDate,RankName)
     select top 1 * from emphistory eh where eh.rankid=@inputrankid 
     order by eh.monitordate desc
 return
 end
share|improve this answer
sorry give the datatypes accordingly.. – SRIRAM Oct 19 '12 at 6:03
i also have the function.... I want to use the function in the query – user1599392 Oct 19 '12 at 7:10
just call the function in your query. show me your query so that i may try – SRIRAM Oct 19 '12 at 7:11
I have provided the code.... – user1599392 Oct 19 '12 at 15:29

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.