#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    87
    Rep Power
    21

    Left join weekdays and time interval


    Hi!

    I try to sum up visits based on the different times of day and days of the week but I can't figure out how to get all time interval and days, even for zero visits.

    The following query works fine and I get a summary of all days, even if it's zero visits on certain days. The weekdays table contains a ID and the name of the weekday.
    Code:
    SELECT w.name
         , COUNT(v.ID) cnt 
    FROM weekdays w
    LEFT JOIN visits v ON w.ID = WEEKDAY(v.date) AND YEAR(v.date) = '2014'
    GROUP BY w.name
    My problem is when I try to add time interval to question I only get time interval and days with non-zero value but I want all the time intervals for all days.
    Code:
    SELECT CASE
            WHEN HOUR(date) BETWEEN '06' AND '12' THEN '06-12'
            WHEN HOUR(date) BETWEEN '13' AND '18' THEN '13-18'
            WHEN HOUR(date) BETWEEN '19' AND '24' THEN '19-24'
            ELSE 'NA'
           END as time_interval 
         , w.name
         , COUNT(v.ID) cnt 
    FROM gh_weekdays w
    LEFT JOIN gh_visits v ON w.ID = WEEKDAY(v.date) AND YEAR(v.date) = '2014'
    GROUP BY time_interval, w.name
    Any help is appreciated!

    Kind regards, Andreas
  2. #2
  3. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    you need another table for the time intervals
    Code:
    CREATE TABLE time_intervals
    ( lo_hr TINYINT
    , hi_hr TINYINT
    , time_interval CHAR(5)
    );
    
    INSERT INTO time_intervals VALUES 
     ( 6,12,'06-12')
    ,(13,18,'13-18')
    ,(19,24,'19-24')
    ,( 0, 5,'NA')
    ;
    
    SELECT w.name
         , t.time_interval
         , COUNT(v.ID) cnt 
      FROM gh_weekdays AS w
    CROSS
      JOIN time_intervals AS t  
    LEFT OUTER
      JOIN gh_visits AS v 
        ON YEAR(v.date) = 2014
       AND WEEKDAY(v.date) = w.ID
       AND HOUR(v.date) BETWEEN t.lo_hr AND t.hi_hr 
    GROUP 
        BY w.name
         , t.time_interval

    Comments on this post

    • andy_hbg agrees
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    87
    Rep Power
    21
    Originally Posted by r937
    you need another table for the time intervals
    Thank you, just what i wanted!

    The query takes about 15 seconds to execute with just over 400'000 rows in gh_visits. Is that what you can expect?

    Regards!
  6. #4
  7. SQL Consultant
    Devshed Supreme Being (6500+ posts)

    Join Date
    Feb 2003
    Location
    Toronto Canada
    Posts
    27,178
    Rep Power
    4274
    Originally Posted by andy_hbg
    Is that what you can expect?
    yes, because of the way the join uses functions on the date column
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
  8. #5
  9. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Dec 2008
    Posts
    87
    Rep Power
    21
    Originally Posted by r937
    yes, because of the way the join uses functions on the date column
    Thanks again!

IMN logo majestic logo threadwatch logo seochat tools logo