SUM & ROUND FUNCTIONS help PLEASE !!!

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)

SUM & ROUND FUNCTIONS help PLEASE !!!

Author

Topic

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-06 : 10:29:51

Hello friends,
I am trying to convert an Access query to SQL View. It worked, but doesn't return the correct data. Can you please take a look at my code to see why incorrect data?

MS Access query:

round(1*Sum(IIf(IsNull(job.subjob_time),
IIf(IsNull(Subjob_time_lc.SubjobtimeTotal),
IIf(IsNull(Subjob_time_del.Subjob_time),0,Subjob_time_del.Subjob_time),Subjob_time_lc.SubjobtimeTotal),job.Subjob_time])),2) AS Subjob_time,

sql server:

1*sum(isnull(job.subjob_time,0)+isNull(subjob_time_lc.subjobtimetotal,0)+isNull(subjob_time_del.subjob_time,0)) as subjob_time

Thanks,
Jenny.

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 10:36:58

Try this one:

ROUND(SUM(ISNULL(job.subjob_time, ISNULL(Subjob_time_lc.SubjobtimeTotal, ISNULL(Subjob_time_del.Subjob_time, 0)))), 2)

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-06 : 11:07:57

woh, it worked. It return correct data now.
Can you please tell me why to have '0' before the four )))) but not for other fields???
Thanks.

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

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-06 : 11:34:42

Hello,
Big thanks, I have one more question:
I am trying to convert an Access query to SQL View. It worked, but doesn't return the correct data. Can you please take a look at my code to see why incorrect data?
In fact, it returns many zeros....

Access query:
round(IIf(IsNull(job.total_cost), (1+1*IIf(IsNull(office.fees),0,office.fees))*(money+t_money+o_money),job.total_cost),2) AS total_cost

sql server:
(1+isnull(office.fees,0) *isnull(money,0) + isnull(t_money,0) + isnull(o_money,0), job.total_cost,0)) as total_cost

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 11:37:54

I just translated the MS Access Query. Basically I translated

IIf(IsNull(<Column1>), <Column2>, <Column1>)

to

ISNULL(<Column1>, <Column2>)

and that did the trick.

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-06 : 15:18:21

Hello, this is what I did as you said but it generated an error "The isnull function requires 2 arguments".
round(isnull(job.total_cost), (1+IsNull(office.fees,0))*(isnull(money+t_money+o_money)),2) AS total_cost

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 15:44:21

Try this one:

round(isnull(job.total_cost, (1+IsNull(office.fees,0))*(isnull(money+t_money+o_money,0),2)) AS total_cost

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-06 : 16:55:32

It worked but incorrect data return from the access query. I don't know why.

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-06 : 17:03:13

Here's what I understand with what the code is doing:

IF job.total_cost is not null
THEN job.total_cost
ELSE (1 + office.fees) * (money + t_money + o_money)

Is this what you are expecting? What was the original formula in MS Access?

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-07 : 11:37:07

This is the original Access query:
round(IIf(IsNull(job.total_cost), (1+1*IIf(IsNull(office.fees),0,office.fees))*(money+t_money+o_money),job.total_cost),2) AS total_cost

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-07 : 11:49:33

The SQL statement I previously sent matches this Access query. Can you give some sample data and the result given by Access and SQL Server.

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-07 : 15:31:16

Access query:
money t_money o_money total_cost
----- ------- ------- ----------
186,289.75 11,379.7 0 202,611.19

sql server:
money t_money o_money total_cost
----- ------- ------- ----------
186,289.75 11,379.7 0 393,558.18

office.fees is .025

Can you help please?

Jenny.

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-07 : 15:57:11

Can you post your SQL Server statement.

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-07 : 16:25:52

Here is my sql stmt:
isnull(job.total_cost,(1+isnull(office.fees,0))*(isnull(money+t_money+o_money),0))) as total_cost

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

rfrancisco
Yak Posting Veteran

95 Posts

Posted - 2005-04-07 : 16:50:51

Check if the job.total_cost is null. If is not null, then that's where the problem is. The query will only perform the calculation if job.total_cost is null. If it is not null, then it will return the value of job.total_cost.

If you want to override the value of job.total_cost, then remove the last isnull. The query will now look like this:

round((1+IsNull(office.fees,0))*isnull(money+t_money+o_money,0),2) AS total_cost

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-08 : 08:48:52

I got a new error "Line 2: Incorrect syntax near '0'.
I don't know why?

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

jennypretty
Yak Posting Veteran

96 Posts

Posted - 2005-04-08 : 08:51:01

WOH WOH WOH, IT WORKED.
BIG THANKS.
JENNY.

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