Forum Discussion
emadadel2008
Jul 10, 2020Brass Contributor
Check in and check out time in SQL
My table is:
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:
No RepliesBe the first to reply