March 6, 2015 at 7:58 am
Hi All,
I am sure such queries would not be complicated for you guys and available all over the internet. But unfortunately i failed to find one such for my requirement. Need all of yours assistance in building this query.
Table Name: EmployeeDetails
Columns: EMpID - Date - WorkedHours
For each day I get details of number of hours worked by each employee in this table.
Now my HR wants a report with such columns
empid - Week - Month - Qtr
So, week will have Sum of hours worked by employee in that week
Month will have Sum of hours worked by employee in that Month
Qtr will have Sum of hours worked by employee in that Qtr
How can i get this output?
Thanks in advance
March 6, 2015 at 8:18 am
This may not be the most efficient but should work...
CREATE TABLE EmployeeDetails (empid int, workdate date, workedhours int)
SELECT
empid,
[Week] = (SELECT SUM(workedhours) FROM EmployeeDetails w WHERE w.empid = e.empid and DATEPART(WW, workdate) = DATEPART(WW, GETDATE())),
[Month] = (SELECT SUM(workedhours) FROM EmployeeDetails w WHERE w.empid = e.empid and DATEPART(mm, workdate) = DATEPART(MM, GETDATE())),
[Qtr] = (SELECT SUM(workedhours) FROM EmployeeDetails w WHERE w.empid = e.empid and DATEPART(mm, workdate) = DATEPART(QUARTER, GETDATE()))
FROM EmployeeDetails e
March 6, 2015 at 8:51 am
Superbly worked
Just one small change the QTR query will have QQ in datepart function. Right?
SELECT
empid,
[Week] = (SELECT SUM(workedhours) FROM EmployeeDetails w WHERE w.empid = e.empid and DATEPART(WW, workdate) = DATEPART(WW, GETDATE())),
[Month] = (SELECT SUM(workedhours) FROM EmployeeDetails w WHERE w.empid = e.empid and DATEPART(mm, workdate) = DATEPART(MM, GETDATE())),
[Qtr] = (SELECT SUM(workedhours) FROM EmployeeDetails w WHERE w.empid = e.empid and DATEPART(QQ, workdate) = DATEPART(QUARTER, GETDATE()))
FROM EmployeeDetails e
March 6, 2015 at 9:07 am
.
March 6, 2015 at 9:09 am
Well spotted. Thanks.
March 7, 2015 at 3:21 am
roger.price-1150775 (3/6/2015)
Well spotted. Thanks.
Just one small thing... I also have a EmployeeMaster table which has EMPID, EMP_name, DeptID in the table.
When I pass em.DeptID=1 in where clause at the end. I get repeated data... Why is that?
SELECT
em.empid,em.Emp_Name,
[Week] = (SELECT SUM(OTHours) FROM tblOTDetails w WHERE w.empid = e.empid and DATEPART(WW, otdate) = DATEPART(WW, GETDATE())),
[Month] = (SELECT SUM(OTHours) FROM tblOTDetails w WHERE w.empid = e.empid and DATEPART(mm, otdate) = DATEPART(MM, GETDATE())),
[Qtr] = (SELECT SUM(OTHours) FROM tblOTDetails w WHERE w.empid = e.empid and DATEPART(QQ, otdate) = DATEPART(QUARTER, GETDATE()))
FROM tblOTDetails e,tblEmployeeMaster em
were em.deptID=1
March 9, 2015 at 1:02 pm
SELECT
em.empid,em.Emp_Name,
[Week] = (SELECT SUM(OTHours) FROM tblOTDetails w WHERE w.empid = e.empid and DATEPART(WW, otdate) = DATEPART(WW, GETDATE())),
[Month] = (SELECT SUM(OTHours) FROM tblOTDetails w WHERE w.empid = e.empid and DATEPART(mm, otdate) = DATEPART(MM, GETDATE())),
[Qtr] = (SELECT SUM(OTHours) FROM tblOTDetails w WHERE w.empid = e.empid and DATEPART(QQ, otdate) = DATEPART(QUARTER, GETDATE()))
FROM tblOTDetails e,tblEmployeeMaster em
were em.deptID=1
You are implicitly JOINing your two tables but you aren't defining the criteria for the JOIN. I would suspect both of your tables migh have a DeptID field in them. That would be poor design in my view. I would think that DeptID should be in the EmployeeMaster table only.
Either way, try this in your FROM clause:
FROM tblOTDetails e
INNER JOIN tblEmployeeMaster em ON e.EmployeeID = em.EmployeeID
This should give you one OT record for each Employee
Sigerson
"No pressure, no diamonds." - Thomas Carlyle
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply