Select and select into calulation produces Different result ??? HELP

Question

Select and select into calulation produces Different result ??? HELP

rmurray 17656

SSC Rookie

Points: 37
More actions
July 13, 2016 at 1:31 am

#311699

I have a Calculation in a query that is producing two different results. when used in a query statement the result is 160.442873, However when I use a select into or Union statement the value returned is 81.825865.
This the calculation:
CASE WHEN stdformat = 'HR' THEN CAST(ProdStandard * BurdenRate * laborqty / (mfglotsize * runqty) AS float)
WHEN stdformat = 'PM' THEN CAST((burdenrate * laborqty) / (prodstandard * 60) AS float) ELSE CAST(burdenrate * laborqty / prodstandard AS float) END AS [std£]
and it uses the 'HR' Case.
Values are 5.5*28.1*10033/(18950*.51)

Viewing 5 posts - 1 through 4 (of 4 total)

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

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 1

rmurray 17656 (7/13/2016)
I have a Calculation in a query that is producing two different results. when used in a query statement the result is 160.442873, However when I use a select into or Union statement the value returned is 81.825865.
This the calculation:
CASE WHEN stdformat = 'HR' THEN CAST(ProdStandard * BurdenRate * laborqty / (mfglotsize * runqty) AS float)
WHEN stdformat = 'PM' THEN CAST((burdenrate * laborqty) / (prodstandard * 60) AS float) ELSE CAST(burdenrate * laborqty / prodstandard AS float) END AS [std£]
and it uses the 'HR' Case.
Values are 5.5*28.1*10033/(18950*.51)

This is the part of the query which is consistent and which you know to be correct, so the error is probably in the structure of the query. Can you post all three please? The plain query, the SELECT INTO query and the UNION query? Thanks.

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

alastair.beveridge SSCrazy Points: 2590 More actions · Answer 2

Your 2 results differ by a factor of 0.51, which is the value you think runqty has. Maybe this will help point to what is going on.

rmurray 17656 SSC Rookie Points: 37 More actions · Answer 3

The Select into is coming from the existing view/Query

So I have a stored view with the calculation shown in it (recording 160...) then I have a scheduled routine that runs this view in a union statement into a table. I've checked and confirmed that the union statement does not include another value relating to this. I've also created a insert into statement from the existing view that creates the incorrect (80..) value

rmurray 17656 SSC Rookie Points: 37 More actions · Answer 4

The Select into is coming from the existing view/Query

So I have a stored view with the calculation shown in it (recording 160...) then I have a scheduled routine that runs this view in a union statement into a table. I've checked and confirmed that the union statement does not include another value relating to this. I've also created a insert into statement from the existing view that creates the incorrect (80..) value