October 19, 2012 at 12:09 am
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 < 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?
October 19, 2012 at 8:15 am
There is nowhere near enough information to be able to help here. Please see the first link in my signature about best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 9:17 am
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).
If you just need the second last professor rank, you can use the following query. If you need something different, you should explain yourself better.
WITH CTE AS(
SELECT a.EmpID, a.StartDate, c.RankID, c.MonitorDate,
ROW_NUMBER() OVER( PARTITION BY a.EmpID ORDER BY a.StartDate DESC) rn
FROM dbo.vwEmployee A
INNER JOIN dbo.EmpHistory c ON c.Empid = a.EmpID
WHERE RankID IN( 'JPR', 'SPR', 'TERM'))
SELECT EmpID, StartDate, RankID, MonitorDate
FROM CTE
WHERE rn = 2
October 19, 2012 at 9:23 am
Hello Luis,
Actually, there is table valued function which gives the same data as the emphistory table. I want to use that tabel instead of CTE. I have also provided the existing code. I want to add the table valued function to get the results.
TVF
fn_name(empRoleID) gives the exact same data as the emphistory table. can you help me out?
October 19, 2012 at 10:15 am
I'm not sure that I can help you, because I'm not sure of what you want.
If the TVF gives the same result as the table, why won't you use the table?
What do you want to show?
Your code is confusing and you really should include comments in it.
We're willing to help but you need to help us out. It's good that you included DDL and sample data, but now we need the expected results.
October 19, 2012 at 11:20 am
Hello,
Using the table EmpHistory, it is not satisfying all the rules. So I want to use this function as it satisfies all the rules. This is what I want:
I have attached data in tabular format in excel sheet. In that excel sheet, there is emphistory table. See into that data. I want the latest professor rank i.e jpr(2nd record). So I want to join this tble valued function to the query.
October 19, 2012 at 11:24 am
deepeshdhake (10/19/2012)
Hello,Using the table EmpHistory, it is not satisfying all the rules. So I want to use this function as it satisfies all the rules. This is what I want:
I have attached data in tabular format in excel sheet. In that excel sheet, there is emphistory table. See into that data. I want the latest professor rank i.e jpr(2nd record). So I want to join this tble valued function to the query.
Unfortunately this doesn't really make anything an easier for us. It is much preferred if you can provide ddl (create table statements), sample data (insert statements) and desired output based on your sample data. Take a look at the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 19, 2012 at 11:27 am
I have also provided the ddl statements
October 19, 2012 at 11:38 am
deepeshdhake (10/19/2012)
Hello,Using the table EmpHistory, it is not satisfying all the rules. So I want to use this function as it satisfies all the rules. This is what I want:
I have attached data in tabular format in excel sheet. In that excel sheet, there is emphistory table. See into that data. I want the latest professor rank i.e jpr(2nd record). So I want to join this tble valued function to the query.
The file didn't have anything new
We don't know which are the rules, and we don't know what's the function doing.
Why won't you use the CTE that I posted?
October 19, 2012 at 1:26 pm
Hello,
I have updated the file. There are tables and functions in it and also the requirement is specified in requirement sheet. Can u help me out?
October 19, 2012 at 1:41 pm
So just telling us that the table value function returns the same thing as table is not helpful. We speak in t-sql around here. Why would you have a table value function that is nothing more than select * from a table? Can you post the ddl for this function? It would be far more helpful if you could post your details in something that is not an office document. There are at least two people that are trying to help but you are not helping us understand the problem.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply