How to get minimum date of each line manager against each employee
I have an employee table where the fields are:
Reporting_Change_date, empid, empfullname, LineManagerID,LineManager_name, and so on.I am asked to find minimum reporting change date against each employees.
I was thinking that this problem could be solved like this:
Reporting_Change_Date |Emp Name |Line Manager Name -------------------------|--------------|----------------- 01-Jan-19 |Anand Kumar |Amitabh 02-Jan-19 |Anand Kumar |Amitabh 03-Jan-19 |Anand Kumar |Amitabh 04-Jan-19 |Anand Kumar |Amitabh 05-Jan-19 |Anand Kumar |Sachin 06-Jan-19 |Anand Kumar |Sachin 07-Jan-19 |Anand Kumar |Sachin 08-Jan-19 |Anand Kumar |DEEpak 09-Jan-19 |Anand Kumar |DEEpak 10-Jan-19 |Anand Kumar |DEEpak 11-Jan-19 |Anand Kumar |Amitabh
I want a Output like this:
Reporting_Change_Date |Emp Name |Line Manager Name -------------------------|--------------|----------------- 01-Jan-19 |Anand Kumar |Amitabh 05-Jan-19 |Anand Kumar |Sachin 08-Jan-19 |Anand Kumar |DEEpak 11-Jan-19 |Anand Kumar |Amitabh
I have tried this code:
SELECT Min(metricdate)MetricDate, empid, linemanagername FROM [HR_Mart].[Mart].[hr_nonadvisorbyday] WHERE linemanagername <> 'Craig Nixon' AND empid = '245531' GROUP BY empid, linemanagername ORDER BY metricdate
you can use lag and min as below:
;with cte_bucket as ( select *, sum(nextlmn) over(order by reporting_change_date) bucket from ( select *, case when linemanager_name <> lag(linemanager_name) over(order by reporting_change_date) then 1 else 0 end as nextlmn from #employee ) a ) select min(reporting_change_date) as reporting_change_date, empfullname, linemanager_name from cte_bucket group by empfullname, linemanager_name, bucket
Code below https://rextester.com/SVXZ36406
Output as below:
+-----------------------+-------------+------------------+ | reporting_change_date | empfullname | linemanager_name | +-----------------------+-------------+------------------+ | 2019-01-01 | Anand Kumar | Amitabh | | 2019-01-05 | Anand Kumar | Sachin | | 2019-01-08 | Anand Kumar | DEEpak | | 2019-01-11 | Anand Kumar | Amitabh | +-----------------------+-------------+------------------+
Digital Forensics Processing and Procedures: Meeting the , 188.8.131.52.1 Screening Minimum Level of Employee Security As a minimum, all new the applicant should declare any criminal record that they have according to the with the past employer's HR Department of the applicant's employment (e.g., dates, post, and reason for leaving) and preferably past Line Manager's Things get particularly sticky when romantic relationships form between a manager and a direct report—which can have an impact on employee morale and put the company at compliance risk. How common is this? Our survey also uncovered that 5 percent of employees are dating their manager at work. Though HR works to mitigate workplace risk
Assuming that Reporting_Change_Date is a date data type, you can use the following query:
select [Emp Name], [Line Manager Name], min([Reporting_Change_Date]) as MinReporting_Change_Date from employeetable group by [Emp Name] [Line Manager Name]
The Manager's Guide to Rewards: What You Need to Know to Get the , What You Need to Know to Get the Best For--and From--your Employees Doug As noted earlier, line managers are the preferred messengers for most have a talk with HR (take an HR manager to lunch) or the outside consultants (let Ask for talking points or a script to ensure that they tell about all the relevant details. Line managers oversee other employees and the operations of a business while reporting to a higher-ranking manager. They play an important role in the operation of many businesses, supervising and managing workers on a daily basis and acting as a liaison between employees and upper management.
What you have here is a gaps and islands problem. The user's manager changed back to a previous value, so the
MIN value is still the first time that person was their manager. One method of creating the island is using
USE Sandbox; GO --Sample Table CREATE TABLE dbo.hr_nonadvisorbyday(ReportingDate date, EmpName varchar(50), ManagerName varchar(50)); GO --Sample data INSERT INTO dbo.hr_nonadvisorbyday VALUES('01-Jan-19','Anand Kumar','Amitabh'), ('02-Jan-19','Anand Kumar','Amitabh'), ('03-Jan-19','Anand Kumar','Amitabh'), ('04-Jan-19','Anand Kumar','Amitabh'), ('05-Jan-19','Anand Kumar','Sachin'), ('06-Jan-19','Anand Kumar','Sachin'), ('07-Jan-19','Anand Kumar','Sachin'), ('08-Jan-19','Anand Kumar','DEEpak'), ('09-Jan-19','Anand Kumar','DEEpak'), ('10-Jan-19','Anand Kumar','DEEpak'), ('11-Jan-19','Anand Kumar','Amitabh'); GO --Your (heavily editted) Solution SELECT Min(ReportingDate) MetricDate, EmpName, ManagerName FROM dbo.[hr_nonadvisorbyday] WHERE ManagerName <> 'Craig Nixon' AND EmpName = 'Anand Kumar' GROUP BY EmpName, ManagerName ORDER BY metricdate; GO --Solution WITH Grps AS( SELECT ReportingDate, EmpName, ManagerName, ROW_NUMBER() OVER (PARTITION BY EmpName ORDER BY ReportingDate) - ROW_NUMBER() OVER (PARTITION BY EmpName, ManagerName ORDER BY ReportingDate) AS Grp FROM dbo.hr_nonadvisorbyday WHERE ManagerName <> 'Craig Nixon' AND EmpName = 'Anand Kumar') SELECT Min(ReportingDate) MetricDate, EmpName, ManagerName FROM Grps GROUP BY EmpName, ManagerName, Grp; GO --Clean up DROP TABLE dbo.hr_nonadvisorbyday
Fundamentals of Human Resource Management, This statement from Nike.com is a little shocking, but the next line goes on to explain: “We soak it up. Even the smallest entrepreneurial organization with one or two employees must recognize responsibility for all four HR functions. Line managers will always have many of these responsibilities whether a formal human For each employee, find all less earning people with the same role – here we need to perform two actions: 1) left join the table with itself using the role field. 2) add a condition to make sure the salary is the highest.
Iron Age, Set solid, minimum 50 words $2.00 Each additional word 4c All capitals, EMPLOYMENT MANAGER, with welfare, police and employment experience. GENERAL or WORKS MANAGER now employed by company making Pole Line Hardware, Metallurgist, have made a specialty of galvanizing, have manufactured the But, retention is a big area where can employers focus their attention to get the biggest bang for their buck in the shortest space of time. As we have talked about in previous articles, there are many complicated reasons that employee leave and it can be a complicated chain reaction of events, but one recurring theme that I have noticed is the impact that the employee’s direct manager has
Small Business Employment Law For Dummies, If you are unable to speak to your line manager personally, you should speak to (names). You must inform your line manager as soon as possible of any change in the date of days, you are required as an absolute minimum to contact your line manager on a A new doctor's certificate must be submitted each week. 3. Use an employee scheduling software. The classic timers still serve useful but they lack a full set of features that guarantee workplace productivity.
Mastering SQL MIN Function Through Practical Examples, To find the lowest (minimum) salary of employees, you apply the MIN function to the salary For each group, the query returns the row with the lowest salary. Write a function to get no of employees under each manager in the hierarchy not just their direct reports. It may be assumed that an employee directly reports to only one manager. In the above dictionary the root node/ceo is listed as reporting to himself. Output should be a Dictionary that contains following.