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

    How to create daily attendance report in Login Time, Logout Time, Total Hours, Total logs

    Iam Create table name tblattendance

    Create 3 coulmns:
    OrgId
    UserId
    DateOfTransaction

    inserted data :
    orgid useid DateOfTransaction
    1 1 06-02-2016:08:12
    1 2 06-02-2016:08:20
    1 3 06-02-2016:08:30
    1 1 06-02-2016:15:43
    1 1 06-02-2016:15:45
    1 2 06-02-2016:15:43

    evng time only 1 and 3 userids only punch but not punch userid 2

    i want to show daily attendance out

    Userid Date Login Time Logout Time Total Hours Total Logs
    1 06-02-2016 08:12 15:45 7 hours 12 mins 3
    2 06-02-2016 08:20 15:43 7 hours 10 mins 2
    3 06-02-2016 08:30 - - 1
  • #764469

    While storing Date-Time in database use data type as DateTime and then you can calculate difference between first entry as IN-TIME and last entry of the id is OUT-TIME

    First you need to get both INTIME and OUTTIME, to check it, you need to ensure that there should be at least two entries of same id
    first check who is not put TIME-OUT with below query
    select Userid, count(*)
    from YourTable
    Group by Userid

    Now get INTime and OUTTime , means first and last entry of each user ID
    To get first entry use below query
    SELECT TOP 1 uid FROM table1 where uid = '1'
    ORDER BY DateOfTransaction

    To get Last entry use below query
    SELECT TOP 1 uid FROM table1 where uid = '1'
    ORDER BY DateOfTransaction DESC

    finally use below query to get difference, here is sample
    -- Declare the Start and End date
    DECLARE @SDATE AS DATETIME
    TART_DATE AS DATETIME
    DECLARE @END_-- Set Start and End date
    SET @START_DATE = GETDATE()
    SET @END_DATE = DATEADD(SECOND, 3910, GETDATE())
    -- Get the Result in HH:MI:SS:MMM(24H) format
    SELECT CONVERT(VARCHAR(12), DATEADD(MS, DATEDIFF(MS, @START_DATE, @END_DATE), 0), 114) AS TimeDiff
    -- Result in HH:MI:SS:MMM(24H) format
    -------------
    TimeDiff
    -------------
    01:05:10:000

    Hope it helps


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

  • #764523
    Hi,

    Create 2 fields in your database table like Intime and Outtime and while enter into premises update Intime and while went out from premises then update outtime and log count, again 2nd time went out, again update outtime and increment the log count. Finally last outtime of the day time and first Intime date subtract both and check the difference between 2 times that will give the total hours and for logs already you are update the log entry for each and every outtime.

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/

  • #764559
    hi naveen sir
    iam using biomatric attendance .
    only 1 field is required sir..

  • #764560
    thanq sir...

    perfect working using group by ...

    Select userid,
    Min(DateOfTransaction) as InTime,
    max(DateOfTransaction) as OutTime,
    convert(varchar(8),(convert(datetime,MAX(DateOfTransaction),110) - convert(datetime,MIN(DateOfTransaction),110)),108) AS Duration
    from tblattendance
    group by userid

  • #764591
    Hi Prabhakar,

    Nice to hear to resolve the issue..

    --------------------------------------------------------------------------------
    Give respect to your work, Instead of trying to impress your boss.

    N@veen
    Blog : http://naveens-dotnet.blogspot.in/


Sign In to post your comments