September 12, 2010 at 8:42 am
Hi
My problem. I have a tank that contains multiple feedstock types. I have a sale from the tank of 186.436
I need to know how this sale volume is split by the feedstock types, so I use this function (a snippet of the full function):
CREATE FUNCTION fnTb_SUM_TANK_BY_FEEDSTOCK
(
@TANK_ID INT,
@UNIT_ID INT
)
AS
SELECT
FS.TANK_ID,
FS.UNIT_ID,
FS.Feedstock_Type_Code,
FS.QTY / FS.TANK_TOTAL AS FEEDSTOCK_RATIO,
FS.TANK_TOTAL
FROM FEEDSTOCK_SUMMARY AS FS
The function is used in a SP (only the relevant part is shown below):
SELECT
DATT.TANK_ID,
DATTU.UNIT_ID,
VSTBF.FEEDSTOCK_CODE,
VSTBF.FEEDSTOCK_RATIO * DATTU.VOLUME_BY_QTY AS QTY
FROM
DEX_ALLOCATE_TO_TANK AS DATT
INNER JOIN DEX_ALLOCATE_TO_TANK_UNIT AS DATTU ON DATT.DEX_ALLOCATE_TO_TANK_ID = DATTU.DEX_ALLOCATE_TO_TANK_ID
CROSS APPLY dbo.fnTb_SUM_TANK_BY_FEEDSTOCK_2(DATT.TANK_ID, DATTU.UNIT_ID) AS VSTBF
The result is
TANK_IDUNIT_IDFEEDSTOCK_CODEFEEDSTOCK_RATIO QTY
361RME 0.342959 63.9399
361SME 0.490008 91.3551
361PME 0.167036 31.1415
The SP and the function work correctly, with one problem. If you add the QTY up you get a total of 186.4366. The input volume is 186.436. The volume needs to be to 3 decimal places and the input volume must match exactly the sum of the output volume (after it has been split by the feedstock).
I have tried using various combinations of the round function to round or truncate the values as well as casting to 3 decimal places for the ratio, but i still get an inconsistent result. With some transactions the input and output volume matches and others they do not.
Any help on solving this would be appreciated
Kind regards
Justin
September 12, 2010 at 9:06 am
Can you post the table definitions please? The data types of the columns are going to be important here.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 12, 2010 at 10:30 am
Hi thanks for your response
The data types used are:
FS.TANK_ID INT
FS.UNIT_ID INT
FS.Feedstock_Type_Code VARCHAR(10)
The columns FS.QTY and FS.TANK_TOTAL are actually calculated columns from an earlier step in the function fnTb_SUM_TANK_BY_FEEDSTOCK (which I have not shown in the example). They are both the product of a sum on an uderlying column called:
QTY DECIMAL (18,3)
FS.QTY - the sum of the QTY by feedstock in the tank
FS.TANK_TOTAL - the tank total
Kind regards
Justin
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply