MySQL Help
 
Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
User Name:
Password:
Remember me

The Shed is going Social! Join us on FaceBook and Twitter and chime in on the conversation.

Go Back   Dev Shed ForumsDatabasesMySQL Help

Reply
Add This Thread To:
  Del.icio.us   Digg   Google   Spurl   Blink   Furl   Simpy   Y! MyWeb 
Thread Tools Search this Thread Rate Thread Display Modes
 
Unread Dev Shed Forums Sponsor:
  #1  
Old January 7th, 2013, 09:15 AM
phpcog2 phpcog2 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2011
Posts: 39 phpcog2 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 24 m 55 sec
Reputation Power: 3
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.

Reply With Quote
  #2  
Old January 7th, 2013, 09:27 AM
phpcog2 phpcog2 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2011
Posts: 39 phpcog2 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 24 m 55 sec
Reputation Power: 3
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!

Reply With Quote
  #3  
Old January 7th, 2013, 10:46 AM
cafelatte cafelatte is offline
Contributing User
Dev Shed Intermediate (1500 - 1999 posts)
 
Join Date: Mar 2008
Posts: 1,923 cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level)cafelatte User rank is Major (30000 - 40000 Reputation Level) 
Time spent in forums: 1 Month 5 Days 16 h 21 m 8 sec
Reputation Power: 377
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';

Reply With Quote
  #4  
Old January 7th, 2013, 10:50 AM
phpcog2 phpcog2 is offline
Contributing User
Dev Shed Newbie (0 - 499 posts)
 
Join Date: Mar 2011
Posts: 39 phpcog2 User rank is Just a Lowly Private (1 - 20 Reputation Level) 
Time spent in forums: 8 h 24 m 55 sec
Reputation Power: 3
Ah, that's much more elegant.

Thank you.

Reply With Quote
Reply

Viewing: Dev Shed ForumsDatabasesMySQL Help > Correct use of calendar table to fill gaps

Developer Shed Advertisers and Affiliates



Thread Tools  Search this Thread 
Search this Thread:

Advanced Search
Display Modes  Rate This Thread 
Rate This Thread:


Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is On
HTML code is Off
View Your Warnings | New Posts | Latest News | Latest Threads | Shoutbox
Forum Jump

Forums: » Register « |  User CP |  Games |  Calendar |  Members |  FAQs |  Sitemap |  Support | 
  
 


Powered by: vBulletin Version 3.0.5
Copyright ©2000 - 2013, Jelsoft Enterprises Ltd.

© 2003-2013 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap