Calculate attendance based on another table

  • I have table called logininfo where each employee gets on record if he is present on that day..i need ur help to calculate total working days of the month

    CREATE TABLE logininfo(Emp_code INT,Login_time datetime,Logout_time datetime)

    INSERT INTO logininfo VALUES(469,'2014-02-01 09:00:00','2014-02-01 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-02 09:00:00','2014-02-02 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-03 09:00:00','2014-02-03 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-04 09:00:00','2014-02-04 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-06 09:00:00','2014-02-06 17:50:00')

    Suppose we have to find the workingdays of the employee from 2014-02-01 to 2014-02-06 output is 5 since there is no record on 2014-02-05 so he is consider has a holiday

  • ganapathy.arvindan (6/17/2016)


    I have table called logininfo where each employee gets on record if he is present on that day..i need ur help to calculate total working days of the month

    CREATE TABLE logininfo(Emp_code INT,Login_time datetime,Logout_time datetime)

    INSERT INTO logininfo VALUES(469,'2014-02-01 09:00:00','2014-02-01 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-02 09:00:00','2014-02-02 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-03 09:00:00','2014-02-03 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-04 09:00:00','2014-02-04 17:50:00')

    INSERT INTO logininfo VALUES(469,'2014-02-06 09:00:00','2014-02-06 17:50:00')

    Suppose we have to find the workingdays of the employee from 2014-02-01 to 2014-02-06 output is 5 since there is no record on 2014-02-05 so he is consider has a holiday

    SELECT

    Emp_code,

    MONTH(Login_time),

    [DaysLogged] = COUNT(*)

    FROM logininfo

    GROUP BY Emp_code, MONTH(Login_time)

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply