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

Polachan Paily 221 Reputation points
2021-02-04T07:38:14.44+00:00

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

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,565 questions
0 comments No comments
{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,176 Reputation points
    2021-02-04T09:31:48.007+00:00

    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.

    0 comments No comments

1 additional answer

Sort by: Most helpful
  1. EchoLiu-MSFT 14,571 Reputation points
    2021-02-04T09:35:36.297+00:00

    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:
    63993-image.png

    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.

    0 comments No comments