SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2012 Forums
 Transact-SQL (2012)
 Null value issue
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

helixpoint
Constraint Violating Yak Guru

278 Posts

Posted - 03/12/2013 :  08:03:09  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/12/2013 :  08:16:52  Show Profile  Reply with Quote
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
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

278 Posts

Posted - 03/12/2013 :  08:24:13  Show Profile  Reply with Quote
I believe so

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/12/2013 :  08:28:24  Show Profile  Reply with Quote
quote:
Originally posted by helixpoint

I believe so

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


means warning message problem... right?

--
Chandu
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

278 Posts

Posted - 03/12/2013 :  08:50:28  Show Profile  Reply with Quote
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

Edited by - helixpoint on 03/12/2013 08:51:01
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/12/2013 :  08:55:14  Show Profile  Reply with Quote
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
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

278 Posts

Posted - 03/12/2013 :  08:58:10  Show Profile  Reply with Quote
Change them????

Dave
Helixpoint Web Development
http://www.helixpoint.com
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/12/2013 :  09:33:36  Show Profile  Reply with Quote
If (govvalue + ctrvalue) is zero then whats your calculation?

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


--
Chandu
Go to Top of Page

helixpoint
Constraint Violating Yak Guru

278 Posts

Posted - 03/12/2013 :  11:18:19  Show Profile  Reply with Quote
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
Go to Top of Page

bandi
Flowing Fount of Yak Knowledge

India
2217 Posts

Posted - 03/13/2013 :  01:08:27  Show Profile  Reply with Quote
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
Go to Top of Page

UnemployedInOz
Yak Posting Veteran

Australia
54 Posts

Posted - 03/15/2013 :  08:06:48  Show Profile  Reply with Quote
-- 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)))
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.45 seconds. Powered By: Snitz Forums 2000