Not sure why I am getting NULLS

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)

Not sure why I am getting NULLS

Author

Topic

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-01 : 14:37:31

Hi,

I need to understand why my logic returns NULLS. I have the declaration and query below. I could eliminate the NULLS by deleting from temp table but NULLS should not show in the first place.

DECLARE
@StartDate datetime,
@EndDate datetime,
@ClientType varchar(50),
@Product varchar(50),
ProtocolID
@ListID varchar(50),
@IsBuy varchar(20),
@SizeBucket varchar(100),
@Maturity varchar(100)

SET @StartDate = '20100101'
SET @EndDate = '20100201'
SET @ClientType = '154246'
SET @Product = '154247'
SET @ListID = 'All Inquiries'
SET @IsBuy = 'Bid/Offer'
SET @SizeBucket = '<=1000'
SET @Maturity = '>1 & <=3'

declare @LowMaturity DECIMAL(10,5),--int,
@HighMaturity DECIMAL(10,5)--int

SET @LowMaturity = case @Maturity when '<=1' then 0
when 'All Maturity' then 0.00000
when '>1 & <=3' then 1.00001
when '>3 & <=5' then 3.00001
else 5.01
end

SET @HighMaturity = case @Maturity when '<=1' then 1
when 'All Maturity' then 9999
when '>1 & <=3' then 3
when '>3 & <=5' then 5
else 9999
end

SELECT
DISTINCT
Product = prd.Description,
SizeBucket = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
InquiryCount = COUNT(DISTINCT inq.MISInquiryID),
InquiryVolume = ROUND(SUM(iql.USDSize),0),
TradeCount = COUNT(DISTINCT trd.MISInquiryID),
TradeVolume = ROUND(SUM(IsNull(trd.USDSize,0)),0),
ProductID = iql.ProductID,
HitRatioCount = cast(COUNT(DISTINCT trd.MISInquiryID)* 100.0/COUNT(DISTINCT inq.MISInquiryID) as decimal(10,1)),
HitRatioVolume = cast(SUM(IsNull(trd.USDSize,0))* 100.0/SUM(iql.USDSize) as decimal(10,1)),
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

INTO
#DealerTemp

FROM
Inquiry inq

INNER JOIN
InquiryLeg iql
ON
iql.MISInquiryID = inq.MISInquiryID

LEFT JOIN
Trade trd
ON
trd.MISInquiryID = iql.MISInquiryID
and trd.LegSequence = iql.LegSequence
and trd.IsVolume = 1

INNER JOIN
Product prd
ON
prd.ProductID = iql.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and iql.USDSize between @LowSize and @HighSize
and dbo.YtM(inq.InquiryDate, iss.Maturity) between @LowMaturity and @HighMaturity

GROUP BY
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
iql.ProductID,
prd.Description,
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

ORDER BY iql.ProductID
select * from #DealerTemp

declare @verbose int
set @verbose = 5
if @verbose > 0
Begin
select @LowSize
select @HighSize
select @LowMaturity
select @HighMaturity
End

For example results appear as
below where the last column is the Maturity column. For <= 1 Year I do not get NULLS but for the rest I do.

Also I have a funtion as below which is a DECIMAL(10,5) type.

High Grade 1 2483 476387 0 0 1 0 0 NULL
High Grade 1 6254 1292434 6254 1292399 1 100 100 >1 Year & <=3 Years
Floating Rate Notes 1 63 10417 0 0 2 0 0 NULL

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

I have taken a long time figuring out but in vain. I was told to use the function as another approac was giving large numbers in the result set.

I values passed for @LowMaturity and @HighMaturity seem fine which I checked already.

Thanks for your help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-04-01 : 14:42:14

try including inq.InquiryDate,iss.Maturity in last select statement first and see if they're having correct values you expect

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-02 : 09:24:17

Hi checked the Inquiry and Maturity dates.

For the same set of Maturity Dates in groups of 10, I have a different Inquiry Date. Please see the last two columns. Having it as a datetime distinguishes the time. Could this be the cause of my NULLS?

I checked the variable values for the range that I am passing example for >1 & <=3 , LowMaturity = 1.01 and High Maturity = 3.00001. I should get one row of data for this range and not the additional row with a NULL value.

Thanks for your help

