Results 1 to 5 of 5
  1. #1
    Join Date
    Sep 2010
    Posts
    153

    Smile Unanswered: getting values based on date

    Hi, I have a doubt. I have a tabke where i am inserting the records..

    Structure of table :-

    Name Marks Times
    ----------- -------- ------ -----------

    RAM 98 2011-04-26 11:27:23.187
    RAJESH 76 2011-04-26 11:27:33.717
    RAJU 70 2011-04-26 11:27:42.403
    RAJIV 85 2011-04-20 11:29:23.810


    Notice:-

    first three data are for current date (getdate()) and the last one is on 20th april.

    Requirement - i want average of marks where data should be of sysdate / getdate / current date.

    Average is taken in thre eintervals due to heavy load of data.

    1st) average of all the marks which comes in between 12am to 8 am.

    2nd) average of all the marks which comes in between 8 am to 4pm.

    3rd) average of all the marks which comes in between 4pm to midnight 12.

    Hoping to get the solution as soon as possible. It's very urgent Kindly try it..please
    Thanks lot

  2. #2
    Join Date
    Sep 2010
    Posts
    153
    Sorry guys.

    I did it.

    this is the query:-

    select avg(convert(float, marks)) from student where datepart (dd, times) = datepart (dd, getdate()) and datepart (hh, getdate()) between 8 and 16;

  3. #3
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    your solution fails if there are any marks in other months besides the current month
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  4. #4
    Join Date
    Sep 2010
    Posts
    153

    Smile It's workign properly :O

    Hi,
    For me it's workign properly even when the marks are in different month. I might be wrong in testing. Could you provide me the solution or the way to test my query because it's workign absolutely fine with my code.


    Code will be appreciated

    Thanks

  5. #5
    Join Date
    May 2005
    Location
    South Africa
    Posts
    1,365
    Provided Answers: 1
    Try with this test data
    RAM 8 2011-03-28 11:27:23.187
    RAJESH 6 2011-03-28 11:27:33.717
    RAJU 0 2011-03-28 11:27:42.403
    RAJIV 5 2011-03-20 11:29:23.810
    RAM 98 2011-04-28 11:27:23.187
    RAJESH 76 2011-04-28 11:27:33.717
    RAJU 70 2011-04-28 11:27:42.403
    RAJIV 85 2011-04-20 11:29:23.810

Posting Permissions

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