28 Apr 2016 22:49

Joined:	05/11/2013
Level:	Gold
Points:	1551

Points:	5

How to find the first and last time of the day

Hi All,

Need one help. I have some record like below for around 100 members on daily basis-

Date & Time Reader User ID
3/14/2016 6:00 AM In 123
3/14/2016 7:00 AM out 123
3/14/2016 8:00 AM In 123
3/14/2016 9:00 AM out 123
3/14/2016 10:00 AM In 123
3/14/2016 11:00 AM out 123
3/14/2016 12:00 PM In 123
3/14/2016 1:00 PM out 123
3/14/2016 2:00 PM In 123
3/14/2016 3:00 PM out 123

Data is in Excel but can be put in SQL. Can somebody tell me how I can find the first In time, Last out time and the time person was In on daily basis.

Any help will be appreciated.

#765735

29 Apr 2016 03:47

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	4

Hi,
Try this:
create table #Dates (UserID numeric (18,0), Reader varchar (20), Date_Time datetime )
Insert into #Dates values (123, 'In', '2015-08-24 06:00:00.000')
Insert into #Dates values (123, 'Out', '2015-08-24 07:00:00.000')
Insert into #Dates values (123, 'In', '2015-08-24 08:00:00.000')
Insert into #Dates values (123, 'Out', '2015-08-24 09:00:00.000')
Insert into #Dates values (123, 'In', '2015-08-24 10:00:00.000')
Insert into #Dates values (123, 'Out', '2015-08-24 11:00:00.000')
Insert into #Dates values (124, 'In', '2015-08-24 06:15:00.000')
Insert into #Dates values (124, 'Out', '2015-08-24 07:30:00.000')
Insert into #Dates values (124, 'In', '2015-08-24 08:00:00.000')
Insert into #Dates values (124, 'Out', '2015-08-24 09:30:00.000')
Insert into #Dates values (124, 'In', '2015-08-24 10:00:00.000')
Insert into #Dates values (124, 'Out', '2015-08-24 11:30:00.000')
select * from #Dates
SELECT UserID, COUNT(*) as Total_In_Outs , MIN(Date_Time) as FirstIn, MAX(Date_Time) LastOut
FROM #Dates
GROUP BY UserID , CAST([Date_Time] AS DATE)

#765822

05 May 2016 12:44

Joined:	05/11/2013
Level:	Gold
Points:	1551

Points:	1

Hello Sashi,

Thanks for your reply.
SELECT UserID,
COUNT(*) as Total_In_Outs , MIN(Date_Time) as FirstIn,
MAX(Date_Time) as LastOut FROM sample2 GROUP BY UserID

Worked but what if I have daily data like I want the data of daily. Like in out of 24th, 25th etc. separately..Here is the input

create table sample2 (UserID numeric (18,0), Reader varchar (20), Date_Time datetime );
Insert into sample2 values (123, 'In', '2015-08-24 06:00:00.000');
Insert into sample2 values (123, 'Out', '2015-08-24 07:00:00.000');
Insert into sample2 values (123, 'In', '2015-08-24 08:00:00.000');
Insert into sample2 values (123, 'Out', '2015-08-24 09:00:00.000');
Insert into sample2 values (123, 'In', '2015-08-24 10:00:00.000');
Insert into sample2 values (123, 'Out', '2015-08-24 11:00:00.000');

Insert into sample2 values (123, 'In', '2015-08-25 7:00:00.000');
Insert into sample2 values (123, 'Out', '2015-08-25 8:00:00.000');
Insert into sample2 values (123, 'In', '2015-08-25 08:15:00.000');
Insert into sample2 values (123, 'Out', '2015-08-25 11:00:00.000');

Insert into sample2 values (124, 'In', '2015-08-24 06:15:00.000');
Insert into sample2 values (124, 'Out', '2015-08-24 07:30:00.000');
Insert into sample2 values (124, 'In', '2015-08-24 08:00:00.000');
Insert into sample2 values (124, 'Out', '2015-08-24 09:30:00.000');
Insert into sample2 values (124, 'In', '2015-08-24 10:00:00.000');
Insert into sample2 values (124, 'Out', '2015-08-24 11:30:00.000');

Here for 123, I have entry for 24th and 25th and so need the data for both the dates. Please help.

Thanks,
Ashutosh Jha
http://tricksroad.com

#765824

05 May 2016 23:28

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	2

