query to get today's timespent

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 2000 Forums

Transact-SQL (2000)

query to get today's timespent

Author

Topic

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-16 : 23:33:13

Hi friends
following query gets me list of assignments and timespent on them so far

select fk_assid,
'timespent'=CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0'
ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) END from worktime
group by fk_assid

in the same query as 3rd column i want to find out how much time spent
today.

worktime table has following fields
WTID,fk_assid,wt_starttime,wt_stoptime
just to let u know everytime we start work on assignment a new entry
will be created in worktime table.
many thanks for ur ideas.

Cheers

dataphile
Yak Posting Veteran

71 Posts

Posted - 2004-06-17 : 03:39:51

sum(case when convert(char(10),wt_starttime,111) = convert(char(10),getdate(),111)
then datediff(n,wt_starttime,isnull(wt_stoptime,getdate())) else 0 end)

/*********
if convert(char(10),wt_starttime,111) = convert(char(10),getdate(),111) then wt_starttime is today
have a look at the "coalesce" function in "books online". will simplify the second column's statement.
*/

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-17 : 16:59:12

Thank u veru much dataphile.
that worked beautifully :-)

Cheers

rajani
Constraint Violating Yak Guru

367 Posts

Posted - 2004-06-17 : 17:26:37

by the way dataphile
u suggested i use coalesce
in my case the field wt_starttime will never be null but wt_stoptime can be
null.can i still use coalesce ?if so how can i change my 2nd column query

Cheers

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources