| Author |
 Topic  |
|
|
helixpoint
Constraint Violating Yak Guru
250 Posts |
 Posted - 03/12/2013 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/12/2013 : 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
250 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/12/2013 : 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
250 Posts |
Posted - 03/12/2013 : 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 |
Edited by - helixpoint on 03/12/2013 08:51:01 |
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/12/2013 : 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
250 Posts |
|
|
bandi
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/12/2013 : 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
250 Posts |
Posted - 03/12/2013 : 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
Flowing Fount of Yak Knowledge
India
1451 Posts |
Posted - 03/13/2013 : 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
Australia
52 Posts |
Posted - 03/15/2013 : 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)))
|
|
|
| |
Topic |
|
|
|
Null value issue
