Post reply

SQL Query returning number of updated tasks in a day

  • December 14, 2007 at 4:56 am

    #62601

    Hi,

    I've got a table that holds tasks updated by user simplified to three columns only:

    TaskID UserName ModifiedDate

    int varchar smalldatetime

    0 admin 14/12/2007 01:34:00

    1 admin 14/12/2007 02:23:00

    3 user 13/12/2007 03:23:00

    4 user 13/12/2007 05:53:00

    5 user 14/12/2007 06:53:00

    6 user 14/12/2007 07:53:00

    7 user 14/12/2007 08:53:00

    etc.

    now I need to create a stored proc (with function to return New table) with In params:

    @UserName

    @StartDate

    @EndDate

    And I want to return a table for UserName "user":

    DayDate NoOfUpdatedTasks

    13/12/2007 2

    14/12/2007 2

    My Problems:

    1) i cannot convert date from long format 13/12/2007 05:53:00 to 13/12/2007 day only (I'm using set @StartDate = convert (datetime, @StartDate, 103)

    2) when I count distinct it always returns full date and number of tasks 1

    Please Help!

    kris

  • December 14, 2007 at 5:22 am

    #760156

    Use something along these lines (no guarantee, since you didn't post table structure - but it should give you idea how to strip time from datetime value):

    SELECT DATEADD(d, DATEDIFF(d, 0, ModifiedDate), 0) as day_only,

    COUNT(*) as No_of_updated_tasks

    FROM tasktable

    WHERE user = @user

    GROUP BY DATEADD(d, DATEDIFF(d, 0, ModifiedDate), 0)

    ORDER BY 1

  • December 14, 2007 at 6:23 am

    #760199

    Exactly what I wanted!

    Thank you Vladan

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

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