Eliminating NULLs in the final result set

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)

Eliminating NULLs in the final result set

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 10:36:37

Hi

I am using SQL 2005 and Crystal reports. My final result set from the DB returns NULL value for Maturity for a group of Dealers.

I group by Dealers and then by Maturity.

For maturity, I call a function called Ytm. which gives month range

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

ALTER FUNCTION [dbo].[YtM]
(
@TradeDate datetime,
@Maturity datetime
)
RETURNS DECIMAL(10,5)
AS
BEGIN

DECLARE @Result DECIMAL(10,5)

SELECT @Result = CONVERT ( DECIMAL(10,5), DATEDIFF ( dd, @TradeDate, @Maturity ) / 365.00 )

RETURN(@Result)
END

For Maturity, I assign the below using CASE.
Maturity = CASE
WHEN dbo.YtM(inq.InquiryDate, iss.Maturity) <=1 THEN '<=1 Year'
WHEN dbo.YtM(inq.InquiryDate, iss.Maturity) >1 and dbo.YtM(trd.TradeDate, iss.Maturity) <=3 THEN '>1 Year & <=3 Years'
WHEN dbo.YtM(inq.InquiryDate, iss.Maturity) >3 and dbo.YtM(trd.TradeDate, iss.Maturity) <=5 THEN '>3 Years & <=5 Years'
WHEN dbo.YtM(inq.InquiryDate, iss.Maturity) >5 THEN '>5 Years'
END
Is there a way I can eliminate the NULL values for Maturity using where clause?

I tried something like below using a where clause but that gave me no data at all.

where dlr.Maturity != NULL

SELECT
DISTINCT
dlr.ProductID AS ProductID,
dlr.Product AS Product,
SizeBucket,
avgdlr.DealerBucket AS Dealer,
dlr.Maturity As Maturity,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount AS HitRatioCount,
TradeVolume,
dlr.HitRatioVolume AS HitRatioVolume,
avgdlr.AvgDlrCnt AS AvgDealerCount,
avgdlr.AvgRspCntWithLevel AS AvgResponseCount,
@ColumnValues AS ProductName,
@ClientDesc AS TypeofClient,
@ProductDesc As ProductType,
@ClientValues AS ClientTypeName

from
#DealerTemp dlr

INNER JOIN
#AvgDealerResponse avgdlr
ON
avgdlr.ProductID = dlr.ProductID
group by
dlr.SizeBucket,
dlr.ProductID,
dlr.Product,
avgdlr.AvgDlrCnt,
avgdlr.AvgRspCntWithLevel,
avgdlr.DealerBucket,
dlr.Maturity,
InquiryCount,
InquiryVolume,
TradeCount,
dlr.HitRatioCount,
TradeVolume,
dlr.HitRatioVolume
order by dlr.ProductID
GO
example of results

Dealer Maturity InquiryCount InquiryVol
>=1 & <=5 Dealers NULL 5552 1083683

>=6 & <=10 Dealers NULL 5552 1083683

Thanks for your help

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 10:44:36

The temp table that the final result is selected from has NULLS for Maturity.

select * from #DealerTemp
where TradeCount = 0
and Maturity = NULL

For example I have the below result sets and Maturity is the last column. I need to eliminate the rows that have Maturity as NULL.

Thanks

High Grade 1 5552 1083683 0 0 1 0 0 NULL
Floating Rate Notes 1 82 16240 0 0 2 0 0 NULL
Emerging Markets 1 502 139628 0 0 3 0 0 NULL
High Yield 1 116 21250 0 0 4 0 0 NULL
US Agency 1 237 39799 0 0 6 0 0 NULL

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2010-04-01 : 10:49:00

[code]
select * from #DealerTemp
where TradeCount = 0
and Maturity IS NOT NULL[/code]

Harsh Athalye
http://www.letsgeek.net/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources