using FUNCTION -SQL Server query to find the current post of the employee

Question

Can you tell how to find the last updated post of the employee from the table. Two tables :Employee and EmployeeDetails. Employee fields are: EmployeeID ,EmployeeName EmployeeDetails fields are: EmployeeID, Designation, PromotionDate.

How to find the current Designation of an employee if we give an EmployeeID. Using function. I want to create a simple function to get the Designation according to the current Designation (Latest PromotionDate ) using the EmployeeID, then join with Employee table.

DROP TABLE IF EXISTS Employee

CREATE TABLE Employee
(
    EmployeeID INT NOT NULL PRIMARY KEY IDENTITY(1000,1),
    EmployeeName VARCHAR(25)
)

INSERT INTO Employee VALUES('AAA');
INSERT INTO Employee VALUES('LAAA');
INSERT INTO Employee VALUES('RSSS');
INSERT INTO Employee VALUES('SEEE');
INSERT INTO Employee VALUES('CFFF');
INSERT INTO Employee VALUES('SEEEW');
INSERT INTO Employee VALUES('MCCC');
INSERT INTO Employee VALUES('DERR');
INSERT INTO Employee VALUES('DERR');
INSERT INTO Employee VALUES('DERW');

SELECT * FROM Employee

DROP TABLE EmployeeDetails

CREATE TABLE EmployeeDetails
(
   EmployeeID INT FOREIGN KEY REFERENCES Employee(EmployeeID),
   Designation VARCHAR(25),
   PromotionDate Date
)

INSERT INTO EmployeeDetails VALUES(1000,'www','2020-11-20');
INSERT INTO EmployeeDetails VALUES(1000,'qqq','2020-01-23');
INSERT INTO EmployeeDetails VALUES(1009,'qqq','2020-09-20');


SELECT * FROM  EmployeeDetails

SELECT
    E.EmployeeID,
    E.EmployeeName,
    ED.Designation, ED.PromotionDate
FROM 
    Employee E 
JOIN  
    EmployeeDetails ED ON E.EmployeeID = ED.EmployeeID

I wrote a function for this, but I don't know how to incorporate it with the query:

 CREATE FUNCTION GetOnlyTheCurrentPost
( @EmpID INT)
RETURNS DATE
AS
BEGIN
DECLARE @PromoDate DATE
SELECT @PromoDate= MAX(PromotionDate)
FROM EmployeeDetails
WHERE EmployeeID = @EmpID
 RETURN(@PromoDate)
END

I changed the function like this as below

ALTER FUNCTION [dbo].[GetOnlyTheCurrentPost]
( @PromoDate DATE)
RETURNS DATE 
AS
BEGIN
SELECT  @PromoDate= MAX(PromotionDate)
FROM EmployeeDetails 
RETURN(@PromoDate)
END

SELECT
    E.EmployeeID,
    ED.Designation,[dbo].[GetOnlyTheCurrentPost](ED.PromotionDate) AS LatestPost
FROM
    Employee E
JOIN
     EmployeeDetails ED
     ON E.EmployeeID = ED.EmployeeID

This will show all the records not only the latest post but also every records.

Again, I changed my function. I want to get the current Designation, if i give the EmployeeID Like, SELECT [dbo].[GetOnlyTheCurrentDesignation](1011). Output should be printed according to the given corresponding EmployeeID Output : ProjectManager

    ALTER FUNCTION GetOnlyTheCurrentDesignation
(@EmpID INT)
RETURNS VARCHAR(MAX) 
AS
BEGIN
DECLARE @Designation VARCHAR(25)
SELECT @Designation=Designation, MAX(PromotionDate)
FROM EmployeeDetails
WHERE EmployeeID = @EmpID
 RETURN(@Designation)
END

Please tell me a solution to fix this

Does this answer your question? [Retrieving last record in each group from database](https://stackoverflow.com/q/4751913/2029983) — Thom A, Nov 10 '20 at 16:37
Does this answer your question? [Get top 1 row of each group](https://stackoverflow.com/q/6841605/2029983) — Thom A, Nov 10 '20 at 16:38
Actually I want to use FUNCTION to get the Employee's Designation by EmployeeID according to Current Post ,then join with Employee table — Aweelmarchons, Nov 12 '20 at 04:42

score 1 · Accepted Answer · answered Nov 10 '20 at 18:52

1

Try something like this:

SELECT
    E.EmployeeID,
    E.EmployeeName,
    ED.Designation, ED.PromotionDate
FROM 
    Employee E 
JOIN  
    (SELECT * FROM EmployeeDetails ED2
          WHERE PromotionDate = (SELECT MAX(PromotionDate) 
                                 FROM EmployeeDetails 
                                 WHERE EmployeeID = ED2.EmployeeID)) ED 
     ON E.EmployeeID = ED.EmployeeID

answered Nov 10 '20 at 18:52

user9601310

1,076
1
7
12

But i want to use FUNCTION in this query – Aweelmarchons Nov 11 '20 at 12:12
1

You can invoke the function you've written with `select e.*, dbo.GetOnlyTheCurrentPost(EmployeeID) as LatestPost from employee e`. Tested at https://dbfiddle.uk/?rdbms=sqlserver_2019&fiddle=43ff6422cb28485328fd6e1fc3779e99 (NOTE: Not sure why this question was tagged as duplicate!?) – user9601310 Nov 12 '20 at 07:40
Can you explain it a lil bit please.SELECT MAX(PromotionDate) FROM EmployeeDetails GROUP BY EmployeeID CREATE FUNCTION GetOnlyTheCurrent (@PromoDate DATE) RETURNS DATE AS BEGIN SELECT @PromoDate= MAX(PromotionDate) FROM EmployeeDetails GROUP BY EmployeeID RETURN(@PromoDate) END SELECT E.EmployeeID, E.EmployeeName, ED.Designation,ED.PromotionDate FROM Employee E JOIN ( SELECT * FROM EmployeeDetails ED2 WHERE PromotionDate = [dbo].[GetOnlyDesignation](PromotionDate)) ED ON E.EmployeeID = ED.EmployeeID Its Wrong Answer – Aweelmarchons Nov 12 '20 at 09:31
select e.*, dbo.GetOnlyTheCurrentPost(EmployeeID) as LatestPost from employee e designation should be viewed . its in EmployeeDetails table. – Aweelmarchons Nov 12 '20 at 09:46
1

Did you look at the link I provided? That uses your first version of the function which seems to me to work fine. You give the function an employee id and it returns the latest promotion date for that employee. The online demo at the link shows it in action. – user9601310 Nov 12 '20 at 11:42
Its working... Thanks a ton.... But, Sorry actually...current Designation should be print . IF SELECT [dbo].[GetOnlyTheCurrentDesignation](1011) AS DESIGNATION . Designation should be print – Aweelmarchons Nov 12 '20 at 13:48
Can you help me solve this one – Aweelmarchons Nov 12 '20 at 16:58
Just a word on use of this site, as I know you are new. When people spend time and effort working out answers that assist you, it's only polite to upvote and/or tag the responses that are helpful. I see you got impatient and asked this question again, using code taken straight from my answer here, which I feel is a bit rude considering this long dialog that's been going on. Also your questions have been quite misleading.e.g.your function was designed to return a date but now you talk about wanting "Designation" which is a different question. Anyway, I see you got your answer. Congratulations! – user9601310 Nov 12 '20 at 19:08
Please accept my sincere apologies. Im really sorry about that.im totally new to this. I dont know how to give vote – Aweelmarchons Nov 13 '20 at 04:44
Im feeling so bad, right now... I used ur code... im sorry about that too....i should acknowledge u first.. Im really thankful to you. even i dint acknowledged at that time. I tried to edit in this query itself.But i couldn't do that.thats y i raise query as another question. – Aweelmarchons Nov 13 '20 at 07:23
1

That's fine, you've got it! A little acknowledgement goes a long way! – user9601310 Nov 13 '20 at 09:09
I totally agree with you. Thank you again. – Aweelmarchons Nov 13 '20 at 10:39
Definitely,i will keep this in my mind and take care of this in my future . – Aweelmarchons Nov 13 '20 at 11:01

using FUNCTION -SQL Server query to find the current post of the employee

1 Answers1