Return Null Value as Zero - 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 2005 Forums

Transact-SQL (2005)

Return Null Value as Zero

Author

Topic

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-30 : 09:26:04

In this code below I want all the rows in the WL_Ranges field Weeks Range Title to show in order of Weeks Range. Using this query below it always just returns a value when it greater than zero, how can I get it to show all returns regardless is zero or not?


SELECT     TOP (100) PERCENT jez.WL_RANGES.[weeks range title], COUNT(jez.WL_DATA.[Weeks Range]) AS Expr1
FROM         jez.WL_DATA RIGHT OUTER JOIN
                      jez.WL_RANGES ON jez.WL_DATA.[Weeks Range] = jez.WL_RANGES.[weeks range]
WHERE     (jez.WL_DATA.[Waiting List Name] = 'PVL') AND (jez.WL_DATA.Month = 'September') AND (jez.WL_DATA.Year = '2008') AND 
                      (jez.WL_DATA.[Generated Date] = CONVERT(DATETIME, '2008-09-29 00:00:00', 102)) OR
                      (jez.WL_DATA.[Waiting List Name] IS NULL)
GROUP BY jez.WL_RANGES.[weeks range title], jez.WL_RANGES.[weeks range]
ORDER BY jez.WL_RANGES.[weeks range]

Where am I going wrong??

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 09:33:24

[code]SELECT r.[Weeks Range Title],
COUNT(d.[Weeks Range]) AS Items
FROM jez.WL_RANGES AS r
LEFT JOIN jez.WL_DATA AS d ON d.[Weeks Range] = r.[Weeks Range]
AND d.[Waiting List Name] = 'PVL'
AND d.Month = 'September'
AND d.Year = '2008'
AND d.[Generated Date] >= '20080929'
GROUP BY r.[Weeks Range Title]
ORDER BY r.[Weeks Range Title][/code]

E 12°55'05.63"
N 56°04'39.26"

JezLisle
Posting Yak Master

132 Posts

Posted - 2008-09-30 : 10:01:34

I know in this example I have used only 1 Waiting List name, but the majority of query I have need to use [Waiting List Name] IN 'PVL,PVW,PTR,PT2') could this be used in same way as code in above thread?

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-09-30 : 10:48:12

Yes, if all have the same Month/Year/Generated criterias.
You can write how many AND/OR as you like for JOIN as you can with WHERE.

The reason for haivng them on the OUTER JOIN table is for counting "NULLS".

E 12°55'05.63"
N 56°04'39.26"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources