Post reply

how to change the QUERY to get todays timespent ??

  • June 16, 2004 at 11:57 pm

    #510757

    How about this?

    select fk_assid,
CASE WHEN sum(datediff(n,wt_starttime,wt_stoptime)) IS NULL THEN '0' 
ELSE sum(datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))) 
END as 'timespent',
SUM(CASE WHEN datediff(d, CONVERT(varchar(15), GetDate(), 106), CONVERT(varchar(15), wt_starttime, 106)) = 0 
THEN datediff(n,wt_starttime,isnull(wt_stoptime,getdate()))
ELSE '0' END)  as 'timespenttoday'
from worktime
group by fk_assid

    In the second CASE I just check if todays date and the date of the start time are the same. The CONVERT statement converts the Datetime to 'dd-mmm-yyyy'

    --------------------
    Colt 45 - the original point and click interface

  • June 17, 2004 at 3:04 pm

    #510934

    Phile what can i say that worked beautifully

    Thank u very much

Viewing 2 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply