Results 1 to 9 of 9
  1. #1
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Unanswered: Hours Days and Months

    This code displays records by the the month they where recorded and displays the months as names (January...)
    How can I do the Same for Days (Mon, Tues) and Hours (9.00,10.00)

    Select DATENAME(mm,dDate) as Month, count(iStatID) as Total_Po
    from ListingStats
    Group by DATENAME(mm,dDate)
    Order by DATENAME(mm,dDate)

  2. #2
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Weekly as well if possible

  3. #3
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Same way, Nate:

    Code:
    Select DATENAME(dw,date) as day, count(id) as Total_Po
    from eventlog
    Group by DATENAME(dw,date)
    Order by DATENAME(dw,date)
    
    Select DATENAME(wk, date) as weeknumber, count(id) as Total_Po
    from eventlog
    Group by DATENAME(wk,date)
    Order by DATENAME(wk,date)
    
    Select DATENAME(hh, date) as hour, count(id) as Total_Po
    from eventlog
    Group by DATENAME(hh,date)
    Order by DATENAME(hh,date)
    table and count column names were changed to protect the innoce...ok, because I was too lazy to change 'em to your names

    Look at BOL under the subject "DATEPART"
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  4. #4
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    THis Displays day of the Week

    Select DATENAME(dw,dDate) as Month, count(iStatID) as Total_Po
    from ListingStats
    Group by DATENAME(dw,dDate)
    Order by DATENAME(dw,dDate)

  5. #5
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Awesome thanks alot

  6. #6
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Not a problem! Remember always, BOL is our friend.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  7. #7
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    What about joining Values Like
    16-Sept

    or Monday 16th

    Can that be done with SQL? I'm trying to display the data in crystal reports.

  8. #8
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    You bet!

    Take a look at Books Online in CONVERT and STRING system functions.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  9. #9
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    I think Ive figured them out thanks for your help need some more test data can't be bothered loading it right now.

    Im pretty sure this loads the last seven days views

    Select DATENAME(dd,dDate)+ ' ' + Substring(DATENAME(mm,dDate),0,4) as Day, count(iStatID) as Views
    from ListingStats Where IListingID = @ListingID AND (dDate < GetDate()) AND (dDate > DATENAME(dd,GetDate())-7)
    Group by DATENAME(dd,dDate)+ ' ' + Substring(DATENAME(mm,dDate),0,4)
    Order by DATENAME(dd,dDate)+ ' ' + Substring(DATENAME(mm,dDate),0,4)

    Thanks for your help much appreciated

Posting Permissions

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