Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
srinath.a
Starting Member
1 Post |
 Posted - 2008-09-08 : 10:07:30
|
| Hi,i am using a sql like this :SELECT count(*) AS count_all, DATE_FORMAT(created_at, '%b %Y') AS date_format FROM users join user_general_info ON users.id = user_general_info.user_id WHERE (created_at BETWEEN '2008-03-01' and '2008-09-04') GROUP BY DATE_FORMAT(created_at, '%b %Y') ORDER BY created_at.from tables users and user_general_info where users table contains id,login,password,activated_at,created_at,updated_at as columns and user_general_info contains id,user_id,fname,lname as columns.and getting output like this :count_all date_format2 Mar 20081 Apr 20084 Jun 200810 Jul 200812 Aug 2008i want to get the missing months data in tables from jan 2008 to Sep 2008 .But i was getting only records that were present in the db tablesbut i would like the output to be :count_all date_format0 Jan 20080 Feb 20082 Mar 20081 Apr 20080 May 20084 Jun 200810 Jul 200812 Aug 2008can we get zeros or nil values for missing months queried between jan and sept ?thanks,Sri.. |
|
|
jsmith8858
Dr. Cross Join
7423 Posts |
Posted - 2008-09-08 : 10:25:19
|
| The easiest way is to create a permanent a table of Months in your database, and populate it with one row per month you will ever need to cover. (say, from 1/1/2000 up to 1/1/2100 or something like that). Then, you would simply select FROM your table of months, filtered on the date ranges you want to return, and LEFT OUTER JOIN to your existing data.Based on your usage of DATEFORMAT, I assume you are not using SQL Server, so consider asking your questions in a more appropriate forum. This is a Microsoft SQL Server website.- Jeffhttp://weblogs.sqlteam.com/JeffS |
 |
|
|
|
|
|
|
|
SQL-how to get missing months