August 28, 2008 at 1:22 am
Hi All,
An End user complained me that the data from DB is not correct, he also gave me one excel sheet which contains the data calculated manually and highlighted the difference in RED colour. Herewith i have attached the file.
I started my work by analyzing the stored procedure from the scratch also i used PRINT statement to see the result, But suddenly i though to import the excel sheet data into a table and applying the same formula (used in that file) to know the output from DB side. I hope EXCEL and DB handle the precision and decimals differently, thats why i started to do this analysis.
Here the table structure.
CREATE TABLE dbo.PROD_issue
(
MID varchar(15) NOT NULL,
Dt1 decimal(17,8) NOT NULL,
Dt2 decimal(17,8) NOT NULL,
Dt3 decimal(17,8) NOT NULL,
Dt4 decimal(17,8) NOT NULL,
Dt5 decimal(17,8) NOT NULL,
Dt6 decimal(17,8) NOT NULL,
Dt7 decimal(17,8) NOT NULL,
Dt8 decimal(17,8) NOT NULL,
Dt9 decimal(17,8) NOT NULL,
Dt10 decimal(17,8) NOT NULL,
Dt11 decimal(17,8) NOT NULL,
Dt12 decimal(17,8) NOT NULL
)
Go
insert into PROD_issue values('11',-3.07,4.07,-2.03,-1.71,2.47,1.24,2.36,2.14,3.07,-2.3,4.17,3.12)
insert into PROD_issue values('12',-2.41,4.43,-1.61,-2.04,1.57,1.84,4.29,2.69,3.3,-3.69,4.16,4.98)
insert into PROD_issue values('130',-2.73,0.21,-1.51,-0.78,4.73,-0.04,3.42,0.07,0.97,-0.84,4.1,-0.02)
insert into PROD_issue values('145',-0.99,-0.19,-1.59,-1.02,3.01,-0.64,3.11,-1.24,-0.32,-0.6,2.68,-0.45)
insert into PROD_issue values('146',-1.39,-0.14,-2.08,-1.88,3.9,-1.03,4.43,-2.16,-0.23,-0.68,3.62,-0.83)
insert into PROD_issue values('165',-0.891,3.264,-0.728,-2.828,2.357,0.631,5.577,-0.348,2.475,-2.132,3.461,0.848)
insert into PROD_issue values('175',-1.74,0.12,-1.9,-2.95,2.17,1.2,2.85,-0.47,1.05,-2.67,3.11,0.4)
insert into PROD_issue values('177',-3.34,0.79,-0.63,-4.34,5.07,0.69,4.65,-0.68,2.64,-2.29,4.42,1.38)
insert into PROD_issue values('185',-2.45,2.63,-0.9,-2.03,2.83,0.79,3.35,-0.86,0.96,-1.56,3.8,0.12)
insert into PROD_issue values('18',-1.664,2.462,-1.493,-3.861,5.11,5.01,6.01,-0.24,1.84,-2.92,6.22,0.13)
I want to convert the excel formula =(PRODUCT(1+B2:M2/100)-1)*100. How ?
Actually i tried the below one.
Select MID, (1+ (SUM(Dt1+Dt2+Dt3+Dt3+Dt4+Dt5+Dt6+Dt7+Dt8+Dt9+Dt10+Dt11+Dt12)/100)-1)*100
from Prod_Issue
group by MID
But it gave me the wrong output.
1) i want to know whether i have converted the formula correctly or not.If not, can i get the correct formula ?
2) is there any difference between doing calculation in Excel and SQL ? I think so.
3) generally, How to approach this kind of issue ?
4) Whether my approach is correct or not.
Inputs are highly appreciated !
karthik
August 28, 2008 at 2:10 am
I don't see any implementation of PRODUCT in your query.
_____________
Code for TallyGenerator
August 28, 2008 at 3:29 am
PRODUCT is an excel function. I want to implement it in SQL.
karthik
August 28, 2008 at 3:29 am
Thats why i used SUM function.
karthik
August 28, 2008 at 4:50 am
You probably need to open help on Excel (F1) and read what PRODUCT is.
_____________
Code for TallyGenerator
August 28, 2008 at 6:37 am
I already read about PRODUCT. It is used to multiply numbers or range of numbers.
http://spreadsheets.about.com/od/excelfunctions/qt/product_funtion.htm
But how to implement it in SQL ?
karthik
August 28, 2008 at 6:49 am
Any help would be appreciated !
karthik
August 28, 2008 at 9:35 am
Frankly I would ask the person using the PRODUCT() function to explain what is happening in the expression used. It is unclear what the result/s should be. What is the order of operation? why is there a product provided on a single result? Or is there more than one result coming from that expression? If so what, how, and when?
Its just multiplication and should be easy to explain.
Right?
August 28, 2008 at 10:20 am
Karthik: Product multiplies, Sum adds. What you want is something like this:
Select MID,
( 1.0+Dt1/100.0-1.0
+ 1.0+Dt2/100.0-1.0
+ 1.0+Dt3/100.0-1.0
+ 1.0+Dt4/100.0-1.0
+ 1.0+Dt5/100.0-1.0
+ 1.0+Dt6/100.0-1.0
+ 1.0+Dt7/100.0-1.0
+ 1.0+Dt8/100.0-1.0
+ 1.0+Dt9/100.0-1.0
+ 1.0+Dt10/100.0-1.0
+ 1.0+Dt11/100.0-1.0
+ 1.0+Dt12/100.0-1.0) * 100.0
from Prod_Issue
[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]
August 28, 2008 at 10:39 am
Heh, I made the same mistakes. Here is a correct version:
Select MID,
(((1.0+Dt1/100.0)
*(1.0+Dt2/100.0)
*(1.0+Dt3/100.0)
*(1.0+Dt4/100.0)
*(1.0+Dt5/100.0)
*(1.0+Dt6/100.0)
*(1.0+Dt7/100.0)
*(1.0+Dt8/100.0)
*(1.0+Dt9/100.0)
*(1.0+Dt10/100.0)
*(1.0+Dt11/100.0)
*(1.0+Dt12/100.0))-1.0) * 100.0
from Prod_Issue
This gives the same results as the Excel.
[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]
August 28, 2008 at 2:13 pm
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.
_____________
Code for TallyGenerator
August 28, 2008 at 2:35 pm
Hmmm, I can't see it either Sergiy. Unless you mean with Dynamic SQL?
[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]
August 28, 2008 at 2:48 pm
rbarryyoung (8/28/2008)
Hmmm, I can't see it either Sergiy. Unless you mean with Dynamic SQL?
Well, if to copy Excel tables to SQL Server then yes, you'll need dynamic SQL and all other freaky tricks.
But if you're aware of data normalization, and if you build your tables according to the rules - then where's the problem?
_____________
Code for TallyGenerator
August 28, 2008 at 3:16 pm
I am just saying, I do not see where a Tally table comes in? The query that I already posted works fine without it. And I am not sure where you would use it?
[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]
August 28, 2008 at 3:42 pm
I'd use it for cyclic multiplying.
SELECT @product = @product * Value
FROM Table
Just like for string concatenation.
_____________
Code for TallyGenerator
Viewing 15 posts - 1 through 15 (of 36 total)
You must be logged in to reply to this topic. Login to reply