High Grade 1 1 50.00 0 0.00 1 0.0 0.0 2011-01-12 00:00:00.000 2010-01-04 14:03:57.000
High Grade 1 1 50.00 1 50.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-04 14:09:31.000
High Grade 1 1 400.00 1 400.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-05 11:33:44.000
High Grade 1 1 500.00 1 500.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-06 13:26:06.000
High Grade 1 1 255.00 1 255.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-07 10:30:05.000
High Grade 1 1 50.00 1 50.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-07 14:40:08.000
High Grade 1 1 75.00 1 75.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-08 13:40:25.000
High Grade 1 1 250.00 1 250.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-08 13:57:58.000
High Grade 1 1 275.00 0 0.00 1 0.0 0.0 2011-01-12 00:00:00.000 2010-01-08 14:32:36.000
High Grade 1 1 50.00 1 50.00 1 100.0 100.0 2011-01-12 00:00:00.000 2010-01-08 15:54:12.000
High Grade 1 1 50.00 0 0.00 1 0.0 0.0 2011-01-12 00:00:00.000 2010-01-08 16:23:08.000

sqlnovice123
Constraint Violating Yak Guru

262 Posts

Posted - 2010-04-02 : 10:32:02

Also, I would like to add:

I do not get NULLS for <=1 and >5. The range 0 - 1 and 5.01 - 9999 works for the Low and High Maturity ranges.

Could this be related to data stored in some tables? Or are my ranges incorrect? The type is DECIMAL(10,5) for the function that I am using and the same function works fine in anotehr query that I use to insert in a temp table. The only difference is that the working query joins to three more tables and is filtered by two additional conditions.

This is the other query that does not produce NULLS and uses the same Maturity range and same function.

SELECT DISTINCT
ProductID = iql.ProductID,
Product = prd.Description,
SizeBucket = CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
DealerCnt = COUNT(DISTINCT res.CPPivotalCompanyID),
ResponseCnt = COUNT(DISTINCT CASE WHEN rsl.CPLevel IS NOT NULL THEN res.CPPivotalCompanyID END),
IsBuy = @IsBuy,
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,
/*Maturity = CASE
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) <=1 THEN '<=1 Year'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >1 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=3 THEN '>1 Year & <=3 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >3 AND ABS(year(iss.Maturity) - year(inq.InquiryDate))<=5 THEN '>3 Years & <=5 Years'
WHEN ABS(year(iss.Maturity) - year(inq.InquiryDate)) >5 THEN '>5 Years'
END,*/
ListID = inq.InquiryListID,
DealerBucket = CASE
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) >=1 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=5 THEN '>=1 & <=5 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 5 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=10 THEN '>=6 & <=10 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 10 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=15 THEN '>=11 & <=15 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 15 AND COUNT(DISTINCT res.CPPivotalCompanyID) <=20 THEN '>=16 & <=20 Dealers'
WHEN COUNT(DISTINCT res.CPPivotalCompanyID) > 20 THEN '>20 Dealers'
END

INTO #ResponseCnt
FROM
Inquiry inq

INNER JOIN
InquiryLeg iql
ON
inq.MISInquiryID = iql.MISInquiryID

LEFT JOIN
Trade trd
ON
iql.MISInquiryID = trd.MISInquiryID
and iql.LegSequence = trd.LegSequence
and trd.IsVolume = 1

INNER JOIN
Product prd
ON
iql.ProductID = prd.ProductID

INNER JOIN
#ProductTBL prdtbl

ON
prdtbl.ProductID = prd.ProductID

INNER JOIN
Issue iss
ON
iss.MISIssueID = iql.InstrumentMISIssueID

INNER JOIN
Response res
ON
res.MISInquiryID = iql.MISInquiryID
INNER JOIN
ResponseLeg rsl
ON
rsl.MISResponseID = res.MISResponseID
and rsl.MISInquiryID = iql.MISInquiryID
and rsl.LegSequence = iql.LegSequence
INNER JOIN
#ClientTBL clnttbl

ON
clnttbl.ClientID = trd.PPivotalCompanyID
INNER JOIN
pivotal..Company dlr
ON
dlr.Company_Id = res.CPPivotalCompanyID

WHERE
iql.IsVolume = 1
and inq.IsVolume = 1
and inq.InquiryDate BETWEEN @StartDate and @EndOfEndDate
and inq.InquiryType = 'F'
and trd.IsBuy = CASE @IsBuy WHEN 'Bid' THEN 1 WHEN 'Offer' THEN 0 ELSE trd.IsBuy END
and iql.USDSize between @LowSize and @HighSize
and dbo.YtM(inq.InquiryDate, iss.Maturity) between @LowMaturity and @HighMaturity
--and ABS(year(iss.Maturity) - year(inq.InquiryDate))between @LowMaturity and @HighMaturity

GROUP BY
iql.ProductID,
prd.Description,
CASE
WHEN iql.USDSize <= 1000 THEN 1
WHEN iql.USDSize > 1000 THEN 2
END,
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

inq.InquiryListID

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources