ItHurtsWhenIThink
Technical User
Hoping someone can help.
I am trying to create a record set with a list of hours worked. The results should produce a list sorted by last name, first name and total hours worked (by work code) so, I could see same name twice (or more) if they worked say overtime and ExtraDuty.
Tables (with columns used)
Staffing
-SID
-SDate
StaffApps
-SAID
-SID
StaffPersonnel
-SPID
-SAID
-WorkCode
-UserID
-WorkTime
Users
-UserID
-LName
-FName
StaffWorkCodes
-SWCID
-WorkName
Here is a partial recordset:
Abell Kevin 24.000000 OD
Abell Kevin 24.000000 ED
Abell Kevin 24.000000 OD
Abell Kevin 24.000000 OD
Allum Simona 24.000000 OD
Should look like:
Abell Kevin 72.000000 OD
Abell Kevin 24.000000 ED
Allum Simona 24.000000 OD
Here is the statement that I have. Issue is it does not group by User LName and WorkName.
Select Users.LName, Users.FName, Sum((StaffPersonnel.WorkTime / 60)) As
WrkHours, StaffWorkCodes.WorkName
From StaffPersonnel Inner Join
Users On StaffPersonnel.UserID = Users.UserID Inner Join
StaffWorkCodes On StaffPersonnel.WorkCode = StaffWorkCodes.SWCID Left Join
StaffApps On StaffApps.SAID = StaffPersonnel.SAID Inner Join
Staffing On Staffing.SID = StaffApps.SID
Group By Users.LName, Users.FName, StaffWorkCodes.WorkName,
StaffWorkCodes.PayrollItem, Staffing.SDate, StaffPersonnel.WorkTime
Having StaffWorkCodes.PayrollItem = 'Yes' And
Staffing.SDate Between Cast('1/1/2013' As SMALLDATETIME) And Cast('1/31/2014'
As SMALLDATETIME)
Order By Users.LName
Thanks...
I am trying to create a record set with a list of hours worked. The results should produce a list sorted by last name, first name and total hours worked (by work code) so, I could see same name twice (or more) if they worked say overtime and ExtraDuty.
Tables (with columns used)
Staffing
-SID
-SDate
StaffApps
-SAID
-SID
StaffPersonnel
-SPID
-SAID
-WorkCode
-UserID
-WorkTime
Users
-UserID
-LName
-FName
StaffWorkCodes
-SWCID
-WorkName
Here is a partial recordset:
Abell Kevin 24.000000 OD
Abell Kevin 24.000000 ED
Abell Kevin 24.000000 OD
Abell Kevin 24.000000 OD
Allum Simona 24.000000 OD
Should look like:
Abell Kevin 72.000000 OD
Abell Kevin 24.000000 ED
Allum Simona 24.000000 OD
Here is the statement that I have. Issue is it does not group by User LName and WorkName.
Select Users.LName, Users.FName, Sum((StaffPersonnel.WorkTime / 60)) As
WrkHours, StaffWorkCodes.WorkName
From StaffPersonnel Inner Join
Users On StaffPersonnel.UserID = Users.UserID Inner Join
StaffWorkCodes On StaffPersonnel.WorkCode = StaffWorkCodes.SWCID Left Join
StaffApps On StaffApps.SAID = StaffPersonnel.SAID Inner Join
Staffing On Staffing.SID = StaffApps.SID
Group By Users.LName, Users.FName, StaffWorkCodes.WorkName,
StaffWorkCodes.PayrollItem, Staffing.SDate, StaffPersonnel.WorkTime
Having StaffWorkCodes.PayrollItem = 'Yes' And
Staffing.SDate Between Cast('1/1/2013' As SMALLDATETIME) And Cast('1/31/2014'
As SMALLDATETIME)
Order By Users.LName
Thanks...