ADD MULTIPLY ERRORS PLEASE HELP

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 2000 Forums

SQL Server Development (2000)

ADD MULTIPLY ERRORS PLEASE HELP

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-04 : 10:52:18

Hello friends,
I 've trying to figure out the error on my calculation, but I can't figure out why?

colA colB colC colD
----- -------- -------- -------
1 1.32 2.8999 0
2 2 2922 22.33
3 98.22 0 0

SELECT colA,
1*ROUND(COALESCE(COLB,''),(1+1*COALESCE(COLC,'')*(1*SUM(COALESCE(colD,'')) AS COST_OF_EMP
FROM EMP
GROUP BY BLAH BLAH.....

The data returns is wrong, it didn't do any calculation from the row that has a zero.... the 1st and 3rd rows return all zero, only the 2nd row return right data.
Do you have any idea?

Thank you very much,
Jen.

The stupid question is the question you don't ask.
www.single123.com

X002548
Not Just a Number

15586 Posts

Posted - 2004-11-04 : 11:06:23

I'm suprised you didn't get an error....1st, you don't need coalesce in a sum...and if you did use it, you should supply zero, not an empty string...

Post the actual code...and the table DDL

Brett

8-)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-04 : 11:17:43

Sorry, I was confused with the character and numeric data types.
I fixed the code, but it still doesn't return the correct data.

SELECT colA,
1*ROUND(COALESCE(COLB,0),(1+1*COALESCE(COLC,0)*(1*SUM(COALESCE(colD,0))) AS COST_OF_EMP
FROM EMP
GROUP BY BLAH BLAH.....

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-04 : 11:49:11

What is the correct value supposed to be? Forget T-SQL, what mathematical formula are you trying to apply to these 3 columns? What are you trying to do with ROUND() function?

- Jeff

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-04 : 21:30:21

quote:
Originally posted by jennypretty

Sorry, I was confused with the character and numeric data types.
I fixed the code, but it still doesn't return the correct data.

SELECT colA,
1*ROUND(COALESCE(COLB,0),(1+1*COALESCE(COLC,0)*(1*SUM(COALESCE(colD,0))) AS COST_OF_EMP
FROM EMP
GROUP BY BLAH BLAH.....

The stupid question is the question you don't ask.
www.single123.com

if a number is multiplied by 1, doesn't it give you the number?

--------------------
keeping it simple...

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-05 : 10:40:08

Yes, you're right. I deleted #1, 'cause I migrated from Access so I am confused.
Jen.

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 10:55:05

so, do you have your solution?

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-05 : 12:48:40

Not yet. In Access query, it shows correct values. I use the same query in sql, then it doesn't return correct data. I don't know why.
Jen.

The stupid question is the question you don't ask.
www.single123.com

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 13:10:53

Again, what formula are you trying to calculate? in regular english and math, what results are you trying to get?

- Jeff

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-05 : 14:05:03

This is the code:

CREATE VIEW Emp_Hours AS
SELECT Emp_Data.Emp_Hours_ID,
Emp_Data.Emp_ID,
Emp_Data.Mod_ID,
Emp_Data.M_ID,
Sum(IsNull(Emp_Data.Emp_Hours,0) + IsNull(Emp_Hour1.EmpHoursTotal,0) + IsNull(Emp_Hour2.Emp_Hours,0)) AS Emp_Hours,
Sum(IsNull(Emp_Data.Emp_Labor,0) + IsNull(Emp_Hour1.LaborTotal,0) +
IsNull(Emp_Hour2.Emp_Labor,0)) AS Emp_Labor,
Sum(IsNull(Emp_Data.Emp_Travel,0) + IsNull(Emp_Hour2.Emp_Travel,0)) AS Emp_Travel,
Sum(IsNull(Emp_Data.Emp_Dc,0) +IsNull(Emp_Hour2.Emp_Dc,0)) AS Emp_Dc,
/* this part I use the same clause above ... */
/* since can't use ALIAS above to calculate ....*/
ROUND(COALESCE(Emp_Data.Emp_Cost,0),(1+COALESCE(Department.Dept_Charge,0)*(Sum(COALESCE(Emp_Data.Emp_Labor,0) +
COALESCE(Emp_Hour1.LaborTotal,0) +COALESCE(Emp_Hour2.Emp_Labor,0))+Sum(COALESCE(Emp_Data.Emp_Travel,0) +
COALESCE(Emp_Hour2.Emp_Travel,0))+Sum(COALESCE(Emp_Data.Emp_Dc,0) +COALESCE(Emp_Hour2.Emp_Dc,0))))) AS Emp_Cost

FROM (Department RIGHT JOIN Cont ON Department.Department_ID = Cont.Department_ID)
RIGHT JOIN (Assign RIGHT JOIN (Emp RIGHT JOIN ((Emp_Data LEFT JOIN Emp_Hour1 ON Emp_Data.Emp_Hours_ID = Emp_Hour1.Emp_Hours_ID)
LEFT JOIN Emp_Hour2 ON Emp_Data.Emp_Hours_ID = Emp_Hour2.Emp_Hours_ID) ON Emp.Emp_ID = Emp_Data.Emp_ID)
ON Assign.Assign_ID = Emp.Assign_ID) ON Cont.Cont_ID = Assign.Cont_ID
GROUP BY Emp_Data.Emp_Hours_ID, Emp_Data.Emp_ID, Emp_Data.Mod_ID, Emp_Data.M_ID, Emp_Data.Emp_Travel,
Emp_Data.Emp_Dc, Emp_Data.Emp_Cost, Department.Dept_Charge

Why Emp_Cost return wrong data, I mean lots of zeros.... In Access, no zeros.....

Thanks a lot.
Jenny.

The stupid question is the question you don't ask.
www.single123.com

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2004-11-05 : 15:16:51

Giving us the code isn't helping us understand what you want. You need to explain in plain words what you need to do by showing us your sample data and what the expected result set should be. So don't explain with code, but rather with words.

Tara

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-11-05 : 15:53:44

I give up ... this is getting way too frustrating ....

- Jeff

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-11-08 : 03:05:11

quote:
Originally posted by jsmith8858

I give up ... this is getting way too frustrating ....

- Jeff

ha ha ha, jennypretty, you will not get any help if you keep doing this.

again, if you want some help, post what you want to accomplish, some sort of an algorithm or atleast the mathematical formula.

pretty please?

--------------------
keeping it simple...

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2004-11-08 : 10:01:13

Hello guys,
Thanks for trying to help me but I am still confused about that too. That is an Access query and I converted it to sql view. I don't know clearly about why getting the results on the last column since they used lots of joins and GROUP BY clauses. That's why I don't post what my accomplishment is or algorithm....
Thanks, guys.
Please ignore this post.
Jenny.
Big thanks......

The stupid question is the question you don't ask.
www.single123.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources