Null value issue - 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 2012 Forums

Transact-SQL (2012)

Null value issue

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-03-12 : 08:03:09

I am getting a null value for something. below is where it is poping at. Below that is the whole code

sum((actualgovvalue + actualctrvalue ) / ( govvalue + ctrvalue )* 100)

SELECT [t1].[recordnum] [group3],
[t1].[idnum] [group4],
[t4].[teamname] [group5],
sum( govvalue + ctrvalue ) [BidHours],
sum( actualgovvalue + actualctrvalue ) [ActualHours],
SUM( govvalue + ctrvalue ) - sum( actualgovvalue + actualctrvalue) [RemainingHours],
sum((actualgovvalue + actualctrvalue ) / ( govvalue + ctrvalue )* 100) [plannedPercentage]
FROM tbltrkrecord t1
JOIN (SELECT r.id,
r.recordnum,
Row_number()
OVER (
partition BY recordnum
ORDER BY revnum DESC) row
FROM tbltrkrecord r
JOIN tbltrkstatus s
ON s.id = r.statusid
WHERE s.name <> 'Cancelled') t2
ON t2.row = 1
AND t2.id = t1.id
JOIN tbltrkrecord t3
ON t3.recordnum = t2.recordnum
LEFT JOIN (SELECT DISTINCT
tn1.recordnum,
tn3.name,
tn2.name
AS
teamname
,
tn2.teamid,
coalesce(tn4.govvalue,0) govvalue,
COALESCE (tn4.govvalue * y.govrate, 0) AS govcost,
coalesce(tn4.ctrvalue, 0) ctrvalue,
COALESCE (tn4.ctrvalue * y.ctrrate, 0) AS ctrcost,
coalesce(tn5.actualgovvalue, 0) actualgovvalue,
COALESCE (tn5.actualgovvalue * y.govrate, 0) AS actualgovcost,
coalesce(tn5.actualctrvalue,0) actualctrvalue,
COALESCE (tn5.actualctrvalue * y.ctrrate, 0) AS actualctrcost
FROM (SELECT recordnum,
Max(systemid) AS systemid,
Max(funcareaid) AS funcareaid,
Max(recordtypeid) AS recordtypeid,
Max(esttypeid) AS esttypeid,
Max(ratesyearid) AS year
FROM tbltrkrecord
GROUP BY recordnum) AS tn1
INNER JOIN (SELECT systemid,
id AS teamid,
name
FROM tbltrkteam
UNION
SELECT id AS systemid,
-1 AS teamid,
'UNASSIGNED TEAM' AS name
FROM tbltrksystem) AS tn2
ON tn2.systemid = tn1.systemid
INNER JOIN tbltrkestlabel AS tn3
ON tn3.esttemplateid = tn1.esttypeid
LEFT OUTER JOIN (SELECT r.recordnum,
l.subtask,
e.teamid,
Sum(p.govvalue) AS govvalue,
Sum(p.ctrvalue) AS ctrvalue
FROM tbltrkrecord AS r
INNER JOIN tbltrkteamest AS e
ON e.recordid = r.id
INNER JOIN tbltrkestphase AS p
ON p.teamestid = e.id
INNER JOIN tbltrkestlabel AS l
ON l.id = p.estlabelid
GROUP BY r.recordnum,
l.subtask,
e.teamid) AS tn4
ON tn4.recordnum = tn1.recordnum
AND tn4.subtask = tn3.subtask
AND tn4.teamid = tn2.teamid
LEFT OUTER JOIN (SELECT a.recordnum,
CASE
WHEN a.esttypeid = 1 THEN 'Imported Hours'
ELSE b.subtask
END AS subtask,
b.teamid,
Sum(b.actualgovvalue) AS actualgovvalue,
Sum(b.actualctrvalue) AS actualctrvalue
FROM (SELECT recordnum,
Max(esttypeid) AS esttypeid
FROM tbltrkrecord AS tblTrkRecord_1
GROUP BY recordnum) AS a
LEFT OUTER JOIN (SELECT t.name AS task,
s.name AS subtask,
v.teamid,
v.actualgovvalue,
v.actualctrvalue
FROM vwtrkwbshours AS v
INNER JOIN tbltask AS t
ON t.id = v.taskid
INNER JOIN tblsubtask AS s
ON s.id = v.subtaskid)
AS b
ON b.task = a.recordnum
GROUP BY a.recordnum,
CASE
WHEN a.esttypeid = 1 THEN 'Imported Hours'
ELSE b.subtask
END,
b.teamid) AS tn5
ON tn5.recordnum = tn1.recordnum
AND tn5.subtask = tn3.subtask
AND tn5.teamid = tn2.teamid
LEFT OUTER JOIN tbltrkratesyear AS y
ON y.systemid = tn1.systemid
AND ( y.funcareaid IS NULL
OR y.funcareaid = tn1.funcareaid )
AND ( y.recordtypeid IS NULL
OR y.recordtypeid = tn1.recordtypeid )
AND y.year = tn1.year
WHERE ( tn4.subtask IS NOT NULL )
OR ( tn5.subtask IS NOT NULL )) t4
ON t4.recordnum = t1.recordnum
WHERE (( t1.archived = 0 ))
GROUP BY [t1].[recordnum],
t1.recordnum,
[t1].[idnum],
[t4].[teamname]
ORDER BY t1.recordnum,
t1.idnum,
t4.teamname

Dave
Helixpoint Web Development
http://www.helixpoint.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 08:16:52

Are You getting NULL value for this column ?
sum((actualgovvalue + actualctrvalue ) / ( govvalue + ctrvalue )* 100)

or any Warning message you are getting? If yes...
Check this link ( First two points under Remarks
http://msdn.microsoft.com/en-us/library/ms190368.aspx
You will get to know the reason

--
Chandu

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-03-12 : 08:24:13

I believe so

Dave
Helixpoint Web Development
http://www.helixpoint.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 08:28:24

quote:
Originally posted by helixpoint

I believe so

Dave
Helixpoint Web Development
http://www.helixpoint.com

means warning message problem... right?

--
Chandu

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-03-12 : 08:50:28

Msg 8134, Level 16, State 1, Line 1
Divide by zero error encountered.
Warning: Null value is eliminated by an aggregate or other SET operation.

Possible zero?

Dave
Helixpoint Web Development
http://www.helixpoint.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 08:55:14

If these two values(govvalue, ctrvalue) are zero then you will obviously get that Divide by zero error encountered.

change these values....
coalesce(tn4.govvalue,0) govvalue,
coalesce(tn4.ctrvalue, 0) ctrvalue

--
Chandu

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-03-12 : 08:58:10

Change them????

Dave
Helixpoint Web Development
http://www.helixpoint.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-12 : 09:33:36

If (govvalue + ctrvalue) is zero then whats your calculation?

(CASE (govvalue + ctrvalue) WHEN 0 THEN ? ELSE (govvalue + ctrvalue) END)*100

--
Chandu

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2013-03-12 : 11:18:19

I tried to do this, but it seems to be evaluating the else???

[plannedPercentage] =
case when COALESCE(SUM( govvalue + ctrvalue),0) = 0 then 0
ELSE sum((actualgovvalue + actualctrvalue ) / ( govvalue + ctrvalue )* 100)
end

Dave
Helixpoint Web Development
http://www.helixpoint.com

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-03-13 : 01:08:27

Here what am doing is
If (govvalue + ctrvalue) = 0 then assign (govvalue + ctrvalue) = 1

sum((actualgovvalue + actualctrvalue ) / COALESCE(NULLIF(govvalue + ctrvalue, 0), 1)*100)

--
Chandu

UnemployedInOz
Yak Posting Veteran

54 Posts

Posted - 2013-03-15 : 08:06:48

-- be clarefull when * by 100 the result could loose the decimals and change the results, use 100.0

[plannedPercentage] = sum(actualgovvalue + actualctrvalue ) * 100.0/ (SUM( isnull(govvalue,0) + isnull(ctrvalue,0)))

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources