Excel Result Vs DB Result

Question

Excel Result Vs DB Result

Viewing 15 posts - 16 through 30 (of 36 total)

You must be logged in to reply to this topic. Login to reply

Carl Federl One Orange Chip Points: 25384 More actions · Answer 1

Joe Celko wrote a solution back in November of 1996 titled "aggregate product function" and can be found in "SQL For Smarties"

Here are Joe Celko's remarks:

Here is a version of the aggregate product function in SQL. You will need to have the logarithm and exponential functions. They are not standards, but they are very common.
The idea is that there are three special cases - all positive numbers, one or more zeroes, and some negative numbers in the set. You can find out what your situation is with a quick test on the sign() of the minimum value in the set.
Within the case where you have negative numbers, there are two sub-cases: (1) an even number of negatives or (2) an odd number of negatives. You then need to apply some High School algebra to determine the sign of the final result.

-- The "NumberTable" is the same as "Tally"

SELECT CASE MIN (SIGN(nbr))

WHEN 1 THEN EXP(SUM(LN(nbr))) -- all positive numbers

WHEN 0 THEN 0.00 -- some zeroes

WHEN -1 -- some negative numbers

THEN (EXP(SUM(LN(ABS(nbr))))

* (CASE WHEN

MOD (SUM(ABS(SIGN(nbr)-1)/ 2)), 2) = 1

THEN -1.00 ELSE 1.00 END)

ELSE NULL END AS big_pi

FROM NumberTable;

SELECT CASE MIN(ABS(SIGN(nbr)))

WHEN 0 THEN 0.00 -- some zeroes

ELSE -- no zeroes

EXP(SUM(LOG(ABS(NULLIF(nbr, 0)))))

* CASE WHEN MOD (CAST(SUM

ABS(SIGN(nbr)-1)/2) AS INTEGER), 2)

= 1

THEN -1.00 ELSE 1.00 END

END AS big_pi

SQL = Scarcely Qualifies as a Language

karthik M One Orange Chip Points: 29139 More actions · Answer 2

rbarryyoung,

I am getting 'TRUNCATION ERROR OCCURED' message when i execute the code.

I think somewhere else data lenght is crossing the maximum limit. Please correct me if i am wrong.

Carl,

Joe's code looks complex, so i need some time to analyze it.Probably i will analyze it on my home PC (this weekend). Thanks for highlighting his method here.

Sergiy,

karthikeyan, it's a good test of how did you learn the lesson about Tally table.
Apparently, not very good. Because you cannot recognize the case where it may be use.

Tally table ? here ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 3

rbarryyoung,

Though i changed the table structure, I am getting the same error message.

create table PROD_issue

(

MID varchar(15),

Dt1 decimal(5,3),

Dt2 decimal(5,3),

Dt3 decimal(5,3),

Dt4 decimal(5,3),

Dt5 decimal(5,3),

Dt6 decimal(5,3),

Dt7 decimal(5,3),

Dt8 decimal(5,3),

Dt9 decimal(5,3),

Dt10 decimal(5,3),

Dt11 decimal(5,3),

Dt12 decimal(5,3)

)

go

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 4

Can you attach the output get from the query ?

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 5

I got it...:) Yes,Just i turn off the below one.

set arithabort off

is it a wise decision ?

I got the result now. It is matching with the excel report. so there is no difference between Excel output and DB output. Am i correct ?

So i think i have to check the procedure logic.

karthik

RBarryYoung SSC Guru Points: 143329 More actions · Answer 6

karthikeyan (8/29/2008)
I got it...:) Yes,Just i turn off the below one.
set arithabort off
is it a wise decision ?

I'll have to check, but I don't think that you should hav eto do it.

So i think i have to check the procedure logic.

Yes.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 7

Karthik: please post the exact error that you were getting.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

karthik M One Orange Chip Points: 29139 More actions · Answer 8

Truncation error occured.

Command has been aborted.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 9

rbarryyoung,

I got the above error message. Please check it.

karthik

Carl Federl One Orange Chip Points: 25384 More actions · Answer 10

Joe's code looks complex, so i need some time to analyze it.Probably i will analyze it on my home PC (this weekend). Thanks for highlighting his method here.[/code]

I misread you problem, since you need the product of the 12 columns within a row, so Joe Celko's solution is not applicable .

If instead, you need the product of a column across the rows , then Celko's solution is applicable. e.g. SUM(DT1) would add the values and PRODUCT(DT1) would multiple the values. Since SQL Server does not have a PRODUCT aggregate function, Celko's code is appropriate, which

1. Changes the decimal value to a logarithm value

2. Sums the logarithm values

3. Converts the logarithm sum back to decimal format.

The remainder of the algorithm determines:

Should the result be a positive or negative number

If any number is zero, then the result should be zero

Good Luck

SQL = Scarcely Qualifies as a Language

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

OK, I think that you are getting an overflow of the Decimal type range that you are using. That was OK when you were using Decimal(17,8), because that was probably a data error.

However, using a Decimal type of (5,3) is makes this a problem because its precision is way to small to be reliable for this kind of calculation. Thus you may be getting far more truncations now, with perfectly legitimate data. I believe that (9,5) is the minimum that you should use.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

karthik M One Orange Chip Points: 29139 More actions · Answer 12

rbarryyoung,

Thanks a lot ! I really appreciate your timely help !:)

I have checked the procedure and fixed the issue sucessfully. Also i got the appreciation mail from my manager. Because it is a extremely prioritized issue. I have mentioned the alternate approaches that i have tried (Excel Calculation Vs DB Calculation) to solve this issue in my email. He appreciated that one also.

Again, Thanks a lot ! 🙂

karthik

RBarryYoung SSC Guru Points: 143329 More actions · Answer 13

Glad we could help, Karthik.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1003873 More actions · Answer 14

Do like Excel does... do the calculations using FLOAT (Barry implied it with 100.0) and then round the answer to the correct number of decimal points for display. If the DT columns were all defined as FLOAT, you wouldn't have a problem with scale.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 15

Hi ALL,

Again i need to do the same calculation but there is some modification in the requirement.

Create table Perf

(

ID intger,

Perf_dt datetime,

Net_Perf decimal(16,8)

)

insert into Perf

select 1,'13/mar/2009',-0.5567

union all

select 1,'12/mar/2009',-0.7865

union all

select 1,'11/mar/2009',-0.5887

union all

select 1,'10/mar/2009',-0.5634

union all

select 1,'09/mar/2009',-0.1343

union all

select 1,'06/mar/2009',-0.3432

union all

select 1,'05/mar/2009',-0.5123

union all

select 1,'04/mar/2009',-0.5845

union all

select 1,'03/mar/2009',-0.5823

union all

select 1,'02/mar/2009',-0.5812

--

select 40,'13/mar/2009',-0.5567

union all

select 40,'12/mar/2009',-0.7865

union all

select 40,'11/mar/2009',-0.5887

union all

select 40,'10/mar/2009',-0.5634

union all

select 40,'09/mar/2009',-0.1343

union all

select 40,'06/mar/2009',-0.3432

union all

select 40,'05/mar/2009',-0.5123

union all

select 40,'04/mar/2009',-0.5845

union all

select 40,'03/mar/2009',-0.5823

union all

select 40,'02/mar/2009',-0.5812

I need to apply the same formula

(PRODUCT(1+B2:M2/100)-1)*100

Inputs are welcome!

karthik