Post reply

how to change the QUERY to get todays timespent ??

  • June 16, 2004 at 9:52 pm

    #108393

    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.

  • 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 3 posts - 1 through 2 (of 2 total)

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