How to get min and max record from each group

Question

0.00/5 (No votes)

See more:

Dummy table

DECLARE @tb TABLE(UserId INT, InOutDateTime DATETIME , AttendanceType CHAR(1))

INSERT INTO @tb VALUES (1,'2017-08-18 08:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-08-18 07:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-08-18 11:00:00.000','O')
INSERT INTO @tb VALUES (1,'2017-08-18 23:00:00.000','I')

INSERT INTO @tb VALUES (1,'2017-09-18 06:00:00.000','I')
INSERT INTO @tb VALUES (1,'2017-09-18 22:00:00.000','O')
INSERT INTO @tb VALUES (1,'2017-09-18 19:00:00.000','I')

INSERT INTO @tb VALUES (3,'2017-08-18 09:00:00.000','I')
INSERT INTO @tb VALUES (3,'2017-08-18 15:00:00.000','O')
INSERT INTO @tb VALUES (3,'2017-08-18 13:00:00.000','O')
INSERT INTO @tb VALUES (3,'2017-08-18 23:00:00.000','I')

select * from @tb

I need minimum InOutDateTime and maximum InOutDateTime for each user for each date. Minimum InOutDateTime should be from where AttendanceType='I' .
Similarly maximum InOutDateTime should be from where AttendanceType='O'

What I have tried:

select UserId, min(InOutDateTime) min_InDateTime, max(InOutDateTime) max_OutDateTime
from @tb group by UserId, CAST(InOutDateTime AS DATE)
ORDER BY UserId, CAST(InOutDateTime AS DATE)

But i am not able to get the results based on AttendanceType

Posted 22-Jan-18 20:51pm

xpertzgurtej

Updated 12-Dec-21 11:05am

v2

Add a Solution

3 solutions

Solution 1

When you GROUP BY, you probably need to use a HAVING clause: Use HAVING and WHERE Clauses in the Same Query | Microsoft Docs[^]

Posted 22-Jan-18 21:31pm

OriginalGriff

Comments

xpertzgurtej 23-Jan-18 3:39am

thanks for your suggestion. I tried but could not find the desired result.

OriginalGriff 23-Jan-18 3:48am

What did you try? What did it give you that you didn't want?

xpertzgurtej 23-Jan-18 3:51am

i tried select UserId,AttendanceType, min(InOutDateTime) min_InDateTime, max(InOutDateTime) max_OutDateTime
from @tb group by UserId, CAST(InOutDateTime AS DATE),AttendanceType
having AttendanceType in ('I','O')
ORDER BY UserId, CAST(InOutDateTime AS DATE). It is giving me 6 rows instead of 3 rows

xpertzgurtej 23-Jan-18 4:10am

DECLARE @tbDate TABLE(Sno INT IDENTITY(1,1), AttendanceDate DATE, USerId INT)

INSERT INTO @tbDate SELECT DISTINCT CAST(InOutDateTime AS DATE),UserId FROM @tb

DECLARE @tbDateCount INT=(SELECT COUNT(*) FROM @tbDate), @RowId INT=1

DECLARE @AttDate DATE,@UId INT,@MaxInOutTime DATETIME,@MinInOutTime DATETIME

DECLARE @FinalResult TABLE(EmployeeId INT, InTime DATETIME, OutTime DATETIME)

WHILE @RowId<=@tbDateCount
BEGIN
SELECT @AttDate=AttendanceDate , @UId=UserId FROM @tbDate WHERE Sno=@RowId

SELECT @MinInOutTime=(SELECT MIN(InOutDateTime) FROM @tb
WHERE CAST(InOutDateTime AS DATE)=@AttDate AND USerId=@UId AND ISNULL(AttendanceType,'')= CASE WHEN AttendanceType IS NOT NULL THEN 'I' ELSE '' END),
@MaxInOutTime=(SELECT MAX(InOutDateTime) FROM @tb
WHERE CAST(InOutDateTime AS DATE)=@AttDate AND USerId=@UId AND ISNULL(AttendanceType,'')= CASE WHEN AttendanceType IS NOT NULL THEN 'O' ELSE '' END)

INSERT INTO @FinalResult SELECT @UId, @MinInOutTime,@MaxInOutTime
SET @RowId=@RowId+1
END

SELECT * FROM @FinalResult

I am able to get the result what i want. But it is not optimized way. It works very slow. I want the result without looping through each record

xpertzgurtej 23-Jan-18 4:35am

Can anybody help

Solution 4

I need to convert the code to vb.net

Posted 12-Dec-21 11:05am

Member 14169712

Comments

CHill60 13-Dec-21 4:09am

Then do so. But stop posting random comments as solutions to other questions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2018-01-23T05:29:00

Try something like this:

SQL

SELECT
    UserId,
    Convert(date, InOutDateTime) As day,
    Min(CASE
        WHEN AttendanceType = 'I' THEN CAST(InOutDateTime As time(0))
        ELSE CAST('23:59:59' As time(0))
    END) As MinInTime,
    Max(CASE
        WHEN AttendanceType = 'O' THEN CAST(InOutDateTime As time(0)) 
    END) As MaxOutTime
FROM
    @tb
GROUP BY
    UserId,
    Convert(date, InOutDateTime)
;

Output:

UserId  day          MinInTime  MaxOutTime
1       2017-08-18   07:00:00   11:00:00
3       2017-08-18   09:00:00   15:00:00
1       2017-09-18   06:00:00   22:00:00