Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2004
    Location
    Lexington KY
    Posts
    2

    Unanswered: grouping by date

    I have a table defined as
    ProjectID Number,
    StartTIme Date/Time
    EndTime Date/Time

    I have created a function to return the elapsed time between StartTime and EndTime.
    I have created a query that returns the ProjectID, StartTIme and ElapsedTime as follows

    SELECT tbl_detail.ProjectID, tbl_detail.StartTime, (DateDiff("n",[StartTime],[EndTime]))/60 AS elapsedtime
    FROM tbl_detail;

    My problem is how do I create a query that groups on the StartDate using only the year/month/day portion of StartDate

    If I execute the following

    SELECT tbl_detail.ProjectID, tbl_detail.StartTime, Sum((DateDiff("n",[StartTime],[EndTime]))/60) AS elapsedtime
    FROM tbl_detail
    GROUP BY tbl_detail.ProjectID, tbl_detail.StartTime;

    It includes the time portion of StartTime when grouping so I get more than on row per project per day.
    I have come up with some code that handles this by breaking the date into parts and creating text fields but it is very cumbersome.
    I know Oracle has a more elegant way of doing this.

    Any help would be appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    PHP Code:
    select ProjectID
         
    format(StartTime,"yyyy-mm-dd") as StartDate
         
    Sum((DateDiff("n",[StartTime],[EndTime]))/60) AS elapsedtime
      from tbl_detail
    group 
        by ProjectID
         
    format(StartTime,"yyyy-mm-dd"
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    Posts
    1

    Re: grouping by date

    Originally posted by rjirak
    I have a table defined as
    ProjectID Number,
    StartTIme Date/Time
    EndTime Date/Time

    I have created a function to return the elapsed time between StartTime and EndTime.
    I have created a query that returns the ProjectID, StartTIme and ElapsedTime as follows

    SELECT tbl_detail.ProjectID, tbl_detail.StartTime, (DateDiff("n",[StartTime],[EndTime]))/60 AS elapsedtime
    FROM tbl_detail;

    My problem is how do I create a query that groups on the StartDate using only the year/month/day portion of StartDate

    Any help would be appreciated.

    For grouping on DATETIME field use DateValue function, example:

    SELECT DateValue([ tbl_detail.StartTime]) AS Expr1, Sum(tbl_detail.MyNumeric) AS SumOftotal
    FROM tbl_detail
    WHERE (((tbl_detail.StartTime)>#12/1/2003#))
    GROUP BY DateValue(DateValue([ tbl_detail.StartTime]));


    Best regards,

  4. #4
    Join Date
    Feb 2004
    Location
    Lexington KY
    Posts
    2

    Thanks

    Thanks to both.
    Both are workable

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •