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

    Unanswered: Ordering Dated Statistics for Graphing

    Im trying to create a line graph for displaying the number of page views, in this instance for the last 7 days, problem is I would like to dispaly the graph as the last 7 days, so for today, tuesday would be the last day to hold a value on the graph and the six previous would be monday through wednesday descending, currently the values for the graph are thrown in by the day where monday always shows in the same position in the graph which is not that great,, (hope that makes sense)

    If I could get the values out of the database in the right order I can use them to display in the graph as like,


    Currently this is not as as I like,
    PHP Code:
    SELECT COUNT([Day]) as num,[Day],dDate FROM ListingStats 
                                        WHERE iListingID
    =12
                                         
    AND (dDate <= GetDate() AND dDate >= DateAdd(d,-7,GetDate()))
    GROUP BY [DAY],dDate
    ORDER BY 
    [DAYASC 
    where the date is a datetime field, can I trunc this to get just the date so this current format will work? day represents an Enum of the day of the week 1=monday etc

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    This will truncate a datetime value to a whole date:
    Code:
    dateadd(day, datediff(day, 0, dDate), 0)
    You might also find this handy for displaying the days past:
    Code:
    datediff(day, getdate(), dDate)
    If you have further questions, please post the output data you are trying to get.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1

    Redesigned Date Table

    Ive Redesigned the Statistics table so I only use one row per day, the time they view doesn't matter, using this SPROC to insert the Records

    PHP Code:
    CREATE PROCEDURE [dbo].[InsertListingStatistics]
    (
    @
    ListingID INT,
    @
    Date DateTime,
    @
    DayofWeek INT,
    @
    MonthofYear INT,
    @
    Year INT,
    @
    Insert_IfNotExists BIT 1
    )
    AS
    BEGIN
        SET NOCOUNT ON
    ;
        
        DECLARE @
    HasRow BIT
        SELECT 
    @HasRow=COUNT(*) FROM ListingStats WHERE iListingID=@ListingID

    IF @Insert_IfNotExists AND @HasRow=0
    BEGIN
        INSERT INTO ListingStats
    (
                
    iListingID,
                
    dDate,
                [
    Day],
                [
    Month],
                [
    Year],
                
    NumViews)
            
    VALUES(
                @
    ListingID,
                @
    Date,
                @
    DayofWeek,
                @
    MonthofYear,
                @
    Year,
                
    1
            
    )
        
    END
        
    ELSE
        
    BEGIN
            UPDATE ListingStats SET numviews 
    = (numviews+1)
                
    WHERE iListingID=@ListingID

        END


    END
    GO 
    This WIll now display the last 7 days with a bit of application logic with the days
    PHP Code:
    SELECT dateadd(daydatediff(day0dDate), 0),* From ListingStats 
            WHERE iListingID 
    12
    AND (dDate <= GetDate() AND dDate >= DateAdd(d,-7,GetDate())) ORDER BY dDate ASC 
    Though Now Im onto displaying Months and can't get the group by correct
    this would be the month select statement
    PHP Code:
    SELECT SUM(NumViews) as NumViews,[Month],[Year],dateadd(daydatediff(day0dDate), 0FROM ListingStats
    WHERE iListingID 
    12
    AND (dDate <= GetDate() AND dDate >= DateAdd(d,-365,GetDate()))
    GROUP BY [Month],[Year] ,DDate
    ORDER BY dDate ASC 

    Which Currently Shows 33 records representing (test data), where the days of a record being recorded are held, though this should be grouped by the month so the 13th 14th and 25th of december should all be summed fro Dec of whatever year.
    Heres some data from the select anyway
    PHP Code:
    16    6    2007    2007-06-14 00:00:00.000
    10    7    2007    2007
    -07-14 00:00:00.000
    16    8    2007    2007
    -08-14 00:00:00.000
    13    9    2007    2007
    -09-14 00:00:00.000
    7    10    2007    2007
    -10-14 00:00:00.000
    5    11    2007    2007
    -11-14 00:00:00.000
    10    12    2007    2007
    -12-11 00:00:00.000
    12    12    2007    2007
    -12-12 00:00:00.000
    22    12    2007    2007
    -12-13 00:00:00.000
    10    1    2008    2008
    -01-07 00:00:00.000
    10    2    2008    2008
    -02-07 00:00:00.000
    32    2    2008    2008
    -02-08 00:00:00.000
    13    3    2008    2008
    -03-09 00:00:00.000
    7    3    2008    2008
    -03-10 00:00:00.000
    5    3    2008    2008
    -03-11 00:00:00.000
    10    4    2008    2008
    -04-12 00:00:00.000
    12    4    2008    2008
    -04-13 00:00:00.000
    22    4    2008    2008
    -04-14 00:00:00.000
    10    5    2008    2008
    -05-07 00:00:00.000
    16    5    2008    2008
    -05-08 00:00:00.000
    13    5    2008    2008
    -05-09 00:00:00.000
    7    5    2008    2008
    -05-10 00:00:00.000
    5    5    2008    2008
    -05-11 00:00:00.000
    10    5    2008    2008
    -05-12 00:00:00.000
    12    5    2008    2008
    -05-13 00:00:00.000
    22    5    2008    2008
    -05-14 00:00:00.000
    12    5    2008    2008
    -05-15 00:00:00.000
    13    5    2008    2008
    -05-16 00:00:00.000
    12    5    2008    2008
    -05-17 00:00:00.000
    15    5    2008    2008
    -05-18 00:00:00.000
    5    5    2008    2008
    -05-19 00:00:00.000
    11    5    2008    2008
    -05-20 00:00:00.000
    7    5    2008    2008
    -05-21 00:00:00.000 

  4. #4
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    OK got it, not sure if its the best way but it works

    PHP Code:
    SELECT [dbo].ufn_GetDateOnly(dDate), 
    SUM(NumViews) as NumViews,[Month],[YearFROM ListingStats
    WHERE iListingID 
    12
    AND (dDate <= GetDate() AND dDate >= DateAdd(d,-365,GetDate()))
    GROUP BY [Month],[Year] ,[dbo].ufn_GetDateOnly(dDate)
    ORDER BY [dbo].ufn_GetDateOnly(dDateASC 
    where
    [dbo].ufn_GetDateOnly is :
    PHP Code:
    CREATE FUNCTION [dbo].[ufn_GetDateOnly] ( @pInputDate    DATETIME )
    RETURNS DATETIME
    BEGIN

        
    RETURN CAST(CAST(YEAR(@pInputDate) AS VARCHAR(4)) + '/' +
                    
    CAST(MONTH(@pInputDate) AS VARCHAR(2)) + '/' +
                    
    CAST(DAY(0) AS VARCHAR(2)) AS DATETIME)

    END
    GO 

  5. #5
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    ufn_GetDateOnly?
    I'm not sure why you bother posting questions if you aren't going to pay attention to the responses.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  6. #6
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    Sorry, I tried to make the suggested item work for the Months, but didn't work as I'd hoped, I'll have another look at it when I get some time

  7. #7
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Making it work for months is easy, but I can't see anywhere in your code where you are trying to truncate to a month.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  8. #8
    Join Date
    Aug 2007
    Location
    Auckland New Zealand
    Posts
    120
    Provided Answers: 1
    To be Honest, I don't really understand how that functions doing it either? but it works and displays the correct data, so it will do for now.

  9. #9
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Ignorance = the next developers worst nightmare
    George
    Home | Blog

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by georgev
    Ignorance = the next developers worst nightmare
    ... = the next consultant's paycheck.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    ssshhhhhh. you will give away the secret as to why we only hand out bad advice on the internet. you know it's the internet way of doing business. you get something crappy for free and you have to pay for the good stuff.
    “If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry.” Earnest Hemingway, A Farewell To Arms.

Posting Permissions

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