Select Statement Help - SQL Server Forums

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.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Select Statement Help

Author

Topic

lenredles
Starting Member

4 Posts

Posted - 2006-03-15 : 15:18:52

I am stuck on a fairly simple issue. I have the below statement, which evaluates each record then groups by month. It will then return a month total. This works flawlessly.

The problem is that not all of my people have totals for every month. When this happens, the months shift on my graph because only a certain amount of rows are returned.

What I need to do is add something to this statement to return 0 values for every month if there is not total for that month. This way my graph points will not shift down to spot 1 if the first total is in October. Of course that point should be at 10 not 1. Here is the statement... Thanks for the help in advance.

SELECT COUNT(*) AS total FROM gen WHERE gen.app_date BETWEEN '01/01/2005' AND '12/31/2005' AND gen.lo_rep = 'AE;'
GROUP BY MONTH(gen.app_date) ORDER BY MONTH(gen.app_date)

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-15 : 15:48:47

Try
Replacing Count(*) by Isnull(Count(*),0)

lenredles
Starting Member

4 Posts

Posted - 2006-03-15 : 15:53:58

Still only 2 rows returned unfortunately.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-15 : 15:57:01

Can u put some data & expected results

lenredles
Starting Member

4 Posts

Posted - 2006-03-15 : 16:09:20

The statement returns this...

8 --This is November
24 -- This is December

These now become points 1 and 2 on my line graph. The graph has 12 points, supposed to be Jan - Dec.

Desired Result would be the following Rows...

0 --Jan
0 --Feb etc...
0
0
0
0
0
0
0
0
8
24

Does this help?

jhermiz

3564 Posts

Posted - 2006-03-15 : 16:20:52

Put the months in another table and left join to them ?

Keeping the web experience alive -- [url]http://www.web-impulse.com[/url]

RS Blog -- [url]http://weblogs.sqlteam.com/jhermiz[/url]

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-03-15 : 16:46:44

Use the following and create the missing months & count as zero
http://www.mindsdoor.net/SQLTsql/FindGapsInSequence.html

Union all ur existing one with that

Select cnt, mnth from 
(Select count(*) as cnt, mnth from urTbl <with urconditions>
Union all
Select 0 as cnt, <missing months query>) as MainQry

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources