Get star employee record from attendance according to each Month

user_17475924 0
Viewed 120

I have Employee and Employee details table. In Employee table it has column like Id(Primary Key), Name, Gender, Joining Date, Department and so on.

In Employee Details Table it has column like Id(Primary Key), Employee Table Id(Foreign Key), Current Salary, Daily login Time, Daily logout time and so on.

What I am trying to achieve is, finding the STAR Employee of the month. The formula is,

  • First I need to find the attendance of each employee according to the month, which I am able to do and below is the result.
Id Name Attendance Month
1 Ryan 20 January
2 Joanna 19 January
3 Tom 21 January
4 Lucy 10 January
5 Frank 15 January
6 Jane 17 January
7 Robert 11 January
8 Ryan 18 February
9 Joanna 17 February
10 Tom 20 February
11 Lucy 16 February
12 Frank 15 February
13 Jane 17 February
14 Robert 11 February
15 Ryan 22 March
16 Joanna 19 March
17 Tom 11 March
18 Lucy 10 March
19 Frank 15 March
20 Jane 17 March
21 Robert 15 March
  • Then I need to find the Highest Attendance of the month and 15% less will be the lowest attendance, If any employee fall in this range then that employee will will be the STAR employee of the month.

In this second step I am facing all the problem. I am able to get Highest and Lowest attendance but not able to compare.

Here is my query for calculating this:

SELECT MAX(Attendance) AS [Highest Attendance], 
MAX(Attendance) - ( MAX(Attendance) * .15 ) AS [Lowest Attendance], Month
FROM Employee 
GROUP BY Month;

Can anyone please help me with this? I am using SQL Server 2017.

Here is some dummy data to create the above scenario:

-- create a table
CREATE TABLE Employee (
  Id int PRIMARY KEY,
  Name varchar(100) NOT NULL,
  Attendance int NOT NULL,
  Month varchar(20) NOT NULL
);
-- insert some values
INSERT INTO Employee VALUES (1, 'Ryan', 20, 'January');
INSERT INTO Employee VALUES (2, 'Joanna', 19, 'January');
INSERT INTO Employee VALUES (3, 'Tom', 21, 'January');
INSERT INTO Employee VALUES (4, 'Lucy', 10, 'January');
INSERT INTO Employee VALUES (5, 'Frank', 15, 'January');
INSERT INTO Employee VALUES (6, 'Jane', 17, 'January');
INSERT INTO Employee VALUES (7, 'Robert', 11, 'January');

INSERT INTO Employee VALUES (8, 'Ryan', 18, 'February');
INSERT INTO Employee VALUES (9, 'Joanna', 17, 'February');
INSERT INTO Employee VALUES (10, 'Tom', 20, 'February');
INSERT INTO Employee VALUES (11, 'Lucy', 16, 'February');
INSERT INTO Employee VALUES (12, 'Frank', 15, 'February');
INSERT INTO Employee VALUES (13, 'Jane', 17, 'February');
INSERT INTO Employee VALUES (14, 'Robert', 11, 'February');

INSERT INTO Employee VALUES (15, 'Ryan', 22, 'March');
INSERT INTO Employee VALUES (16, 'Joanna', 19, 'March');
INSERT INTO Employee VALUES (17, 'Tom', 11, 'March');
INSERT INTO Employee VALUES (18, 'Lucy', 10, 'March');
INSERT INTO Employee VALUES (19, 'Frank', 15, 'March');
INSERT INTO Employee VALUES (20, 'Jane', 17, 'March');
INSERT INTO Employee VALUES (21, 'Robert', 15, 'March');

My Expected example output is like:

Name Month Employee Attendance Highest Attendance Lowest Attendance Status
Ryan January 20 21 17.85 STAR EMPLOYEE
sql-server sql
3 Answers

You can achieve the above scenario in many ways but I would like to use CTE. Common table expressions (CTEs) allow you to structure and organize your SQL queries.

WITH calculated_value AS(
    SELECT MAX(Attendance) AS [Highest Attendance], 
    (MAX(Attendance) - ( MAX(Attendance) * .15 )) AS [Lowest Attendance], Month
    FROM Employee 
    group by Month
)
SELECT e.Name, e.Month, e.Attendance as [Employee Attendance], cv.[Highest Attendance],
       cv.[Lowest Attendance], 
       'STAR EMPLOYEE' AS [Status]
FROM calculated_value AS cv
JOIN Employee AS e ON e.Month = cv.Month
WHERE e.Attendance <= cv.[Highest Attendance] AND e.Attendance >= cv.[Lowest Attendance]

N.B: Joining with months I won't prefer

user_101174330

I'm not totally sure what you are after, but if you want to apply a status based on the attendance being greater than a minimum you can do something along the following lines using window functions

with m as (
    select *, Max(attendance) over(partition by [month]) HighestAttendance
    from employee
)
select [Name], [Month], Attendance, 
    HighestAttendance, LowestAttendance,
    case when Attendance > LowestAttendance then 'Star employee' else 'you suck' end Status
from m
cross apply(values(HighestAttendance-(HighestAttendance*0.15)))v(LowestAttendance)
user_153326500

Most calculations can be done in a sub-query.

SELECT 
  [Name]
, [Month]
, Attendance          AS [Employee Attendance]
, AttendanceMax       AS [Highest Attendance]
, AttendanceThreshold AS [Lowest Attendance] 
, CASE 
  WHEN Attendance >= AttendanceThreshold 
  THEN 'STAR EMPLOYEE' 
  END AS Status
FROM
(
  SELECT *
  , [MonthNumber] = CHARINDEX(UPPER(LEFT([Month], 3)),'___JANFEBMARAPRMAYJUNJULAUGSEPOCTNOVDEC') 
  , [AttendanceMax] = MAX(attendance) OVER (PARTITION BY [month])
  , [AttendanceThreshold] = ROUND((1-0.15)*MAX(attendance) OVER (PARTITION BY [month]), 2)
  FROM employee
) q
WHERE Attendance >= AttendanceThreshold
ORDER BY [MonthNumber] ASC, Attendance DESC

Name   | Month    | Employee Attendance | Highest Attendance | Lowest Attendance | Status       
:----- | :------- | ------------------: | -----------------: | ----------------: | :------------
Tom    | January  |                  21 |                 21 |             17.85 | STAR EMPLOYEE
Ryan   | January  |                  20 |                 21 |             17.85 | STAR EMPLOYEE
Joanna | January  |                  19 |                 21 |             17.85 | STAR EMPLOYEE
Tom    | February |                  20 |                 20 |             17.00 | STAR EMPLOYEE
Ryan   | February |                  18 |                 20 |             17.00 | STAR EMPLOYEE
Joanna | February |                  17 |                 20 |             17.00 | STAR EMPLOYEE
Jane   | February |                  17 |                 20 |             17.00 | STAR EMPLOYEE
Ryan   | March    |                  22 |                 22 |             18.70 | STAR EMPLOYEE
Joanna | March    |                  19 |                 22 |             18.70 | STAR EMPLOYEE

Demo on db<>fiddle here

user_40034190
Related