CODEWITHSUNDEEP
sqlsql-server
shanks
shanks

Reputation:

Finding Max and Min Times

I want to display max time and min time for a day in grid control using Visual Basic from a SQL Server database. My data currently looks like this:

UserID    UserName    Date        Time 
------------------------------------------
1         Shanks      30/1/2009   10:11:22 
1         Shanks      30/1/2009   10:15:22 
1         Shanks      30/1/2009   12:15:22
1         Shanks      30/1/2009   13:15:22

I need the output like this:

1         Shanks      30/1/2009   10:11:22    13:15:22

My table structure is:

UserID integer, 
UserName varchar(20), 
[Date] datetime, 
[Time] datetime

How can I get that output from my data?

Upvotes: 0

Views: 2905

Answers (2)

Russ Cam
Russ Cam

Reputation: 125488

SELECT 
    UserID,
    UserName,
    [Date],
    MIN([Time]),
    MAX([Time])
FROM
    Table
GROUP BY
    UserID,
    UserName,
    [Date]

Tested and working correctly with the following

DECLARE @Table TABLE (UserID INT,UserName VARCHAR(25),[Date] DATETIME,[Time] DATETIME)

INSERT INTO @Table VALUES(1, 'Shanks', '30 JAN 2009', '10:11:22');
INSERT INTO @Table VALUES(1, 'Shanks', '30 JAN 2009', '10:15:22');
INSERT INTO @Table VALUES(1, 'Shanks', '30 JAN 2009', '12:15:22');
INSERT INTO @Table VALUES(1, 'Shanks', '30 JAN 2009', '13:15:22');
INSERT INTO @Table VALUES(2, 'Shilpa', '3 JAN 2009', '10:11:22');
INSERT INTO @Table VALUES(2, 'Shilpa', '3 JAN 2009', '11:15:22');
INSERT INTO @Table VALUES(2, 'Shilpa', '3 JAN 2009', '12:15:22');
INSERT INTO @Table VALUES(2, 'Shilpa', '3 JAN 2009', '17:15:22');


SELECT 
    UserID,
    UserName,
    [Date],
    MIN([Time]),
    MAX([Time])
FROM
    @Table
GROUP BY
    UserID,
    UserName,
    [Date]

Results in

UserID      UserName                  Date                                            
----------- ------------------------- ----------------------- ----------------------- -----------------------
1           Shanks                    2009-01-30 00:00:00.000 1900-01-01 10:11:22.000 1900-01-01 13:15:22.000
2           Shilpa                    2009-01-03 00:00:00.000 1900-01-01 10:11:22.000 1900-01-01 17:15:22.000

Upvotes: 2

Max Schmeling
Max Schmeling

Reputation: 12509

Is this what you're looking for?

  SELECT UserID,
         UserName,
         Date,
         MIN([Time]) AS MinTime,
         MAX([Time]) AS MaxTime,
    FROM Users
GROUP BY UserID, UserName, [Date]

Upvotes: 2

Related Questions