stop select case creating new rows?

[Tommy Pickersgill]

I've created a stored procedure which retrives a list of worklogs for a specific week, log time is added together, joined to user table based on UserID and then returned as HoursWorked. This worked fine as the following code:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname, round(sum(WorkDone) / 60, 1)
FROM Users u LEFT OUTER JOIN
(
select sum(WorkDone) WorkDone, UserId UserId, WorkUnitTypeId WorkUnitTypeId
FROM worklog
WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
GROUP BY UserID, WorkUnitTypeId
HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId
ORDER BY sum(WorkDone) DESC

END

I then realised WorkDone can sometimes store hours, days or weeks and is defined in 'WorkUnitTypeID' so I added a CASE statement to the Select:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname,
CASE w.WorkUnitTypeId
WHEN 1 THEN ISNULL(round(sum(w.WorkDone / 60),1), 0) --Minutes
WHEN 2 THEN ISNULL(round(sum(w.WorkDone),1), 0) --Hours
WHEN 3 THEN ISNULL(round(sum(w.WorkDone * 24),1), 0) --Days
WHEN 4 THEN ISNULL(round(sum(w.WorkDone * 168),1), 0) --Weeks
ELSE 0
END WorkDone
FROM Users u LEFT OUTER JOIN
(
select sum(WorkDone) WorkDone, UserId UserId, WorkUnitTypeId WorkUnitTypeId
FROM worklog
WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
GROUP BY UserID, WorkUnitTypeId
HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId
ORDER BY sum(WorkDone) DESC

END

Again this worked but presented a new issue, the procedure now returns multiple rows for the same user but for each of the WorkUnitTypeID - instead of adding them together. I'm new to SQL in general and have that feeling I've missed something really si

[WoutL]

Try replacing

GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId

by

Group by u.Email, u.FirstName + ' ' + u.LastName,
CASE w.WorkUnitTypeId
WHEN 1 THEN ISNULL(round(sum(w.WorkDone / 60),1), 0) --Minutes
WHEN 2 THEN ISNULL(round(sum(w.WorkDone),1), 0) --Hours
WHEN 3 THEN ISNULL(round(sum(w.WorkDone * 24),1), 0) --Days
WHEN 4 THEN ISNULL(round(sum(w.WorkDone * 168),1), 0) --Weeks
ELSE 0
END

Wout Louwers

[Tommy Pickersgill]

Thanks for the reply but no joy:

Cannot use an aggregate or a subquery in an expression used for the group by list of a GROUP BY clause.

[Tommy Pickersgill]

Can anyone else offer any more on this? Sorry for the spam but it's quite urgent. I've tried playing about with it more by placing the case inside the JOIN - which I got my hopes up about but still the same result of recieving multiple user rows with different worklogs :mad:

ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek]
(
@MinimumHours float,
@EndDate datetime,
@AmountOfDaysBack int
)
as
begin
SELECT u.Email,
u.FirstName + ' ' + u.LastName AS Usersname,
w.WorkDone
from Users u LEFT OUTER JOIN
(
Select UserID,
WorkUnitTypeId,
CASE WorkUnitTypeId
WHEN 1 THEN ISNULL(round(sum(WorkDone / 60),1), 0) --Minutes
WHEN 2 THEN ISNULL(round(sum(WorkDone),1), 0) --Hours
WHEN 3 THEN ISNULL(round(sum(WorkDone * 24),1), 0) --Days
WHEN 4 THEN ISNULL(round(sum(WorkDone * 168),1), 0) --Weeks
ELSE 0
END as WorkDone
FROM worklog
WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate)
--HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours
GROUP BY UserId,
WorkUnitTypeId
) w
ON u.UserId=w.UserId
WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1
GROUP BY w.UserId,
u.FirstName,
u.LastName,
u.Email,
WorkUnitTypeId,
WorkDone
ORDER BY sum(WorkDone) DESC

END

[WoutL]

Sorry, My mistake. Try this group by clause:

Group by u.Email, u.FirstName + ' ' + u.LastName

Wout Louwers

[i j russell]

Try this ALTER procedure [dbo].[GetAllUsersWhoHaventSubmittedTimeForWeek] ( @MinimumHours float, @EndDate datetime, @AmountOfDaysBack int ) as begin SELECT u.Email, u.FirstName + ' ' + u.LastName AS Usersname, w.WorkDone from Users u LEFT OUTER JOIN ( Select UserID, Sum(CASE WorkUnitTypeId WHEN 1 THEN ISNULL(round(sum(WorkDone / 60),1), 0) --Minutes WHEN 2 THEN ISNULL(round(sum(WorkDone),1), 0) --Hours WHEN 3 THEN ISNULL(round(sum(WorkDone * 24),1), 0) --Days WHEN 4 THEN ISNULL(round(sum(WorkDone * 168),1), 0) --Weeks ELSE 0 END) as WorkDone FROM worklog WHERE WorkLogDateTime >= dateadd(d, -@AmountOfDaysBack, @EndDate) and WorkLogDateTime <= dateadd(d, 1, @EndDate) --HAVING round(sum(WorkDone) / 60, 1) < @MinimumHours GROUP BY UserId ) w ON u.UserId=w.UserId WHERE u.userid not in (SELECT User_ID FROM ReportUsers WHERE Status = 0) and u.isactive = 1 GROUP BY w.UserId, u.FirstName, u.LastName, u.Email, WorkUnitTypeId, WorkDone ORDER BY sum(WorkDone) DESC END

[Tommy Pickersgill]

[deleted] Got it working! Bascilly deleted it and started again and managed to get it working from the advice you both gave (Wrapping the case in a sum and removing Workunittypeid from the top level group by). Thanks for the help - first and last stored procedure I attempt :laugh:

modified on Thursday, August 6, 2009 5:45 AM