Check in and check out time in SQL

Occasional Contributor

My table is:

sOOXJ

SELECT
TOP (1000) [ID] ,[UserName] ,[CheckTime] ,[Checktype] ,[CheckinLocation] ,[lat] ,[lng] FROM [dbo].[CheckTime] INSERT INTO [dbo].[CheckTime] ([UserName], [CheckTime], [Checktype],[CheckinLocation], [lat], [lng]) VALUES (<UserName, nchar(10),> ,<CheckTime, datetime,> ,<Checktype, nvarchar(50),> ,<CheckinLocation, nvarchar(50),> ,<lat, float,> ,<lng, float,>)GO

Create table script:

CREATE TABLE [dbo].[CheckTime]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [UserName] [nchar](10) NULL,
    [CheckTime] [datetime] NULL,
    [Checktype] [nvarchar](50) NULL,
    [CheckinLocation] [nvarchar](50) NULL,
    [lat] [float] NULL,
    [lng] [float] NULL,

    CONSTRAINT [PK_CheckTime] 
        PRIMARY KEY CLUSTERED ([ID] ASC)
                    WITH (STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

I need to select each distinct home holding the maximum value of datetime.

  • max CheckTime as check out
  • min CheckTime as check in

I need a result like this:

id | Username | check in          | check out---+----------+-------------------+-------------------
1  |   10     | 2017-1-2 08:02:05 | 2017-1-2 10:02:05
1  |   12     | 2017-1-2 08:02:05 | 2017-1-2 10:02:05
1  |   12     | 2017-1-3 08:02:05 | 2017-1-3 10:02:05
1  |   10     | 2017-1-3 08:02:05 | 2017-1-3 10:02:05

I have tried:

0 Replies