You must Sign In to post a response.
  • Category: General

    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
    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
    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
    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
    Hi Prasad,

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

    Thanks,
    Ashutosh Jha
    http://tricksroad.com

  • #765898
    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
    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
    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
    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
    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]


Sign In to post your comments