How to get minimum date of each line manager against each employee

sql min date in where clause
sql select min value from multiple rows
sql min group by
sql where min date
how to get maximum date in cognos report studio
minimum count in sql
min max function in mysql
sql aggregate functions

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:

Data

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 , 18.1.3.6.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 ROW_NUMBER():

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.