Calculate attendance based on another table

• GA_SQL

  Hall of Fame

  Points: 3166

  More actions

  June 17, 2016 at 6:58 am

  #327643

  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

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  June 17, 2016 at 7:06 am

  #1885589

  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