Joins,Groupby,Datetime Stored Procedures(REFERENCE)

set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Renju.R
-- Create date: August 1,2008
-- Description:
-- =============================================
ALTER procedure [dbo].[GetEmpPerformance]
@fromDate datetime,
@toDate datetime
--@projectId int,
--@moduleId int
as
begin
select ws.currentStatus,am.activityName,
w.workID,w.work,w.startDate,w.finishDate,w.formId,w.activityId,
ts.activityId,sum(ts.hoursInActivity),sum(ts.minInActivity),
(select UserName from dbo.UserRegMaster where UserID=w.toUser) as ToUser,
--dbo.GetWorkHours(@fromDate, @toDate) TotalWorkingHours,
sum(DATEDIFF(hour,w.startDate,w.finishDate)) as WorkHours,
sum(DATEDIFF(minute,w.startDate,w.finishDate) - (DATEDIFF(hour,w.startDate,w.finishDate) * 60)) as WorkMinutes
from work w
left outer join dbo.workStatus ws on ws.workID=w.workID
and statusDate=(select max(statusDate) from workStatus where workId=w.workID)
left outer join dbo.TimeSheet ts
on ts.activityId=w.activityId and
ts.workID=w.workID
left outer join dbo.ActivityMaster am on ts.activityId=am.activityId and ts.workID=w.workID
where --projID=@projectId and modID=@projectId and
startDate between
convert(datetime,@fromDate,101) and convert(datetime,@toDate,101) group by
ws.currentStatus,am.activityName,
w.workID,w.work,w.startDate,w.finishDate,w.formId,w.activityId,
ts.activityId,ToUser
end