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.

• 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:

CodePudding user response：

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

CodePudding user response：

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)

CodePudding user response：

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