Formatting this crazy SQL with Group By? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Formatting this crazy SQL with Group By?

Author

Topic

monaya
Yak Posting Veteran

58 Posts

Posted - 2012-02-01 : 12:36:22

I have a table with clock in and out timestamps with different employee positions. Employee can clock in with different positions. I'd like to display the hours worked for each position type over a given period.

here's mysql:

SELECT staffclockin.staffid, stafftypes.stafftype, SUM(TIMESTAMPDIFF(MINUTE,staffclockin.clockintime,staffclockin.clockouttime)/60) AS thehours, SUM(staffclockin.tips) AS totaltips 
FROM staffclockin, stafftypes
WHERE clockintime between "2012-01-01" and "2012-02-10" AND stafftypes.stafftypeid = staffclockin.stafftypeid
GROUP BY staffclockin.stafftypeid, staffclockin.staffid
ORDER BY staffclockin.clockinid DESC

Of course, I'm getting results like this with duplicate staff id's:

I'd like to get results to look more like my sketch below to show staff id only once? is that possible?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-02-01 : 12:42:07

One option is tp use a CASE expression. This is not 100%, but something to get you going:

SELECT 
    staffclockin.staffid, 
    SUM(CASE WHEN stafftypes.stafftype = 'Bartender' THEN TIMESTAMPDIFF(MINUTE,staffclockin.clockintime,staffclockin.clockouttime)/60) ELSE 0 END) AS Bartender, 
    SUM(CASE WHEN stafftypes.stafftype = 'Manager' THEN TIMESTAMPDIFF(MINUTE,staffclockin.clockintime,staffclockin.clockouttime)/60) ELSE 0 END) AS Manager, 
    ...
    SUM(staffclockin.tips) AS totaltips 
FROM staffclockin, stafftypes
WHERE clockintime between "2012-01-01" and "2012-02-10" AND stafftypes.stafftypeid = staffclockin.stafftypeid
GROUP BY staffclockin.stafftypeid
ORDER BY staffclockin.clockinid DESC

monaya
Yak Posting Veteran

58 Posts

Posted - 2012-02-01 : 13:07:27

That's actually working pretty nice. The only problem is I'm still getting duplicate staff id's on the left. is there a way to make that appear once like the position?

monaya
Yak Posting Veteran

58 Posts

Posted - 2012-02-01 : 13:08:51

I'm trying to combine the hours for a single staff member into one column.

I think if I just

group by staffclockin.staffid

this should work. Let me see

monaya
Yak Posting Veteran

58 Posts

Posted - 2012-02-01 : 14:22:10

That worked for me. I just changed the group by to the staffid. Thank you!!!!!!!!!!!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources