SQL Server - procedure to calculate sum - Asked By Lucy Margret on 19-Aug-11 08:39 AM

Hi,
i need to write a procedure to calculate the sum(hours) where userid='@userid' and project='@project' and also between the dates,@date1 and @date2. I have a fields named hours, userid,project and date...
Thanks
pete rainbow replied to Lucy Margret on 19-Aug-11 10:27 AM
this should give you a start...

CREATE
PROCEDURE TotalHours
@UserId INT ,
@project Varchar(255),
@date1 datetime,
@date2 datetime
AS
 
SELECT sum(hours)
FROM sometableOrOtherYouForgotToMention
WHERE userid = @userid and project = @project and date between @date1 and @date2
GROUP BY this that and the other
Rohan Dave replied to Lucy Margret on 19-Aug-11 04:36 PM
Your Procedure looks like below ...

create procedure usp_CountHoursofProject
@UserID BIGINT,
@Project Varchar(Max),
@Date1 DateTime,
@Date2 DateTime
As
BEGIN
Select Sum(Hours) From yourtablename
Where
UserId = @UserId
And Project = @Project
And date Between @Date1 And @Date2

END
Anoop S replied to Lucy Margret on 20-Aug-11 06:43 AM
Use DATEDIFF (Transact-SQL)

DATEDIFF ( datepart , startdate , enddate )
You can use SQL Server DateDiff function to calculate difference between two dates. This delay can be calculated in days, months, years, minutes,hours or seconds.

Datepart

Abbreviations

year

yy, yyyy

quarter

qq, q

month

mm, m

dayofyear

dy, y

day

dd, d

week

wk, ww

Hour

hh

minute

mi, n

second

ss, s

millisecond

ms