columns based on different where clauses

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

SQL Server 2005 Forums

Transact-SQL (2005)

columns based on different where clauses

Author

Topic

drarmst
Starting Member

3 Posts

Posted - 2011-01-17 : 10:33:24

Hello...I'm no SQL expert...so this could be a dumb question..

I have a table made for keeping track of timepunches for employees. The columns are:

tpKey, userKey, timeIn, timeOut, isRegHours, isSickHours, isVacationHours, isPersonalHours

I would like to have a select statement return a table like this:

Username | Regular Time | Sick Time | Vacation Time | Personal Time

I can do this for just regular time, or just sick time, etc with the following statement:

select userID,
  sum(TotalSeconds) / 3600 as Hours, 
  (sum(TotalSeconds) % 3600) / 60 as Minutes
from
(
    select userID, DateDiff(second, timeIn, timeOut) as TotalSeconds 
    FROM users INNER JOIN timePunches ON users.userKey=timePunches.userKey  where isRegHours=1
) a 
group by userID;

Results:


userName    Hours    Minutes
user1           2          0
user2           7	  30

But I don't know how to combine 4 of those statements into one table. Any help would be greatly appreciated.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2011-01-17 : 10:51:26

You could do something like this:


select 
	  userID
	, CASE WHEN isRegHours = 1 THEN Convert(VARCHAR(4),Hours) + ':' +  Convert(VARCHAR(4),Minutes) ELSE NULL END AS RegularTime
	, CASE WHEN isSickHours = 1 THEN Convert(VARCHAR(4),Hours) + ':' +  Convert(VARCHAR(4),Minutes) ELSE NULL END AS SickTime
	, CASE WHEN isVacationHours = 1 THEN Convert(VARCHAR(4),Hours) + ':' +  Convert(VARCHAR(4),Minutes) ELSE NULL END AS VacationTime
	, CASE WHEN isPersonalHours = 1 THEN Convert(VARCHAR(4),Hours) + ':' +  Convert(VARCHAR(4),Minutes) ELSE NULL END AS PersonalTime
from
(
    select 
		  userID
		, isRegHours
		, isSickHours
		, isVacationHours
		, isPersonalHours
		, sum(DateDiff(second, timeIn, timeOut) / 3600) as Hours
		, sum(DateDiff(second, timeIn, timeOut) % 3600) as Minutes	
    FROM 
		users 
	INNER JOIN 
		timePunches 
	ON 
		users.userKey = timePunches.userKey
	GROUP BY
		  userID
		, isRegHours
		, isSickHours
		, isVacationHours
		, isPersonalHours
) a

drarmst
Starting Member

3 Posts

Posted - 2011-01-17 : 11:38:31

Thanks for the help...this is very close to what I'm looking for. I changed these lines in your statement as the time wasn't quite calculating right:

		, sum(DateDiff(second, timeIn, timeOut)) / 3600 as Hours
		, (sum(DateDiff(second, timeIn, timeOut)) % 3600) / 60 as Minutes

The results are now:


userID          Reg     Sick    Vac     Pers
user1           NULL	NULL	NULL	9:0
user1           NULL	NULL	16:23	NULL
user1           NULL	2:0	NULL	NULL
user1           52:0	NULL	NULL	NULL
user2           NULL	7:30	NULL	NULL
user2           18:0	NULL	NULL	NULL

Is there a way to group by the userID so that it would only show null if there were no entries in the table with that category of time? Like this:


userID          Reg     Sick    Vac     Pers
user1           52:0	2:0	16:23    9:0
user2           18:0	7:30	NULL	NULL

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-01-17 : 11:54:16

You can add a MAX to each of RickD's case statement and then group by userID

Jim

Everyday I learn something that somebody else already knew

drarmst
Starting Member

3 Posts

Posted - 2011-01-17 : 13:17:41

That works great! Thanks to both of you for the help.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources