| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
09-04-08, 08:55
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 11
|
|
SQL-how to get missing months
|
|
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
and getting output like this :
count_all date_format
2 Mar 2008
1 Apr 2008
4 Jun 2008
10 Jul 2008
12 Aug 2008
i 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 tables
but i would like the output to be :
count_all date_format
0 Jan 2008
0 Feb 2008
2 Mar 2008
1 Apr 2008
0 May 2008
4 Jun 2008
10 Jul 2008
12 Aug 2008
can we get zeros or nil values for missing months queried between jan and sept ?
thanks,
Srinath
|
|
09-04-08, 08:55
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 11
|
|
|
SQL-how to get missing months
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
and getting output like this :
count_all date_format
2 Mar 2008
1 Apr 2008
4 Jun 2008
10 Jul 2008
12 Aug 2008
i 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 tables
but i would like the output to be :
count_all date_format
0 Jan 2008
0 Feb 2008
2 Mar 2008
1 Apr 2008
0 May 2008
4 Jun 2008
10 Jul 2008
12 Aug 2008
can we get zeros or nil values for missing months queried between jan and sept ?
thanks,
Srinath
|
|
09-04-08, 10:15
|
|
Registered User
|
|
Join Date: Aug 2003
Location: Where the Surf Meets the Turf @Del Mar, CA
Posts: 6,410
|
|
|
|
you need to create a "calendar" table
__________________
You can lead some folks to knowledge, but you can not make them think.
The average person thinks he's above average!
For most folks, they don't know, what they don't know.
|
|
09-04-08, 11:51
|
|
Lost Boy
|
|
Join Date: Jan 2004
Location: Croatia, Europe
Posts: 3,629
|
|
Here's an example; I have created a table (its name is "test") which contains result of your current query (as you didn't provide your test case):
Code:
SQL> SELECT cnt, TO_CHAR(date_format, 'mon yyyy') df
2 FROM test
3 ORDER BY date_format;
CNT DF
---------- --------
2 mar 2008
1 apr 2008
4 jun 2008
10 jul 2008
12 aug 2008
As you'd like to complete months list, you could use UNION operator which would unite your result with a generated current year's calendar. LEVEL pseudocolumn is used to create this calendar; NOT IN will have to rely on your data (I used my "test" table).
Code:
SQL> SELECT y.cnt, TO_CHAR(y.date_format, 'mon yyyy') df
2 FROM (SELECT t.cnt, t.date_format
3 FROM TEST t
4 UNION
5 SELECT 0 cnt, x.datum
6 FROM (SELECT ADD_MONTHS(TRUNC(SYSDATE, 'yyyy'), LEVEL - 1) datum
7 FROM dual
8 CONNECT BY LEVEL <= 12
9 ) x
10 WHERE datum NOT IN (SELECT date_format FROM TEST)
11 AND datum <= (SELECT MAX(date_format) FROM TEST)
12 ) y
13 ORDER BY y.date_format;
CNT DF
---------- --------
0 jan 2008
0 feb 2008
2 mar 2008
1 apr 2008
0 may 2008
4 jun 2008
10 jul 2008
12 aug 2008
8 rows selected.
SQL>
|
|
09-04-08, 11:59
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Try a LEFT JOIN to a months/calendar table.
|
|
09-04-08, 12:04
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 11
|
|
hi georgev,
i was not using any months/calendar table as i was formating in to months name using date_format on my created_at column where it is type Date
thanks !
|
|
09-04-08, 12:09
|
|
Registered User
|
|
Join Date: Mar 2002
Location: Reading, UK
Posts: 1,123
|
|
Similar to Littlefoot's query, if you have to span a year boundary you could outer join your table to this subquery
Code:
select add_months(to_date(200801,'yyyymm'), rn-1)
from
(
SELECT ROWNUM AS rn
FROM dual
CONNECT BY ROWNUM <= (select months_between(to_date(200808,'yyyymm') , to_date(200801,'yyyymm'))+1 from dual)
)
Alan
|
|
09-04-08, 12:16
|
|
Registered User
|
|
Join Date: Sep 2008
Posts: 11
|
|
Hi,
i was not using any months/calendar table as i was formating in to months name using date_format on my created_at column where it is type Date.
SQL :
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
i am retreiving records by using only one SQL ,
joining tables users and user_general_info where created_at exists in users table.
thanks for your posts.
|
Last edited by srinath.a; 09-04-08 at 12:27.
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL-how to get missing months