it is pretty simple, just use MIN and MAX inbuilt functions for it, see below snippet


SELECT MIN(Date & Time), MAX(Date & Time), COUNT(*)
FROM table1
GROUP BY User ID, date

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

#765857

09 May 2016 06:15

Joined:	05/11/2013
Level:	Gold
Points:	1551

Points:	1

Hi Prasad,

It's not like that. Please check my comment above your answer and please help!

Thanks,
Ashutosh Jha
http://tricksroad.com

#765898

12 May 2016 02:23

Joined:	15/12/2015
Level:	Gold
Points:	1308

Points:	4

Hi Ashutosh,
1. Add these line in my above script:
Insert into #Dates values (123, 'In', '2015-08-25 7:00:00.000');
Insert into #Dates values (123, 'Out', '2015-08-25 8:00:00.000');
Insert into #Dates values (123, 'In', '2015-08-25 08:15:00.000');
Insert into #Dates values (123, 'Out', '2015-08-25 11:00:00.000');
Insert into #Dates values (124, 'In', '2015-08-25 11:00:00.000')
Insert into #Dates values (124, 'Out', '2015-08-25 12:30:00.000')
2. Now try running my "whole" script:

SELECT UserID, COUNT(*) as Total_In_Outs , MIN(Date_Time) as FirstIn, MAX(Date_Time) LastOut
FROM #Dates
GROUP BY UserID , CAST([Date_Time] AS DATE)

here at last we have used DATE in Group BY clause, so it will automatically retrieves daily data i.e. data for both the dates.

#766547

20 Jun 2016 03:10

Joined:	12/12/2013
Level:	Gold
Points:	529

Points:	4

Hi,

Kindly use the Following Query,

create table #InOutDates (UserID varchar(100), Date_Time datetime )
Insert into #InOutDates values ('E101','2015-08-24 06:00:00.000'),
('E101', '2015-08-24 07:00:00.000'),
('E101', '2015-08-24 08:00:00.000'),
('E101', '2015-08-24 09:00:00.000'),
('E101', '2015-08-24 10:00:00.000'),
('E101', '2015-08-24 11:00:00.000'),
('E102', '2015-08-24 06:15:00.000'),
('E102', '2015-08-24 07:30:00.000'),
('E102', '2015-08-24 08:00:00.000'),
('E102', '2015-08-24 09:30:00.000'),
('E102', '2015-08-24 10:00:00.000'),
('E102', '2015-08-24 11:30:00.000')

SELECT UserID ,MIN(Date_Time) as FirstIn, MAX(Date_Time) LastOut
FROM #InOutDates
GROUP BY UserID , CAST([Date_Time] AS DATE)
Order By UserId

Regards,
Karunanidhi.K

#766555

20 Jun 2016 05:24

Joined:	30/03/2016
Level:	Bronze
Points:	58

Points:	2

Hi,
Use this below Query to get your expected result,<pre>
select Userid,convert(date,date_time) as date ,min(date_time) as login_time,max(date_time) as logout_time
from sample2
group by userid,convert(date,date_time)</pre>

#766596

21 Jun 2016 02:05

Joined:	05/06/2009
Level:	Gold
Points:	10245

Points:	3

You can add try the following query. The bellow sub query may solve your issue


Select  UserID,  Total_In_Outs ,  FirstIn,  LastOut,  CurrentDate
from(
SELECT UserID,  COUNT(*) as Total_In_Outs , MIN(Date_Time) as FirstIn, 
MAX(Date_Time) as LastOut,  Date_Time as CurrentDate FROM sample2 GROUP BY UserID) as aa group by CurrentDate

By Nathan
Direction is important than speed

#766622

21 Jun 2016 21:13

Joined:	13/06/2007
Level:	Gold
Points:	21327

Points:	3

use some sql inbuilt functions like MIN and MAX to fetch minimum and maximum value and use GROUP BY clause to make it person wise entries
see below snippet


SELECT UserID, COUNT(*) as Total_In_Outs , MIN(Date_Time) as FirstIn, MAX(Date_Time) LastOut
FROM #Dates
GROUP BY UserID , CAST([Date_Time] AS DATE)

but before firing above query you need to insert all records from Excel to database

Thanks
Koolprasd2003
Editor, DotNetSpider MVM
Microsoft MVP 2014 [ASP.NET/IIS]

How to find the first and last time of the day

About dotnetspider.com

Quick Links

General

Services