Calculating with NULLS in Join

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)

Calculating with NULLS in Join

Author

Topic

sharkie
Starting Member

6 Posts

Posted - 2007-11-28 : 06:19:31

Hi there

I have created a view which is created by joining several other views. To join them I have used Full Outer Joins since sometimes the value is NULL. I now want to create the Daily Input which is a total from the inputs view but this isn't working when one of the hundreds, fifties and thirties tables is NULL. Only gives a total when they all have a value. Otherwise the Daily Input is NULL. I have tried changing the null values to zero but this still isn't working.

Any advice on how to add this would be appreciated

Thanks

Here is the code i have

SELECT TOP 100 PERCENT h.DayCreated, h.[Year], h.[Month], SUM(i.Inputs) AS 'Daily Input', ISNULL(x.One_Fifties, 0) AS One_Fifties, ISNULL(h.Hundreds,0) AS Hundreds, ISNULL(f.Fifties, 0) AS Fifties, ISNULL(t.Thirties,0) AS Thirties
FROM dbo. FilmFinishedReels_150s x FULL OUTER JOIN
dbo. FilmFinshedReels_Hundreds h ON x.DayCreated = h.DayCreated AND x.[Year] = h.[Year] AND x.[Month] = h.[Month] FULL OUTER JOIN
dbo.FilmFinishedReels_Fifties f ON h.DayCreated = f.DayCreated AND h.[Year] = f.[Year] AND h.[Month] = f.[Month] FULL OUTER JOIN
dbo. FilmFinishedReels_Thirties t ON f.DayCreated = t.DayCreated AND f.[Year] = t.[Year] AND f.[Month] = t.[Month] FULL OUTER JOIN
dbo. FilmFinishedReels_Inputs i ON t.DayCreated = i.DayCreated AND t.[Year] = i.[Year] AND t.[Month] = i.[Month]
GROUP BY h.DayCreated, h.[Year], h.[Month], /*SUM(i.Inputs),*/ x.One_Fifties, h.Hundreds, f.Fifties, t.Thirties
ORDER BY h.DayCreated

PeterNeo
Constraint Violating Yak Guru

357 Posts

Posted - 2007-11-28 : 06:48:11

Try this,

SELECT TOP 100 PERCENT h.DayCreated, h.[Year], h.[Month], SUM(i.Inputs) AS 'Daily Input',
ISNULL(x.One_Fifties, 0) AS One_Fifties, ISNULL(h.Hundreds,0) AS Hundreds,
ISNULL(f.Fifties, 0) AS Fifties, ISNULL(t.Thirties,0) AS Thirties
FROM dbo. FilmFinishedReels_Inputs i
FULL OUTER JOIN dbo. FilmFinishedReels_Thirties t ON t.DayCreated = i.DayCreated
AND t.[Year] = i.[Year] AND t.[Month] = i.[Month]
FULL OUTER JOIN dbo.FilmFinishedReels_Fifties f ON f.DayCreated = t.DayCreated
AND f.[Year] = t.[Year] AND f.[Month] = t.[Month]
FULL OUTER JOIN dbo.FilmFinshedReels_Hundreds h ON h.DayCreated = f.DayCreated
AND h.[Year] = f.[Year] AND h.[Month] = f.[Month]
FULL OUTER JOIN dbo.FilmFinishedReels_150s x ON x.DayCreated = h.DayCreated
AND x.[Year] = h.[Year] AND x.[Month] = h.[Month]
GROUP BY h.DayCreated, h.[Year], h.[Month], /*SUM(i.Inputs),*/
x.One_Fifties, h.Hundreds, f.Fifties, t.Thirties
ORDER BY h.DayCreated

sharkie
Starting Member

6 Posts

Posted - 2007-11-28 : 07:00:05

I'm afraid that still gives me the same result. The daily input is still NULL if either of them is NULL.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2007-11-28 : 07:17:04

How about this?


SELECT TOP 100 PERCENT 
	i.DayCreated, 
	i.[Year], 
	i.[Month], 
	SUM(i.Inputs) AS 'Daily Input', 
	ISNULL(x.One_Fifties, 0) AS One_Fifties, 
	ISNULL(h.Hundreds,0) AS Hundreds, 
	ISNULL(f.Fifties, 0) AS Fifties, 
	ISNULL(t.Thirties,0) AS Thirties
FROM dbo. FilmFinishedReels_Inputs i 
LEFT JOIN dbo. FilmFinishedReels_Thirties t 
	ON t.DayCreated = i.DayCreated 
	AND t.[Year] = i.[Year] AND t.[Month] = i.[Month]
LEFT JOIN dbo.FilmFinishedReels_Fifties f 
	ON f.DayCreated = i.DayCreated 
	AND f.[Year] = i.[Year] AND f.[Month] = i.[Month] 
LEFT JOIN dbo.FilmFinshedReels_Hundreds h 
	ON h.DayCreated = i.DayCreated 
	AND h.[Year] = i.[Year] AND h.[Month] = i.[Month] 
LEFT JOIN dbo.FilmFinishedReels_150s x 
	ON x.DayCreated = i.DayCreated 
	AND x.[Year] = i.[Year] AND x.[Month] = i.[Month] 
GROUP BY i.DayCreated, i.[Year], i.[Month], /*SUM(i.Inputs),*/ 
ISNULL(x.One_Fifties, 0), ISNULL(h.Hundreds,0), ISNULL(f.Fifties, 0), ISNULL(t.Thirties,0)
ORDER BY i.DayCreated

By setting your group and select to your hundreds, unless you have hundreds, you will not get any result back.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-28 : 07:17:17

how about sum(isnull(i.Inputs,0))

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

sharkie
Starting Member

6 Posts

Posted - 2007-11-28 : 08:21:32

Thanks RickD that is it working now. Wasa silly mistake.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2007-11-29 : 07:20:18

bah. 13 seconds late again.

[Signature]For fast help, follow this link:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25.aspx
Learn SQL
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2007-11-30 : 00:28:15

As an aside, if the code you have is for a view, you should not be doing order by in views. It's meaningless and does not work in SQL Server 2005, even with the TOP 100 PERCENT hack. The order is determined by the order by clause in the outermost select.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources