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

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4

    Correct use of calendar table to fill gaps


    Hi everyone

    I have a table with a productID, date and revenue column.

    I want to return the revenue per date between 2012-01-01 and 2012-06-30 for a specific productID.

    There is no revenue data between 2012-01-01 and 2012-03-15 but I want rows returned for those NULL dates anyway.

    I'm using a calendar table to do this but I can't find the correct way to join them.

    Code:
    SELECT calendar.datefield
         , revenue.totalrevenue
      FROM calendar
    RIGHT JOIN revenue
            ON revenue.date = calendar.datefield
         WHERE (calendar.datefield BETWEEN '2012-01-01' AND '2012-06-30') 
           AND (revenue.productID = 'A'
                OR revenue.totalrevenue = '' 
                OR revenue.totalrevenue IS NULL);
    The above returns all the dates and revenues where revenue exists, but not the 'blank' dates.

    I think this is to do with understanding what side of the query needs to allow nulls, or something - but maybe not!

    Any assistance would be great appreciated.
  2. #2
  3. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    Solved, I think:

    Code:
    SELECT calendar.datefield
         , revenue.totalrevenue
      FROM calendar
    LEFT JOIN revenue
            ON revenue.date = calendar.datefield
         WHERE calendar.datefield BETWEEN '2012-01-01' AND '2012-06-30'
           AND (revenue.productID = 'A' OR revenue.productID IS NULL);
    Comments welcomed!
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Intermediate (1500 - 1999 posts)

    Join Date
    Mar 2008
    Posts
    1,928
    Rep Power
    378
    Code:
    SELECT c.datefield
         , r.totalrevenue
      FROM calendar c
    LEFT JOIN revenue r
            ON r.date = c.datefield
         AND r.productID = 'A'
       WHERE c.datefield BETWEEN '2012-01-01' AND '2012-06-30';
  6. #4
  7. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Mar 2011
    Posts
    53
    Rep Power
    4
    Ah, that's much more elegant.

    Thank you.

IMN logo majestic logo threadwatch logo seochat tools logo