Grouping Issue 2

[ItHurtsWhenIThink]

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

 

[trevil620]

I do not have SQL Server at the moment, but here is the MS-Access version of what you want.. I think..
The following resulted in the three records you expected...
Just change the Date selection, plus PayrollItem = Yes??? (not sure if you used a boolean field)

SELECT USERS.LNAME, USERS.FNAME, Sum([WORKTIME]/60) AS WrkHours, StaffWorkCodes.WORKNAME
FROM StaffApps INNER JOIN staffing ON StaffApps.SID = staffing.SID, USERS INNER JOIN (StaffWorkCodes INNER JOIN StaffPersonnel ON StaffWorkCodes.SWCID = StaffPersonnel.WORKCODE) ON USERS.USERID = StaffPersonnel.USERID
WHERE (((staffing.SDAte) Between #1/1/2013# And #1/31/2014#))
GROUP BY USERS.LNAME, USERS.FNAME, StaffWorkCodes.WORKNAME, StaffWorkCodes.PayrollItem
HAVING (((StaffWorkCodes.PayrollItem)=True))
ORDER BY USERS.LNAME;

Good Luck,
Wayne

 

[ItHurtsWhenIThink]

OK, I see where I went wrong. Should have used UserID in query.

Here is the result I got:

Abell Kevin 4512.000000 OD
Allum Simona 1128.000000 OD

What I need is the ability to group by Last name and WorkName

so result would break hour by user and workName... like

Abell Kevin 72.000000 OD
Abell Kevin 24.000000 ED
Allum Simona 24.000000 OD

Also, not sure what happened to WrkHours value. Looks like it did not divide sum by 60. So, looks like I still have the minutes value.

Great work though. Very close.

Thanks.

 

[trevil620]

I have no idea what your table relationships / keys are, so I was forced to guess.
As for the 'hours not divided by 60'... can't help there because it worked for me -- the reason I got the 'correct' answers was because I had to multiply 24 * 60 and place that value (720) in the table. What is the content of 'WORKTIME' in your data? I am beginning to believe it must be minutes and not hours. I have no idea what the significance of your '60' is?
Send your 'corrected' SQL -- as you can see I am also grouping on WORKNAME, so when I run my SQL, I get the proper 3 rows.

 

[ItHurtsWhenIThink]

Staffing //this table collect data for each day of staffing
-SID - PK
-SDate
-shiftID
-DeptID

StaffApps //this table allows me to collect daily staffing based on units (where people are assigned)
-SAID - PK
-SID
-UnitID

StaffPersonnel //this table allows me to dump personnel onto each unit (SAID)
-SPID - PK
-SAID
-WorkCode
-UserID
-WorkTime

Users
-UserID - PK
-LName
-FName

WrkHours is minutes worked. So I divide by 60 to get my hours.

 

[trevil620]

I'm still trying to help you out, but there is way too much guessing on my part. Please understabd the following:
1. I can try to 'guess' the table relationships based on the original SQL you posted, but it would be nice to know:
(a) have you defined relationships between the FIVE tables you originally mentioned?
(b) based on field names I can ASSUME the keys involved
2. Could you post the VALUES for the FIVE tables (only need the info for the TWO people) so that I can duplicate?
3. Can you post your LATEST SQL? Earlier today you posted "OK, I see where I went wrong. Should have used UserID in query." and I asked for the latest SQL.
I have now deleted all the data I had because I had made incorrect assumptions on keys and relationships.

Thank you.

 

[trevil620]

My SQL produces the following:
LNAME FNAME WrkHours WORKNAME
Abell Kevin 24 ED
Abell Kevin 72 OD
Allum Simona 24 ED

 

[ItHurtsWhenIThink]

I hope this helps. I have exported the data from SQL to an MS Access DB.

https://www.dropbox.com/s/oyz7ba1caxf0teg/SampleData.accdb

 

[trevil620]

Hello, I used the database you posted, used the SQL (see below) and got the results shown below. A couple of points:
1. Before I got your Database, I assumed 'PayrollItem" was a True/False field, ran my query and got zero records (or just the difference between SQL Server and Jet DB.
2. I simplified the query, removed the filter and saw it was a 'Yes/No' field (but all the data you sent has a value of 'Yes'), and got records returned.
3. To determine if hours were correct, I filtered on one name (ALLUM), got details for all records (47) and found every record had 1440 in the "WorkTime" field (which earlier you said was stored in minutes, so 1440 translates to 24 hours).
4. 47 * 1440 / 60 = 1128 therefore the hours calculation is correct.
5. Repeated for "Abell": 188 records * 1440 / 60 = 4512
6. FYI - the data had NO "ED" records for Abell or Allum
6. Below are the first 5 names returned.
LNAME FNAME WrkHours WORKNAME
Abell Kevin 4512 OD
Allum Simona 1128 OD
ANDERSON Robert 1128 OD
ARNOLD Azucena 1128 OT
AVILA Adelina 1128 OD

SELECT USERS.LNAME, USERS.FNAME, Sum([WORKTIME]/60) AS WrkHours, StaffWorkCodes.WORKNAME
FROM StaffApps INNER JOIN staffing ON StaffApps.SID = staffing.SID, USERS INNER JOIN (StaffWorkCodes INNER JOIN StaffPersonnel ON StaffWorkCodes.SWCID = StaffPersonnel.WORKCODE) ON USERS.USERID = StaffPersonnel.USERID
WHERE (((staffing.SDAte) Between #1/1/2013# And #1/31/2014#))
GROUP BY USERS.LNAME, USERS.FNAME, StaffWorkCodes.WORKNAME, StaffWorkCodes.PayrollItem
HAVING (((StaffWorkCodes.PayrollItem)="Yes"))
ORDER BY USERS.LNAME;

Hope the above helps! Wayne

 

[gmmastros]

I was just looking at your original SQL and have a couple of comments.

Also, not sure what happened to WrkHours value. Looks like it did not divide sum by 60.

This is one of those places where SQL Server and Access function differently. If all values in an equation are integers, you will get integer math. For example:

Select 30/60

Anyone with the ability to perform 2nd grade math knows that the answer is 0.5. Unfortunately for you, SQL looks at the 30 and thinks (integer). Then looks at the 60 and thinks (integer). Since everything is an integer, you get integer math. The simplest way to accommodate this is to force it to use fractional math, like this:

Select 30 / 60[!].0[/!]

This time SQL sees the 60.0 and thinks fractional math so you will get the correct answer.

Next, I noticed this:

[!]Having[/!] StaffWorkCodes.PayrollItem = 'Yes' And
Staffing.SDate Between Cast('1/1/2013' As SMALLDATETIME) And Cast('1/31/2014'
As SMALLDATETIME)

The having clause is usually used to filter on aggregate values. For example:

Having Sum(ColumnName) > 40

Unless you are using aggregates (like sum, count, avg, min, max, etc...) you should put your filter criteria in a where clause, like this:

Select	Users.LName, 
		Users.FName, 
		Sum((StaffPersonnel.WorkTime / 60.0)) 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
[!]Where[/!]   StaffWorkCodes.PayrollItem = 'Yes' 
		And Staffing.SDate Between Cast('1/1/2013' As SMALLDATETIME) 
							And Cast('1/31/2014' As SMALLDATETIME)
Group By Users.LName, 
		Users.FName, 
		StaffWorkCodes.WorkName,
		StaffPersonnel.WorkTime

Order By Users.LName

Note that the where clause comes before the group by clause in the query.

Here is the statement that I have. Issue is it does not group by User LName and WorkName.

You're right, it is grouping by everything you have in the group by clause. Your original group by query is this:

[tt]
Group By Users.LName,
Users.FName,
StaffWorkCodes.WorkName,
StaffWorkCodes.PayrollItem,
Staffing.SDate,
StaffPersonnel.WorkTime
[/tt]

Since you are only returning 3 of those columns, I suggest that you remove the others. Your final query should probably be:

Select  Users.LName, 
        Users.FName, 
        Sum((StaffPersonnel.WorkTime / 60.0)) 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
Where   StaffWorkCodes.PayrollItem = 'Yes' 
        And Staffing.SDate Between Cast('1/1/2013' As SMALLDATETIME) 
                               And Cast('1/31/2014' As SMALLDATETIME)
Group By Users.LName, 
         Users.FName, 
         StaffWorkCodes.WorkName
Order By Users.LName, Users.FName

Please try this query. If it produces the correct results and you would like me to explain further, please let me know.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ItHurtsWhenIThink]

George,

Works great. Great points as well.