to find maximum value and minimum value from two different record only

Question

I am trying to find the maximum value and minimum value from two rows of a database
I have the following table
create table #EmpTime (EmpCode varchar(max), AttDate DateTime)
Insert into #EmpTime
values
('01', '2020-05-30 08:06:58.000'),
('02', '2020-05-30 08:06:58.000'),
('03','2020-05-30 10:11:53.000'),
('04',2020-05-30 08:17:23.000),
('01', '2020-05-30 18:06:58.000'),
('02', '2020-05-30 18:06:58.000'),
('03','2020-05-30 17:11:53.000'),
('03','2020-05-30 18:11:53.000'),
('02', '2020-05-30 18:10:58.000'),
('02', '2020-05-30 20:10:58.000'),

from the above example I would get
01 - 2020-05-30 08:06:58.000, 2020-05-30 18:06:58.000
02 - 2020-05-30 08:06:58.000, 2020-05-30 20:10:58.000
03 - 2020-05-30 10:11:53.000, 2020-05-30 18:11:53.000
04 - 2020-05-30 08:17:23.000, Null

I am trying to get maximum and minimum AttDate. Currently I am using the following sql
SELECT
MIN(CASE WHEN Status = 'IN' THEN AttDate END),
MAX(CASE WHEN Status = 'OUT' THEN AttDate END)
from
(SELECT
EmpCode,
AttDate ,
CASE ROW_NUMBER() OVER(PARTITION BY EmpCode ORDER BY id) % 2
WHEN 0 THEN 'OUT'
ELSE 'IN'
END AS Status
FROM
EsslLogs
Where EmpCode= @EmpCode and CONVERT(date, AttDate ) = @AttDate
) t;

In my current example , it only takes if the row is Even rows against the employee. I want to re-write the sql , to consider max and min from two different rows without considering even rows, Please help

Thanks
Pol

Answer 1

Hi @Polachan Paily ,

Welcome to Microsoft Q&A!

Please refer below and check whether it is working.

If not, please provide more details about the rule and expected output.

SELECT EmpCode, min(attdate) MIN  
,case when count(AttDate)=1 then NULL else  max(AttDate) end MAX  
FROM #EmpTime  
group by EmpCode  
order by EmpCode  

Output:

EmpCode MIN MAX  
01 2020-05-30 08:06:58.000 2020-05-30 18:06:58.000  
02 2020-05-30 08:06:58.000 2020-05-30 20:10:58.000  
03 2020-05-30 10:11:53.000 2020-05-30 18:11:53.000  
04 2020-05-30 08:17:23.000 NULL  

Best regards
Melissa

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

Hi @Polachan Paily ,

Please refer to:

create table #EmpTime (EmpCode varchar(max), AttDate DateTime)  
Insert into #EmpTime  
values  
('01', '2020-05-30 08:06:58.000'),  
('02', '2020-05-30 08:06:58.000'),  
('03','2020-05-30 10:11:53.000'),  
('04','2020-05-30 08:17:23.000'),  
('01', '2020-05-30 18:06:58.000'),  
('02', '2020-05-30 18:06:58.000'),  
('03','2020-05-30 17:11:53.000'),  
('03','2020-05-30 18:11:53.000'),  
('02', '2020-05-30 18:10:58.000'),  
('02', '2020-05-30 20:10:58.000')  
  
;WITH cte  
as(SELECT EmpCode,MIN(AttDate) MINAttDate,MAX(AttDate) MAXAttDate   
FROM #EmpTime  
GROUP BY EmpCode)  
  
SELECT EmpCode,MINAttDate,  
IIF(MINAttDate<>MAXAttDate,MAXAttDate,NULL)MAXAttDate  
FROM cte  

Output:

If you have any question, please feel free to let me know.

Regards
Echo

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.