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)
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